Unable to INSERT or UPDATE via form

[`ataraxy]

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'; 
?>

[guido2004]

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.

[`ataraxy]

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 

[guido2004]

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();
    } 

[`ataraxy]

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?

[guido2004]

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.

[`ataraxy]

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.