Results within Products own category

[harryhermit]

Hello All,

I'm diving straight into php without knowing much of how to program (pretty stupid huh?)and of course I have run into a problem. Anyhow, what I am simply trying to do is display items of the same category underneath the item that is currently being displayed, in a box called related items.

Here is my code for displaying the item:

<?
$result = mysql_query("select * from products WHERE item='$item' LIMIT 1") or die (mysql_error());

while ($row = mysql_fetch_array($result))
{

echo "
<table>
<tr>
<td>
<img src=$row[image] border=0 alt=\"$row[name] \"><br>
<b>$row[name] Information & Facts:</b><br>

$row[description]

</td>
</tr>
";
}

I understand how to set simple conditions, but I'm stumped as to what to do next.

[Timotheos]

Hi harryhermit... Welcome to WebmasterWorld!

Best way that I know how to learn php is to do just what you're doing. Get a project, scout around in the manual [php.net] and dive in.

I don't know if I fully understand what you're trying to accomplish. Maybe you could explain any problems you're having. So I'm stumped too as to what to do next.

I did notice that you have a LIMIT 1 on the sql statement which makes it only return one result. Is that what you're after? If so then you wouldn't need the while loop to go through the array.

[harryhermit]

Thanks for your reply. Well there are several categories in the table products ie.:

name¦image¦catname¦category¦link¦item

When calling up an item I use this query condition to bring up a specific item.

"$result = mysql_query("select * from products WHERE item='$item' LIMIT 1") or die (mysql_error()); "

I don't think the Limit is needed, but I need the while loop to go through the array so it can print out the different columns pertaining to the item number, I think.

Anyhow,

I call the item number like ".../topic.php?item=12"

It brings up the page, but I also want a related items box at the bottom that displays items from the same category as "12." And that's where I am hitting a wall.

[Timotheos]

I need the while loop to go through the array so it can print out the different columns pertaining to the item number, I think.

I think you'll find if item is your primary index that it has to be unique and you won't need the loop.

I'm sure there's a nice way to do it all in one query with a join but I'm no expert. Another way would be to get the category

$catagory = $row['category'];

Now use that for another query.

$sql = "SELECT * FROM products WHERE category='$category' AND item<>'$item'";

Now you can loop through the results.

[BitBanger]

Actually you have to do the first query just to determine what the catagory is. Then as Timotheos suggested, do a second query to find all the items that have the same catageory.

On your first query, and possibly the second, you should check to see if any rows have been returned. The code in your inital message will work as is, until the item you are selecting from the database doesn't exist. Then you will output nothing at all.

BTW, the query in the previous message should do what you need.

[coopster]

I'm sure there's a nice way to do it all in one query with a join but I'm no expert.

You will be soon, California ;)

You could join the table to itself:

SELECT 
 t1.*, 
 t2.name AS related_name, 
 t2.image AS related_image, 
 t2.catname AS related_catname, 
 t2.category AS related_category, 
 t2.link AS related_link, 
 t2.item AS related_item 
 FROM products AS t1, products AS t2 
 WHERE t1.item = '$item' AND t1.category = t2.category AND t2.item!= t1.item 
; 

[Timotheos]

Coopster where ya been? Out shoveling [ext.nodak.edu] your driveway?

I knew you'd have the answer ;-)

[coopster]

LOL! Touché, mon ami! I was wondering if you'd come back and see that :)

[harryhermit]

Thanks guys, figured it out with your help. Didn't know how to join it like coop said, but at least its doing what I wanted.

Wrote it like this:

$jkit_category = $row[category];

$jkit_result = mysql_query("SELECT *
FROM products
WHERE category = $jkit_category AND item!='$item' Order by rand() Limit 5 ") or die (mysql_error());
while ($jkit_row = mysql_fetch_array($jkit_result)) {
echo "
<table width=100% border=0 cellpadding=3 cellspacing=0>
<tr>
<td align=left>$jkit_row[name]</td>
<td align=right><a href=product.php?item=$jkit_row[item]>Details</A></td>
</tr>
</table>
";
}