SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 Code:
    <?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 Code:
    <?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 Code:
    <?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'
    ?>

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  3. #3
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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()?

    PHP Code:
        $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 

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You need to display the error AFTER you executed the query, and mysqli_error wants a parameter (see http://www.php.net/mysqli_error)
    PHP Code:
        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();
        } 

  5. #5
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Take a look at your table. Are you sure the name of the column is 'name' ?

    Looking at this query:
    PHP Code:
    $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.

  7. #7
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
        $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:
    PHP Code:
    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.

    PHP Code:
    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:
    PHP Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •