Unable to INSERT or UPDATE via form

background:

first, i am very new to PHP - i’m using Kevin Yank’s “Build your own database driven web site using php & mysql” 4th edition. i’m working with chapter 7’s CMS, using the examples for to add, edit & delete authors. i’ve modified it for use with my product table, which is used to store basic product information and a URL for my product image.

the problem:

i’m having trouble inserting new products, and updating existing products. i can get to my form to add products, and my product information populates the form correctly when choosing edit. when selecting the button to add or edit, all i get is the error that i am specifying (see below). i’ve added code to echo $sql, and the sql statement appears on the page above my error. it looks correct, but i can’t get it into the database. i know i’m missing something very basic here, but after 3 days and a lot of searching, i need some help!

Add product output:

INSERT INTO product SET name=‘testing new product’, price=‘8.00’, details=’ test’, picture=‘images/products/hazlenut.jpg’, category=‘Flavored Coffee’

Error adding submitted product.

Edit product output:

Array ( [name] => Millstone Hazlenut Cream [price] => 8.50 [details] => A medium roast flavored coffee. Test test test. [picture] => images/products/hazlenut.jpg [category] => Flavored Coffee [id] => 1 ) UPDATE product SET name=‘Millstone Hazlenut Cream’, price=‘8.50’, details=’ A medium roast flavored coffee. Test test test.', picture=‘images/products/hazlenut.jpg’, category=‘Flavored Coffee’, WHERE productID=‘1’

Error updating submitted product.

manageproducts.php:

<?php  include '../includes/connect.php';
include	'../includes/helpers.inc.php';
ini_set('display_errors',1);
error_reporting(E_ALL);


$result = mysqli_query($link, 'SELECT productID, productName, productPrice, productDetails, productPicture, productCategory FROM product');
if (!$result)
{
	$error = 'Error fetching products: ' .mysqli_error($link);
	include 'error.html.php';
	exit();
}

while ($row = mysqli_fetch_array($result))
{
$products[] = array('productID' => $row['productID'], 'productName' => $row['productName'], 'productPrice' => $row['productPrice'], 'productDetails' => $row['productDetails'], 'productPicture' => $row['productPicture'],  'productCategory' => $row['productCategory']) ;
}


// Add Product

if (isset($_GET['add']))
{
	$pagetitle = 'New Product';
	$action = 'addform';
	$name = '';
	$price = '';
	$details = '';
	$picture = '';
	$category = '';
	$id = '';
	$button = 'Add product';

	include 'form.html.php';
	exit();
}

if (isset($_GET['addform']))
{
	include '../includes/connect.php';
	$id = mysqli_real_escape_string($link, $_POST['id']);
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$price = mysqli_real_escape_string($link, $_POST['price']);
	$details = mysqli_real_escape_string($link, $_POST['details']);
	$picture = mysqli_real_escape_string($link, $_POST['picture']);
	$category = mysqli_real_escape_string($link, $_POST['category']);
	$sql = "INSERT INTO product SET
			name='$name',
			price='$price',
			details='$details',
			picture='$picture',
			category='$category'";
	echo $sql;		
	echo mysql_error(); 
	
	if (!mysqli_query($link, $sql))
	{
		$error = 'Error adding submitted product.';
		include '../includes/error.html.php';
		exit();
	}

	header('Location: .');
	exit();
}



// Edit Product

if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
	include '../includes/connect.php';

	$id = mysqli_real_escape_string($link, $_POST['id']);
	$sql = "SELECT productID, productName, productPrice, productDetails, productPicture, productCategory FROM product WHERE productID='$id'";
	$result = mysqli_query($link, $sql);
	if (!$result)
	{
		$error = 'Error fetching product details.';
		include '../includes/error.html.php';
		exit();
	}
	
	$row = mysqli_fetch_array($result);
echo "let's see what $row contains: "; print_r($row); echo "<br />"; 
	$pagetitle = 'Edit Product';
	$action = 'editform';
	$name = $row['productName'];
	$price = $row['productPrice'];
	$details = $row['productDetails'];
	$picture = $row['productPicture'];
	$category = $row['productCategory'];
	$id = $row['productID'];
	$button = 'Update product';

	include 'form.html.php';
	exit();
}

if (isset($_GET['editform']))
{
	include '../includes/connect.php';

	$id = mysqli_real_escape_string($link, $_POST['id']);
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$price = mysqli_real_escape_string($link, $_POST['price']);
	$details = mysqli_real_escape_string($link, $_POST['details']);
	$picture = mysqli_real_escape_string($link, $_POST['picture']);
	$category = mysqli_real_escape_string($link, $_POST['category']);
	$sql = "UPDATE product SET
			name='$name',
			price='$price',
			details='$details',
			picture='$picture',
			category='$category',
			WHERE productID='$id'";
			
			print_r($_POST); 
echo $sql;
			
	if (!mysqli_query($link, $sql))
	{
		$error = 'Error updating submitted product.';
		include '../includes/error.html.php';
		exit();
	}

	header('Location: .');
	exit();
}

// Delete Product

if (isset($_POST['action']) and $_POST['action'] == 'Delete')
{
	include '../includes/connect.php';
	$id = mysqli_real_escape_string($link, $_POST['id']);

	// Get product
	//$sql = "SELECT productID FROM joke WHERE productID='$id'";
	//$result = mysqli_query($link, $sql);
	//if (!$result)
	//{
		//$error = 'Error getting list of products to delete.';
		//include '../error.html.php';
		//exit();
	//}

	// For each product
	//while ($row = mysqli_fetch_array($result))
	//{
		//$productId = $row[0];

		// Delete product entries
		$sql = "DELETE FROM product WHERE productID='$id'";
		if (!mysqli_query($link, $sql))
		{
			$error = 'Error deleting product.';
			include '../includes/error.html.php';
			exit();
		}
	

	header('Location: .');
	exit();
}

// Display product list
include '../includes/connect.php';
$result = mysqli_query($link, 'SELECT productID, productName, productPrice, productDetails, productPicture, productCategory FROM product');
if (!$result)
{
	$error = 'Error fetching products from database!';
	include '../includes/error.html.php';
	exit();
}

//while ($row = mysqli_fetch_array($result))
//{
	//$product[] = array('productID' => $row['productID'], 'productName' => $row['productName'], 'productPrice' => $row['productPrice'], 'productDetails' => $row['productDetails'], 'productPicture' => $row['productPicture'], 'productCategory' => $row['productCategory']);
//}

include 'manageproducts.html.php';
?>

manageproducts.html.php:

<?php include 'header_admin.php';?>


<h1>Admin Control Panel :: Manage Products</h1>

<h2>Manage your products here.<br />
	Current products in the database:</h2>

		<p><a href="?add">Add new product</a></p>
			<table border="1" width="890px">
				<tr>
					<th>productName</th>
					<th>productPrice</th>
					<th>productDetails</th>
					<th>productPicture</th>
					<th>productCategory</th>
					<th colspan=2>Modify</th>
				</tr>

			<?php foreach ($products as $product): ?>
					<form action="" method="post">
						
							
								<tr>
									<td><?php echo($product['productName']); ?></td>
									<td><?php echo($product['productPrice']); ?></td>
									<td><?php echo($product['productDetails']); ?></td>
									<td><?php echo($product['productPicture']); ?></td>
									<td><?php echo($product['productCategory']); ?></td>
									<td><input type="submit" name="action" value="Edit"/></td>
									<td><input type="submit" name="action" value="Delete"/></td>
								</tr>

									<input type="hidden" name="id" value="<?php
											echo $product['productID']; ?>"/>
						
					</form>

			<?php endforeach; ?>

							</table>







	<p>&nbsp;</p>

<?php include 'footer_admin.php'; 
?>

form.php.html:

<?php include 'header_admin.php'; ?>


		<h1><?php htmlout($pagetitle); ?></h1>
		<table>
				<form action="?<?php htmlout($action); ?>" method="post">
					<div>
							<tr>
								<td><label for="name">Product Name:</td><td> <input type="text" name="name" size="50"
									id="name" value="<?php htmlout($name); ?>"/></label></td>
							</tr>
					</div>
					<div>
								<td><label for="price">Product Price:</td><td> <input type="text" name="price" size="5"
									id="price" value="<?php htmlout($price); ?>"/></label></td>
							</tr>
					</div>
					<div>
								<td><label for="details">Product Details:</label></td><td>
								<textarea rows="5" cols="38" name="details" id="details"> <?php htmlout($details); ?></textarea></td>
								
							</tr>
					</div>
					<div>
								<td><label for="picture">Product Picture URL:</td><td> <input type="text" name="picture" size="50"
									id="picture" value="<?php htmlout($picture); ?>"/></label></td>
							</tr>
					</div>
					<div>
								<td><label for="category">Product Category:</td><td> <input type="text" name="category" size="50"
									id="category" value="<?php htmlout($category); ?>"/></label></td>
							</tr>
					</div>
					<div>
								<td><input type="hidden" name="id" value="<?php	htmlout($id); ?>"/>
										
										<input type="submit" value="<?php htmlout($button); ?>"/></td>
							</tr>
		
					</div>
				</form>
		</table>

<p>&nbsp;</p>
<?php include 'footer_admin.php'; 
?>

In the UPDATE there is a comma too many (just before the WHERE).

I don’t see the problem with the INSERT query. Just for debugging purposes, when you display that generic error message, please display the mysqli_error() message as well.

thanks for the quick reply. i’ve removed the additional comma in the UPDATE, and added a line to “echo mysql_error();” - when i use “echo mysqli_error();” i get the following output in addition to the quoted output above:

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 122

i have been trying to display errors with no luck. below is my edited UPDATE code. is this what you mean by displaying the mysqli_error()?


	$sql = "UPDATE product SET
			name='$name',
			price='$price',
			details='$details',
			picture='$picture',
			category='$category'
			WHERE productID='$id'";
			
			print_r($_POST); 
echo $sql;
echo mysqli_error();  >>>>>>>>> this is line 122

You need to display the error AFTER you executed the query, and mysqli_error wants a parameter (see http://www.php.net/mysqli_error)

    if (!mysqli_query($link, $sql))
    {
        $error = 'Error adding submitted product.';

        // debug line, delete after debugging
        $error .= ' mysqli_error: ' . mysqli_error($link);

        include '../includes/error.html.php';
        exit();
    }

just what i was looking for to debug, thanks.

i’ve added debugging to the add & edit portions of my code, and i am now getting this for both:

Unknown column ‘name’ in ‘field list’.

i don’t see anything in my code that would be causing this, and have been doing research on this error. it seems like it’s normally caused by missing ’ ’ around the information being sent to the database. any ideas?

Take a look at your table. Are you sure the name of the column is ‘name’ ?

Looking at this query:

$result = mysqli_query($link, 'SELECT productID, productName, productPrice, productDetails, productPicture, productCategory FROM product');

I’d say you’re using the wrong column names in your insert and update queries.

the query includes the correct column names, all beginning with “product”. for example, my name column is “productName”.

i thought that assigning a variable to each of my column names as below, i should be using the variable names:

	$pagetitle = 'Edit Product';
	$action = 'editform';
	$name = $row['productName'];
	$price = $row['productPrice'];
	$details = $row['productDetails'];
	$picture = $row['productPicture'];
	$category = $row['productCategory'];
	$id = $row['productID'];
	$button = 'Update product';

i’ve tried changing values to the exact column names:

if (isset($_GET['editform']))
{
	include '../includes/connect.php';

	$id = mysqli_real_escape_string($link, $_POST['ID']);
	$name = mysqli_real_escape_string($link, $_POST['name']);
	$price = mysqli_real_escape_string($link, $_POST['price']);
	$details = mysqli_real_escape_string($link, $_POST['details']);
	$picture = mysqli_real_escape_string($link, $_POST['picture']);
	$category = mysqli_real_escape_string($link, $_POST['category']);
	$sql = "UPDATE product SET
			productName='$name',
			productPrice='$price',
			productDetails='$details',
			productPicture='$picture',
			productCategory='$category'
			WHERE productID='$id'";

this takes me back to my index.php page in my admin section when attempting to update, no errors & no update to the database.

if (isset($_GET['editform']))
{
	include '../includes/connect.php';

	$id = mysqli_real_escape_string($link, $_POST['productID']);
	$name = mysqli_real_escape_string($link, $_POST['productName']);
	$price = mysqli_real_escape_string($link, $_POST['productprice']);
	$details = mysqli_real_escape_string($link, $_POST['productDetails']);
	$picture = mysqli_real_escape_string($link, $_POST['productPicture']);
	$category = mysqli_real_escape_string($link, $_POST['productCategory']);
	$sql = "UPDATE product SET
			name='$name',
			price='$price',
			details='$details',
			picture='$picture',
			category='$category'
			WHERE productID='$id'";

this produces the following:

Notice: Undefined index: productID in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 110

Notice: Undefined index: productName in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 111

Notice: Undefined index: productprice in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 112

Notice: Undefined index: productDetails in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 113

Notice: Undefined index: productPicture in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 114

Notice: Undefined index: productCategory in /home/MCCNET/casingleton1/public_html/info2750/admin/manageproducts.php on line 115
Array ( [name] => Millstone Hazlenut Cream [price] => 8.50 [details] => A medium roast flavored coffee [picture] => images/products/hazlenut.jpg [category] => Flavored Coffee [id] => 1 ) UPDATE product SET name=‘’, price=‘’, details=‘’, picture=‘’, category=‘’ WHERE productID=‘’

Error updating submitted product. mysqli_error: Unknown column ‘name’ in ‘field list’

the code below also takes me back to my index.php page in my admin section when attempting to update, no errors & no update to the database:

if (isset($_GET['editform']))
{
	include '../includes/connect.php';

	$id = mysqli_real_escape_string($link, $_POST['productID']);
	$name = mysqli_real_escape_string($link, $_POST['productName']);
	$price = mysqli_real_escape_string($link, $_POST['productprice']);
	$details = mysqli_real_escape_string($link, $_POST['productDetails']);
	$picture = mysqli_real_escape_string($link, $_POST['productPicture']);
	$category = mysqli_real_escape_string($link, $_POST['productCategory']);
	$sql = "UPDATE product SET
			productName='$name',
			productPrice='$price',
			productDetails='$details',
			productPicture='$picture',
			productCategory='$category'
			WHERE productID='$id'";

i’m obviously flustered at this point & just toying with things to see what results i’ll get.

also, this line:

echo "let’s see what $row contains: "; print_r($row); echo “<br />”;

produces this result:

let’s see what Array contains: Array ( [0] => 1 [productID] => 1 [1] => Millstone Hazlenut Cream [productName] => Millstone Hazlenut Cream [2] => 8.50 [productPrice] => 8.50 [3] => A medium roast flavored coffee [productDetails] => A medium roast flavored coffee [4] => images/products/hazlenut.jpg [productPicture] => images/products/hazlenut.jpg [5] => Flavored Coffee [productCategory] => Flavored Coffee )

this looks correct to me, and is correctly displayed in my form when choosing to edit an item.