lil_bugga Posted May 19, 2009 Share Posted May 19, 2009 I'm trying to create a bread crumb trail so that users on my website ,http://ben.broxie.co.uk/benwoolner/littleshopper/ ,can jump back a caterogy. At the moment though every time a user clicks to see an items main details the link back to that category seems to always be sci fi. subcat_id cat_id subcat_title 1 1 Mens 2 1 Ladies 3 1 Boys 4 1 Girls 5 2 Compilations 6 2 Pop 7 2 Rock 8 2 Dance 9 3 Books For Beginners 10 3 Childrens Books 11 3 Romance 12 4 Childrens Games 13 4 Driving 14 5 Childrens Movies 15 5 Sci-fi The table above is store_subcategories, and store_items has 75 indervidual IDs consiting of groups of 5 sharing the same subcat_id like below id subcat_id item_title item_price item_desc 1 1 Mens Vest Top 4.00 A sleeveless vest top, ideal for those hot summer ... 2 1 Mens Short Sleeved T-shirt 5.00 A short sleeved, round neck T-shirt. 3 1 Mens Long Sleeved T-shirt 7.00 A long sleeved, round neck T-shirt. 4 1 Mens Hoodie 10.00 Made of thicker material to keep the heat in the h... 5 1 Mens Reversable Rugby Shirt 20.00 A reversable rugby shirt that shows a white stripe... 6 2 Ladies Vest Top 4.00 Vest Top 7 2 Ladies Short Sleeved T-shirt 5.00 Ladies Short Sleeved T-shirt 8 2 Ladies Long Sleeved T-shirt 7.00 Ladies Long Sleeved T-shirt 9 2 Ladies Hoodie 10.00 Ladies Hoodie 10 2 Womans Rugby Shirt 20.00 Womans Rugby Shirt I've tried to create a join to link the tables and get the results I want but I'm still new to PHP and SQL so I think thats where its gone wrong. Below is my code upto the point of the bread crumb trail. <?php session_start(); //connect to server and select database; you may need it $mysqli = mysqli_connect("localhost", "***", "***", "littleshopper"); // sets up queries $get_data_qry = "SELECT item_title, item_price, item_desc, item_image FROM store_items WHERE id='".$_GET["item_id"]."'"; $get_trail_qry = "SELECT store_subcategories.subcat_id, store_subcategories.subcat_title FROM store_subcategories JOIN store_items ON store_items.subcat_id"; // sets up query calls $get_data_res = mysqli_query($mysqli, $get_data_qry) or die(mysqli_error($mysqli)); $get_trail_res = mysqli_query($mysqli, $get_trail_qry) or die(mysqli_error($mysqli)); //get and show 1st level navigation if (mysqli_num_rows($get_data_res) < 1) { $display_content = "<h3>We're Sorry!</h3><p>We've searched out database and couldn't find anything under that category</p>\n"; } else { while ($trail = mysqli_fetch_array($get_trail_res)) { $subcat_id = $trail['subcat_id']; $subcat_title = $trail['subcat_title']; } while ($data = mysqli_fetch_array($get_data_res)) { $item_title = ucwords(stripslashes($data['item_title'])); $item_price = $data['item_price']; $item_image = $data['item_image']; $item_desc = $data['item_desc']; $display_title ="<h3>".$item_title."</h3><br />\n"; $display_price ="<h3>£".$item_price."</h3><br />\n"; $display_image ="<br /><img src=\"".$item_image."\" /><br />\n"; $display_desc ="<br />" .$item_desc. "<br />\n"; } //make breadcrumb trail $display_trail = "<strong><em>You are viewing:</em> <a href=\"/benwoolner/littleshopper/showitems.php?subcat_id=".$subcat_id."\"> ".$subcat_title."</a> > ".$item_title."</strong> I'd be greatful if someone can help me sort this out and explain to me where I've gone wrong and what changes I need to make and why Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/ Share on other sites More sharing options...
lil_bugga Posted May 19, 2009 Author Share Posted May 19, 2009 Basically I want to be able to get the subcat_id and subcat_title coloumns from the store_subcategories based on the selection of an item from the store_items table. For example if i was to choose the womans rugby shirt from the store_items table in the post above I want to retrieve Ladies from the store_subcategories table Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837184 Share on other sites More sharing options...
lil_bugga Posted May 19, 2009 Author Share Posted May 19, 2009 Any idea where I've gone wrong? Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837402 Share on other sites More sharing options...
Ken2k7 Posted May 19, 2009 Share Posted May 19, 2009 while ($trail = mysqli_fetch_array($get_trail_res)) { $subcat_id = $trail['subcat_id']; $subcat_title = $trail['subcat_title']; } while ($data = mysqli_fetch_array($get_data_res)) { $item_title = ucwords(stripslashes($data['item_title'])); $item_price = $data['item_price']; $item_image = $data['item_image']; $item_desc = $data['item_desc']; $display_title ="<h3>".$item_title."</h3><br />\n"; $display_price ="<h3>£".$item_price."</h3><br />\n"; $display_image ="<br /><img src=\"".$item_image."\" /><br />\n"; $display_desc ="<br />" .$item_desc. "<br />\n"; } //make breadcrumb trail $display_trail = "<strong><em>You are viewing:</em> <a href=\"/benwoolner/littleshopper/showitems.php?subcat_id=".$subcat_id."\"> ".$subcat_title."</a> > ".$item_title."</strong> Look at your while loop. It just loops until the end so when you call $subcat_id and $item_title, they're always the last thing. Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837404 Share on other sites More sharing options...
lil_bugga Posted May 19, 2009 Author Share Posted May 19, 2009 while ($trail = mysqli_fetch_array($get_trail_res)) { $subcat_id = $trail['subcat_id']; $subcat_title = $trail['subcat_title']; } while ($data = mysqli_fetch_array($get_data_res)) { $item_title = ucwords(stripslashes($data['item_title'])); $item_price = $data['item_price']; $item_image = $data['item_image']; $item_desc = $data['item_desc']; $display_title ="<h3>".$item_title."</h3><br />\n"; $display_price ="<h3>£".$item_price."</h3><br />\n"; $display_image ="<br /><img src=\"".$item_image."\" /><br />\n"; $display_desc ="<br />" .$item_desc. "<br />\n"; } //make breadcrumb trail $display_trail = "<strong><em>You are viewing:</em> <a href=\"/benwoolner/littleshopper/showitems.php?subcat_id=".$subcat_id."\"> ".$subcat_title."</a> > ".$item_title."</strong> Look at your while loop. It just loops until the end so when you call $subcat_id and $item_title, they're always the last thing. I've tried to amend the code, but I'm still having issues, as I said I'm still learning what I'm doing so can I have a pointer in the right direction with a little more detail so I can follow and learn. while ($trail = mysqli_fetch_array($get_trail_res)) { $subcat_id = $trail['subcat_id']; $subcat_title = $trail['subcat_title']; if (subcat_id = $_GET["item_id"].")"; { break; } } while ($data = mysqli_fetch_array($get_data_res)) { $item_title = ucwords(stripslashes($data['item_title'])); $item_price = $data['item_price']; $item_image = $data['item_image']; $item_desc = $data['item_desc']; $display_title ="<h3>".$item_title."</h3><br />\n"; $display_price ="<h3>£".$item_price."</h3><br />\n"; $display_image ="<br /><img src=\"".$item_image."\" /><br />\n"; $display_desc ="<br />" .$item_desc. "<br />\n"; } //make breadcrumb trail $display_trail = "<strong><em>You are viewing:</em> <a href=\"/benwoolner/littleshopper/showitems.php?subcat_id=".$subcat_id."\"> ".$subcat_title."</a> > ".$item_title."</strong> Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837459 Share on other sites More sharing options...
lil_bugga Posted May 19, 2009 Author Share Posted May 19, 2009 I'm still at a loss, can someone please help me with this? Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837588 Share on other sites More sharing options...
Ken2k7 Posted May 19, 2009 Share Posted May 19, 2009 Go with something like this - <?php session_start(); //connect to server and select database; you may need it $mysqli = mysqli_connect("localhost", "***", "***", "littleshopper"); $item_id = $_GET['item_id']; if (!empty($item_id) && is_numeric($item_id)) { // sets up queries $get_trail_qry = 'SELECT si.item_title, si.item_price, si.item_desc, si.item_image, ss.subcat_id, ss.subcat_title FROM store_subcategories ss INNER JOIN store_items si ON si.subcat_id = ss.subcat_id WHERE si.id = ' . intval($item_id) . ' LIMIT 1'; $result = mysql_query($get_trail_qry); if (!$result) { echo "<h3>We're Sorry!</h3><p>We've searched out database and couldn't find anything under that category</p>\n"; exit; } $row = mysql_fetch_row($result); $display_trail = '<strong><em>You are viewing:</em><a href="/benwoolner/littleshopper/showitems.php?subcat_id='.$row['subcat_id'].'"> '.$row['subcat_title'].'</a> > '.$row['item_title'].'</strong>'; } Link to comment https://forums.phpfreaks.com/topic/158732-not-returning-expected-results/#findComment-837598 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.