Not returning expected results

lil_bugga · 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>&#163;".$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

lil_bugga · 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

lil_bugga · May 19, 2009

Any idea where I've gone wrong?

Ken2k7 · 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>&#163;".$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.

lil_bugga · 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>&#163;".$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>&#163;".$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>

lil_bugga · May 19, 2009

I'm still at a loss, can someone please help me with this?

Ken2k7 · 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>';
}

Sign In

Not returning expected results

Recommended Posts

lil_bugga

Link to comment

Share on other sites

lil_bugga

Link to comment

Share on other sites

lil_bugga

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

lil_bugga

Link to comment

Share on other sites

lil_bugga

Link to comment

Share on other sites

Ken2k7

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information