Page 1 of 1

Stuck on a php tutorial

Posted: Wed Oct 15, 2008 8:35 pm
by nickobee
I am studying the book
Sams Teach Yourself PHP, Mysql and Apache
alls going ok, till chapter 22
a basic shopping cart 2 pages one is the main seestore.php
and the second is showitem.php

pages are below!
the first page works fine, shows the categories and items etc
then when I click onto show item the page (showitem.php) comes up
Unknown column 'item_id' in 'where clause'

(first page works fine)
seestore.php

Code: Select all

<?php
//connect to database
$mysqli = mysqli_connect("localhost", "root", "", "store");
 
$display_block = "<h1>My Categories</h1>
<p>Select a category to see its items.</p>";
 
//show categories first
$get_cats_sql = "SELECT id, cat_title, cat_desc FROM 
                 store_categories ORDER BY cat_title";
$get_cats_res =  mysqli_query($mysqli, $get_cats_sql) or die(mysqli_error($mysqli));
 
 
if (mysqli_num_rows($get_cats_res) < 1) {
   $display_block = "<p><em>Sorry, no categories to browse.</em></p>";
} else {
   while ($cats = mysqli_fetch_array($get_cats_res)) {
        $cat_id  = $cats['id'];
        $cat_title = strtoupper(stripslashes($cats['cat_title']));
        $cat_desc = stripslashes($cats['cat_desc']);
 
        $display_block .= "<p><strong><a href=\"".$_SERVER["PHP_SELF"]."?cat_id=".$cat_id."\">".$cat_title."</a></strong><br/>".$cat_desc."</p>";
 
        if (isset($_GET["cat_id"])) {
         if ($_GET["cat_id"] == $cat_id) {
            //get items
            $get_items_sql = "SELECT id, item_title, item_price FROM store_items WHERE cat_id = '".$cat_id."' ORDER BY item_title";
            $get_items_res = mysqli_query($mysqli, $get_items_sql) or die(mysqli_error($mysqli));
 
            if (mysqli_num_rows($get_items_res) < 1) {
               $display_block = "<p><em>Sorry, no items in this category.</em></p>";
            } else {
               $display_block .= "<ul>";
 
               while ($items = mysqli_fetch_array($get_items_res)) {
                  $item_id  = $items['id'];
                  $item_title = stripslashes($items['item_title']);
                  $item_price = $items['item_price'];
 
                  $display_block .= "<li><a href=\"showitem.php?item_id=".$item_id."\">".$item_title."</a></strong> (\$".$item_price.")</li>";
               }
 
               $display_block .= "</ul>";
            }
 
            //free results
            mysqli_free_result($get_items_res);
 
         }
      }
   }
}
//free results
mysqli_free_result($get_cats_res);
 
//close connection to MySQL
mysqli_close($mysqli);
?>
<html>
<head>
 
(second page dosent work?)
showitem.php

Code: Select all

<?php
//connect to database
$mysqli = mysqli_connect("localhost", "root", "", "store");
 
$display_block = "<h1>My Store - Item Detail</h1>";
 
//validate item
$get_item_sql = "SELECT c.id as cat_id, c.cat_title, si.item_title, si.item_price, si.item_desc, si.item_image FROM store_items AS si LEFT JOIN store_categories AS c on c.id = si.cat_id WHERE si.id = '".$_GET["item_id"]."'";
$get_item_res = mysqli_query($mysqli, $get_item_sql) or die(mysqli_error($mysqli));
 
if (mysqli_num_rows($get_item_res) < 1) {
   //invalid item
   $display_block .= "<p><em>Invalid item selection.</em></p>";
} else {
   //valid item, get info
   while ($item_info = mysqli_fetch_array($get_item_res)) {
      $cat_id = $item_info['cat_id'];
      $cat_title = strtoupper(stripslashes($item_info['cat_title']));
      $item_title = stripslashes($item_info['item_title']);
      $item_price = $item_info['item_price'];
      $item_desc = stripslashes($item_info['item_desc']);
      $item_image = $item_info['item_image'];
   }
 
   //make breadcrumb trail
   $display_block .= "<p><strong><em>You are viewing:</em><br/>
   <a href=\"seestore.php?cat_id=".$cat_id."\">".$cat_title."</a> > ".$item_title."</strong></p>
   <table cellpadding=\"3\" cellspacing=\"3\">
   <tr>
   <td valign=\"middle\" align=\"center\"><img src=\"".$item_image."\"/></td>
   <td valign=\"middle\"><p><strong>Description:</strong><br/>".$item_desc."</p>
   <p><strong>Price:</strong> \$".$item_price."</p>";
 
   //free result
   mysqli_free_result($get_item_res);
 
   //get colors
   $get_colors_sql = "SELECT item_color FROM store_item_color WHERE item_id = '".$_GET["item_id"]."' ORDER BY item_color";
   $get_colors_res = mysqli_query($mysqli, $get_colors_sql) or die(mysqli_error($mysqli));
 
   if (mysqli_num_rows($get_colors_res) > 0) {
        $display_block .= "<p><strong>Available Colors:</strong><br/>";
        while ($colors = mysqli_fetch_array($get_colors_res)) {
           $item_color = $colors['item_color'];
           $display_block .= $item_color."<br/>";
       }
   }
 
   //free result
   mysqli_free_result($get_colors_res);
 
   //get sizes
   $get_sizes_sql = "SELECT item_size FROM store_item_size WHERE item_id = ".$_GET["item_id"]." ORDER BY item_size";
   $get_sizes_res = mysqli_query($mysqli, $get_sizes_sql) or die(mysqli_error($mysqli));
 
   if (mysqli_num_rows($get_sizes_res) > 0) {
       $display_block .= "<p><strong>Available Sizes:</strong><br/>";
 
       while ($sizes = mysqli_fetch_array($get_sizes_res)) {
          $item_size = $sizes['item_size'];
          $display_block .= $item_size."<br/>";
       }
   }
 
   //free result
   mysqli_free_result($get_sizes_res);
 
   $display_block .= "
   </td>
   </tr>
   </table>";
}
?>
<html>
<head>
<title>My Store</title>
</head>
<body>
<?php echo $display_block; ?>
</body>
</html>
 

Re: Stuck on a php tutorial

Posted: Wed Oct 15, 2008 10:06 pm
by requinix
It means that there isn't an "item_id" field in that table. Make sure you typed the right thing.

SOLVED

Posted: Sat Oct 18, 2008 5:32 pm
by nickobee
I fixed it, it was a problem with the database
the color table and the size table both had colomns named "id" I had to rename them "item_id"
thanks for help>>>

Re: SOLVED

Posted: Sat Oct 18, 2008 5:46 pm
by califdon
nickobee wrote:I fixed it, it was a problem with the database
the color table and the size table both had colomns named "id" I had to rename them "item_id"
thanks for help>>>
Just so you'll know, there's nothing wrong with having columns with the same name in different tables, although you might find it confusing.

Re: Stuck on a php tutorial

Posted: Sat Apr 18, 2009 10:37 pm
by deagles
I am also getting the same error attempting to run the code sample from that book.

I set up the mysql database exactly as directed in the prior chapter. The item_size and item_color tables ARE set to item_id rather than id, so I'm not sure what the previous poster had done. (I don't believe that is the issue with the error anyhow.)

In the table for store_categories, I was able to rename id to item_id. However, in the store_items table it will not let me rename id to item_id.

I've tried changing the following:

Code: Select all

$get_item_sql = "SELECT c.id as cat_id, c.cat_title, si.item_title, si.item_price, si.item_desc, si.item_image FROM store_items AS si LEFT JOIN store_categories AS c on c.id = si.cat_id WHERE si.id = '".$_GET["item_id"]."'";
TO:

Code: Select all

$get_item_sql = "SELECT c.id as cat_id, c.cat_title, si.item_title, si.item_price, si.item_desc, si.item_image FROM store_items AS si LEFT JOIN store_categories AS c on c.id = si.cat_id WHERE si.id = '".$_GET["id"]."'";
but I am still getting the same error.

Sadly I am completely lost as to what this statement is trying to accomplish and how to resolve the error.

The store_items table has the following columns:
  • id
    cat_id
    item_title
    item_price
    item_desc
    item_image
The store_categories table has the following columns:
  • item_id (renamed from id)
    cat_title
    cat_desc
Please help a frustrated newbie.

Re: Stuck on a php tutorial

Posted: Sun Apr 19, 2009 2:12 pm
by deagles
Figured it out. Actually the error I was getting was different (basically a syntax error). doh!

Now I'm getting a whole new error: Invalid item selection.

Back to the drawing board...

Re: Stuck on a php tutorial

Posted: Sun Apr 19, 2009 2:48 pm
by deagles
Interesting... I inserted the following:

Code: Select all

$get_item_sql = "SELECT c.id as cat_id, c.cat_title, si.item_title, si.item_price, si.item_desc, 
 
si.item_image FROM store_items AS si LEFT JOIN store_categories AS c on c.id = si.cat_id WHERE 
 
si.id = '".$_GET["id"]."'";
$get_item_res = mysql_query($get_item_sql, $mysql_connect) or die(mysql_error() );
 
$num_rows = mysql_num_rows($result);
 
echo "$num_rows Rows\n";
 
and now I get:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /www/110mb.com/mystuff/htdocs/showitem.php on line 28
Rows

yet 110mb.com supposedly runs php5 and sql5.

Frustrating.

Re: Stuck on a php tutorial

Posted: Sun Apr 19, 2009 3:19 pm
by califdon
You're saying that you get no mysql_error from the mysql_query line, but you do get an error from mysql_num_rows? Sounds like it's not returning any rows that match your criteria. Insert a line that just echoes the value of $get_item_res, right after you assign its value. Inspect what is printed carefully for any missing data.

Re: Stuck on a php tutorial

Posted: Sun Apr 19, 2009 5:06 pm
by deagles
Thank you. Fixed it.

Code: Select all

/validate item
$get_item_sql = "SELECT c.id as cat_id, c.cat_title, si.item_title, si.item_price, si.item_desc, si.item_image FROM store_items AS si LEFT JOIN store_categories AS c on c.id = si.cat_id WHERE si.id = '".$_GET["item_id"]."'";
 
$get_item_res = mysql_query($get_item_sql, $mysql_connect) or die(mysql_error( ) );