nickobee Posted October 15, 2008 Share Posted October 15, 2008 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 pages comes up Unknown column 'item_id' in 'where clause' (first page works fine) seestore.php <?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 <?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> Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/ Share on other sites More sharing options...
MadTechie Posted October 15, 2008 Share Posted October 15, 2008 do you have the item_id field in the store_item_color table ? also please use code tags (the # button) Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-666609 Share on other sites More sharing options...
nickobee Posted October 16, 2008 Author Share Posted October 16, 2008 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 pages comes up Unknown column 'item_id' in 'where clause' (first page works fine) seestore.php <?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 <?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> Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-666684 Share on other sites More sharing options...
nickobee Posted October 16, 2008 Author Share Posted October 16, 2008 Sorry for missing the code tags in my post, I dont know how to get back into the original message to edit. Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-666699 Share on other sites More sharing options...
darkfreaks Posted October 16, 2008 Share Posted October 16, 2008 unkown column? have you set the id column as primary key ??? Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-666701 Share on other sites More sharing options...
MadTechie Posted October 16, 2008 Share Posted October 16, 2008 do you have the item_id field in the store_item_color table ? Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-666952 Share on other sites More sharing options...
nickobee Posted October 16, 2008 Author Share Posted October 16, 2008 I think I have img_id in the color table? If I remove the below code from the showitem page it works minus the size and color so the problem is in the below code, Im new to php so a little confused, //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); Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-667040 Share on other sites More sharing options...
MadTechie Posted October 16, 2008 Share Posted October 16, 2008 Okay the error Unknown column 'item_id' in 'where clause' is an SQL error from this line $get_colors_sql = "SELECT item_color FROM store_item_color WHERE item_id = '".$_GET["item_id"]."' ORDER BY item_color"; Now when you created the database table "store_item_color" you should have a field called "item_id" but it seam liek thats missing.. so you fix this problem you need to add that to the SQL database table Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-667044 Share on other sites More sharing options...
nickobee Posted October 16, 2008 Author Share Posted October 16, 2008 the table "store_item_color" has 2 fields on is "id " the other is " item_color" should I change the " id" to "item_id" ` I did the same for the store_item_size ( I think this will work. I will try and post results) Thanks Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-667054 Share on other sites More sharing options...
nickobee Posted October 16, 2008 Author Share Posted October 16, 2008 SUCCESS thanks MadTechie you are a star It was the database as you suggested. Id had to be renamed image_id thanks for all your help now Im on to the next chapter! hope all goes smooth, NICK... Link to comment https://forums.phpfreaks.com/topic/128621-solved-im-stuck-on-a-php-tutorial/#findComment-667057 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.