SQL joins when coding php

This now seems to be working. I restarted everything. Thanks anyway.


I have two tables I am trying to have information updated to from a webpage. They are as follows:

category - categoryID, cName
products - pDate, pDesc, pImage, pName, pPrice, productID, pSize

I am getting an error back that shows:

Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘Unknown column ‘categoryID’ in ‘field list’’ in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC9\data\localweb\projects\japanArt\public_html\webAdmin\updateAction.php:32 Stack trace: #0 C:\Program Files (x86)\EasyPHP-DevServer-14.1VC9\data\localweb\projects\japanArt\public_html\webAdmin\updateAction.php(32): mysqli->prepare(‘UPDATE products…’) #1 {main} thrown in C:\Program Files (x86)\EasyPHP-DevServer-14.1VC9\data\localweb\projects\japanArt\public_html\webAdmin\updateAction.php on line 32

I am sure it is because there is no categoryID in my product table and it is looking for it. The line of code I think is the issue is:

$sql = "UPDATE products SET categoryID = $new_categoryID, pName ='$new_pName', pPrice = $new_pPrice, pImage = '$new_pImage' WHERE productID = $productID";

The user is editing the records and what should be being updated is the pName, pPrice, pImage. The category is chosen from a drop down list. I feel there should be some join in this sql statement but cannot figure it out. 

If this is making sense I will appreciate all advice. Thanks.

This is the page of code if it helps:
<?php
  define('ALLOW_ACCESS', 1);
	$title = 'Update details';  
	require('../../incAdmin/incHead.php');
	require_once('../../incAdmin/adminConnect.php');
?>
<h2>Update fields for product ID <?php print $_SESSION['updateID']; ?></h2>
<?php
if ($_SESSION['loggedIn']) {

if (isset($_POST['cmdSubmit'])) {
  // CREATE VARIABLES from form's POST data
  $new_categoryID = $_POST['cboCategoryID'];
  $new_pName  = $_POST['txtName'];
  $new_pPrice = $_POST['txtPrice'];
  $new_pImage = $_POST['txtImage'];
 
  // VALIDATE THE FORM (This is very basic. In your Japan Art Print website, make the validation more comprehensive)
  $message = '';

  if (empty($new_pName)) {
	$message = "ERROR: Product name is required";
  }
  if (empty($new_pPrice)) {
	$message = $message . "\nERROR: Product price is required";
  }

  // If no errors, update the record in the database
  if ($message == '') {
     $productID = $_SESSION['updateID'];
  $sql = "UPDATE products SET categoryID = $new_categoryID, pName ='$new_pName', pPrice = $new_pPrice, pImage = '$new_pImage' WHERE productID = $productID";
	if ($stmt = $db->prepare($sql)) {
		$stmt->execute();
		$message = "Update successful. \nNumber of records amended: " . $stmt->affected_rows;
		$stmt->close();
	}
	else {
		$message = "Error while attempting to update the record.";
	}
 }
 }

else {  // first time form is displayed: Initialise variables and obtain record from database
	$new_categoryID = '';
	$new_cName = '';
	$new_pName = '';
	$new_pImage = '';
	$new_pPrice = '';
	$message = '';

   // run the database query to find requested record
   $sql = 'SELECT c.cName, p.pName, p.pPrice, p.pImage FROM category as c, products as p WHERE c.categoryID = c.categoryID and p.productID = ' . $_SESSION['updateID'];
   if ($stmt = $db->prepare($sql)) {
	$stmt->execute();

   	// Bind PHP variables to the output from the prepared statement
	$stmt->bind_result( $old_cName, $old_pName, $old_pPrice, $old_pImage);

   	// Fetch and the record so it can be displayed in the form
	while ($stmt->fetch()) {
		$new_cName = $old_cName;
		$new_pName= $old_pName;
		$new_pPrice = $old_pPrice;
		$new_pImage = $old_pImage;
   	}
	$stmt->close();
  }
  else {
	$message = "*** ERROR: Could not read table to find requested record. \nPlease try again later.";
  }
}

?>
<form id="frmUpdate" method="post" action="updateAction.php">
  <p><br />
  	<label>Category:</label>
	<select name="cboCategoryID">
		<?php
			//Set up a drop-down list of categories
			$sql = 'SELECT * FROM Category ORDER BY cName';
			$stmt = $db->prepare($sql);
			$stmt->execute();
			$stmt->bind_result($row_categoryID, $row_cName);

			while ($stmt->fetch()) {
				print "\n<option ";
				if ($row_categoryID == $new_categoryID) { print 'selected '; }
				print 'value="';
				print $row_categoryID;
				print '">';
				print $row_cName;
				print '</option>';
			}
			$stmt->close();
		?>
	
	</select>
	<br /><br />

  	<label>Product Name:</label> 
	<input type="text" name="txtName" id="txtName" size="70" value="<?php print $new_pName; ?>" />
  	<br /><br />
  
  	<label>Product price: &#160;&#160;&#160; $</label> 
	<input type="text" name="txtPrice" id="txtPrice" size="8" value="<?php printf('%0.2f', $new_pPrice); ?>" />    
 	 <br /><br />

	<label>Image filename:</label> 
	<input type="text" name="txtImage" id="txtImage" size="30" value="<?php print $new_pImage; ?>" />
 	 <br /><br />
 
  	<input type="submit" name="cmdSubmit" id="cmdSubmit" value="Update this record" />
  	<br /><br />
  
  	<label>Report:</label>
  	<textarea name="txtMessage" id="txtMessage" cols="60" rows="4" readonly="readonly"
		style="background-color:#FFFFFF;color:#000000; overflow:hidden;"><?php print $message;?></textarea>
  </p>
</form>  
<!----------------------------------------------------------------------------->
<?php
}
else {
    	print 'ERROR: you are not authorised to access this page';
} 
	require('../../incAdmin/incFoot.php');
?>
1 Like

It has noting to do with joins.

If your product may belong to only one category, then simply add a categoryID column to the product table.

It it may belong to many categories, then create another table product_category and run another insert query, adding newly generated productID and desired categotyID

2 Likes

Thank you once again colshrapnel.

// If no errors, update the record in the database
  if ($message == '') {
     $productID = $_SESSION['updateID'];
  $sql = "UPDATE products SET categoryID = $new_categoryID, pName ='$new_pName', pPrice = $new_pPrice, pImage = '$new_pImage' WHERE productID = $productID";
	if ($stmt = $db-&gt;prepare($sql)) {
		$stmt-&gt;execute();
		$message = "Update successful. \nNumber of records amended: " . $stmt-&gt;affected_rows;
		$stmt-&gt;close();
	}

You’re a sitting duck for potential SQL Injection attack with that bit as you’re not using prepared statements properly

1 Like

Yes, I agree I am a sitting duck. I am going to do some validation next, which what I am hoping you mean. Learning as I go. This is not live. This is just me trying to get my head around my new goal of being able to code php and sql.

1 Like

Are you using mysqli or PDO?

mysqli

Validation and using prepare statements are two different things - you should use both.

This is how prepared statements are done in mysqli (apologies if I’ve got it wrong - I normally use PDO)

$sql = "
UPDATE
products
SET
categoryID = $new_categoryID
, pName =‘$new_pName’
, pPrice = $new_pPrice
, pImage = ‘$new_pImage’
WHERE
productID = $productID";
if ($stmt = $db->prepare($sql)) {
$stmt->bind_param(“ssss”, $new_categoryID,$new_pName,$new_pPrice,$new_pImage);
$stmt->execute();
$message = "Update successful. \nNumber of records amended: " . $stmt->affected_rows;

The “ssss” is the bit that tells MySQL what type of variable each is, in that case it’s 4 strings but in your case category ID and price are probably integers. It’s i for integers, d for double, s for string and b for BLOB.

As @felgall has pointed out validation should also be done, before the data gets anywhere near to the database

Should be

categoryID =?, pName =?, pPrice = ?, pImage = ?

:blush: whoops!, missed that!

Ok so I updated teh bind_param statement to:

else { // first time form is displayed: Initialise variables and obtain record from database
$new_categoryID = ‘’;
$new_cName = ‘’;
$new_pName = ‘’;
$new_pImage = ‘’;
$new_pPrice = ‘’;
$message = ‘’;

// run the database query to find requested record
$sql = 'SELECT p.productID, p.pName, p.pPrice, p.pImage FROM Category as c, Products as p WHERE c.categoryID = c.categoryID and p.productID = ’ . $_SESSION[‘updateID’];
if ($stmt = $db->prepare($sql)) {
$stmt->execute();

  // Bind PHP variables to the output from the prepared statement

$stmt->bind_result(“isssi” $old_cName, $old_pName, $old_pPrice, $old_pImage);

Can I have integra’s and strings together like I have?

hmmm, ok can you explain why so I understand.

I had to take the “isssi” out as it presented another error. :frowning:

The number of characters in the first parameter to the bind (i,s,b,d) need to match the number of parameter names following it and also the number of ? in the prepare statement.

There should be no variables referenced in the prepare, they all need to be replaced by ?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.