what is wrong with this line?

[Doug]

Hello all,

 

I hope this there is an easy answer to this one:

 

I have:

$query="SELECT * FROM '" . $row['cat'] . "' WHERE user_id= '" . $row['user_id'] . "'";

$data = mysqli_query($dbc, $query)
    or die("Error: ".mysqli_error($dbc));

 

which gives me the error: Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''butcher' WHERE user_id= '5'' at line 1

 

so the correct values are being inserted so I think the problem is the quotes. I have tried swapping them and using various combinations but nothing seems to work

 

I would be eternally grateful for other suggestions?

 

[Skewled]

$query="SELECT * FROM '" . $row['cat'] . "' WHERE user_id= '" . $row['user_id'] . "'";

$data = mysqli_query($dbc, $query)
    or die("Error: ".mysqli_error($dbc));

 

What values are getting passed to $row['cat'] and $row['user_id']? The quotes look fine to me..

 

Also, where is the query giving the values to the 2 $row statements?

[PFMaBiSmAd]

Table names don't have single quotes around them.

 

You also should not have separate tables for each cat/category, requiring you to use the result from one query as the table name in another query.

[mustafasr]

$query="SELECT * FROM `" . $row['cat'] . "` WHERE user_id= '" . $row['user_id'] . "'";

 

$data = mysqli_query($dbc, $query)

    or die("Error: ".mysqli_error($dbc));

 

 

This is the correct syntax

[Doug]

 

So, is it impossible to select the name of the table form the lines above? I have 20 different tables. Are you saying it should be one huge table?

 

Or 20 different programs depending on the user’s choice?

 

Thanks or continued help.

 

For context the whole program is below. I eventually want the name of the table to be automatically entered but chose the few lines as a start

 

<?php
  
error_reporting(E_ALL);
session_start();

  
  ?>


<?php
  
require_once('appvars.php');
  require_once('connectvars1.php');

  
//$datbase= $_POST['$datbase'];


// Connect to the database
  
$dbc = mysqli_connect(DB_Host, DB_User, DB_Password, DB_Name);

  
$row = $_POST['user_id'];


if (!isset($_GET['user_id'])) {
    
$query = "SELECT * FROM butcher WHERE user_id = '" .  $row['user_id'] . "'";


  
}
  else 
{
    $query = "SELECT * FROM butcher WHERE user_id = '" . $_GET['user_id'] . "'";
  
}
  $data = mysqli_query($dbc, $query)
or die("Error: ".mysqli_error($dbc));

if (mysqli_num_rows($data) == 1) {
    

  
$row = mysqli_fetch_array($data);
    

}

?>








<?php

require_once('appvars.php');
  require_once('connectvars1.php');

  
// Make sure the user is logged in before going any further.
  
if (!isset($_SESSION['user_id'])) {
    echo '<p class="login">Please <a href="login1.php">log in</a> to access this page.</p>';


exit();
  
}
  



  // Connect to the database

  
$dbc = mysqli_connect(DB_Host, DB_User, DB_Password, DB_Name);
if(!empty($username)) {

echo '<p class="login">This page is already taken please <a href="index5.php"">go home</a> and try again.</p>';

}



if (isset($_POST['submit'])) {
    


// Grab the profile data from the POST
    
$name = mysqli_real_escape_string($dbc, trim($_POST['name']));
    
$phone = mysqli_real_escape_string($dbc, trim($_POST['phone']));

$address1 = mysqli_real_escape_string($dbc, trim($_POST['address1']));
    
$address2 = mysqli_real_escape_string($dbc, trim($_POST['address2']));
    
$postcode = mysqli_real_escape_string($dbc, trim($_POST['postcode']));

$webadd = mysqli_real_escape_string($dbc, trim($_POST['webadd']));

$email = mysqli_real_escape_string($dbc, trim($_POST['email']));
    
$old_picture = mysqli_real_escape_string($dbc, trim($_POST['old_picture']));
    
$new_picture = mysqli_real_escape_string($dbc, trim($_FILES['new_picture']['name']));
    
$new_picture_type = $_FILES['new_picture']['type'];
    $new_picture_size = $_FILES['new_picture']['size']; 


$username = mysqli_real_escape_string($dbc, trim($_POST['username']));

$user_id = mysqli_real_escape_string($dbc, trim($_POST['user_id']));

    
if (!empty($_FILES['new_picture']['tmp_name'])) {list($new_picture_width, $new_picture_height) = getimagesize($_FILES['new_picture']['tmp_name']);

}


//list($new_picture_width, $new_picture_height) = getimagesize($_FILES['new_picture']['tmp_name']);
    
$error = false;

    

// Validate and move the uploaded picture file, if necessary
    
if (!empty($new_picture)) {
      if ((($new_picture_type == 'image/gif') || ($new_picture_type == 'image/jpeg') || ($new_picture_type == 'image/pjpeg') ||
        ($new_picture_type == 'image/png')) && ($new_picture_size > 0) && ($new_picture_size <= MM_MAXFILESIZE) &&
        ($new_picture_width <= MM_MAXIMGWIDTH) && ($new_picture_height <= MM_MAXIMGHEIGHT)) {
        if ($_FILES['new_picture']['error'] == 0) {
          

// Move the file to the target upload folder
          
$target = MM_UPLOADPATH . basename($new_picture);
          
if (move_uploaded_file($_FILES['new_picture']['tmp_name'], $target)) {
            

// The new picture file move was successful, now make sure any old picture is deleted
            
if (!empty($old_picture) && ($old_picture != $new_picture)) {
              
            }
          }
          else {
            

// The new picture file move failed, so delete the temporary file and set the error flag
            
@unlink($_FILES['new_picture']['tmp_name']);
            
$error = true;
            echo '<p class="error">Sorry, there was a problem uploading your picture.</p>';

          }
        }
      }
      
else {
        
// The new picture file is not valid, so delete the temporary file and set the error flag
        
@unlink($_FILES['new_picture']['tmp_name']);

        $error = true;
        
echo '<p class="error">Your picture must be a GIF, JPEG, or PNG image file no greater than ' . (MM_MAXFILESIZE / 1024) .
          ' KB and ' . MM_MAXIMGWIDTH . 'x' . MM_MAXIMGHEIGHT . ' pixels in size.</p>';
      }
    }



$error = false;


// Update the profile data in the database
    
if (!$error) {
      if (!empty($name)&& !empty($phone) && !empty($address1) && !empty($address2)) {
        // Only set the picture column if there is a new picture
        if (!empty($new_picture)) {

//if (!empty($postcode)){
          $query = "UPDATE butcher SET name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', picture = '$new_picture', username = '$username' WHERE user_id = '" . 		$row['user_id'] ."'";
        
}
       
        else {
          
$query = "UPDATE butcher set name = '$name', phone = '$phone', address1 = '$address1', address2 = '$address2', postcode = '$postcode', " .
            " email = '$email', webadd = '$webadd', username = '$username' WHERE user_id = '" . 		$row['user_id'] ."'";
        }}
        mysqli_query($dbc, $query) or die("<br>Query $query<br>Failed with error: " . mysqli_error($dbc) . '<br>On line: ' . __LINE__); 

        

// Confirm success with the user
echo 'USER ID = ' . $row["user_id"] . ''; ?> <br />
<?php
echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile7.php">view your profile</a>?</p>';


        
mysqli_close($dbc);
        exit();
    
  }
      
else {
        echo '<p class="error">You must enter all of the profile data (the picture is optional).</p>';
   
   
    }
  } 

// End of check for form submission
  else {
    

// Grab the profile data from the database
    
$query="SELECT * FROM '". $row['cat'] . "' WHERE user_id= '" . $row['user_id'] . "'";

$data = mysqli_query($dbc, $query)
    or die("Error: ".mysqli_error($dbc));
$row = mysqli_fetch_array($data);

    
if ($row != NULL) {
      $name = $row['name'];
      
$phone = $row['phone'];

$address1 = $row['address1'];

$address2 = $row['address2'];

$postcode = $row['postcode'];
      
$email = $row['email'];

$webadd = $row['webadd'];
      
$old_picture = $row['picture'];

$username = $_SESSION['username'];

$user_id = $row['user_id'];
    
}
    else {
      echo '<p class="error">There was a problem accessing your profile.</p>';

    }
  }


  mysqli_close($dbc);


  ?>
<form enctype="multipart/form-data" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

    <input type="hidden" name="MAX_FILE_SIZE" value="<?php echo MM_MAXFILESIZE; ?>" />
    
<fieldset>
      <legend>Personal Information</legend>

      <label for="name">Name:</label>
      <input type="text" id="name" name="name" value="<?php if (!empty($name)) echo $name; ?>" /><br />

      <label for="phone">Phone:</label>
      <input type="text" id="phone" name="phone" value="<?php if (!empty($phone)) echo $phone; ?>" /><br />

<label for="address1">Address1:</label>
      <input type="text" id="address1" name="address1" value="<?php if (!empty($address1)) echo $address1; ?>" /><br />


      <label for="address2">Address2:</label>
      <input type="text" id="address2" name="address2" value="<?php if (!empty($address2)) echo $address2; ?>" /><br />

      <label for="postcode">Postcode:</label>
      <input type="text" id="postcode" name="postcode" value="<?php if (!empty($postcode)) echo $postcode; ?>" /><br />

      <label for="email">Email:</label>
      <input type="text" id="email" name="email" value="<?php if (!empty($email)) { echo $email; } else { echo 'No email entered';} ?>" /><br />

      <label for="webadd">Web address:</label>
      <input type="text" id="webadd" name="webadd" value="<?php if (!empty($webadd)) { echo $webadd; } else { echo 'No web entered';}  ?>" /><br />


<input type="hidden" name="old_picture" value="<?php if (!empty($old_picture)) echo $old_picture; ?>" />
      <label for="new_picture">Picture:</label>
      <input type="file" id="new_picture" name="new_picture" />
      <?php if (!empty($old_picture)) {
        echo '<img class="profile" src="' . MM_UPLOADPATH . $old_picture . '" alt="Profile Picture" style: height=100px;" />';
      } 

    ?> <br />

<label for="address2">username:</label>
      <input type="text" id="username" name="username" value="<?php if (!empty($username)) echo $username; ?>" /><br />


<label for="user_id">User ID:</label>
      <input type="text" id="user_id" name="user_id" value="<?php echo '' . $row['user_id'] . '' ; ?>" /><br />
   
</fieldset>
    
<input type="submit" value="Save Profile" name="submit" />
  
</form>
<?php echo('<p class="login">You are logged in as ' . $_SESSION['username'] . '. <a href="logout3.php">Log out</a>.</p>');
echo '<class = "label">USER ID: ' . $row['user_id'] . '';


?>
<br />


<?php

echo 'Username = ' . $row["username"] . '' ?><br />

<?php

echo 'This category is  ' . $row["cat"] . '' ?> <br />;

<p><a href="index5.php">Return to homepage</a></p>


<?php require_once('footer.php');
?>
</body> 

</html>

[PFMaBiSmAd]

it should be one huge table?

 

A huge table would start at about 10 to 20 million rows and if you did need to manage that many rows, you would use things like partitioning to get the database engine to transparently manage the data. You would not directly attempt to manage the data in your code and queries.

 

So yes, all the same meaning data should be in one table.