Select Problems

Anon

Hello,
I'm a noob, and have been trying to figure out a problem for a couple of days. I have a product catelog with 3 tables in it.

Categories
-id
-parent_id
-name

Products
-id
-name
-price
-url
-imagefile

products_categories
-product_id
-category_id

I'm trying to display products from a paticular category. Here is the php that I used.

$result = mysql_query ("SELECT product_id FROM products_categories where category_id = $catid");

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

echo "<a href=\"" . $row["url"] . "\"><img src=\"" . $row["imagefile"] . "\" border=0 alt=\"" . $row["name"] .

"\"><BR>" ;

When the page displays, I get red x boxes of all the products. No other text,image or info is displayed. And all the boxes link to http://localhost. I'm asuming my select statement is wrong. That I'm quering the results from my products_categories table. But I'm not sure. Also after a few days of looking online for an answer, I have found that a join might be the answer. Can anyone help me with this one? Any help would be great. Thanks in advanced.

sp0rk

You're only selecting the product_id, so you get what you ask for, which isn't a whole lot. =)

Try something like this:
$result = mysql_query("SELECT * FROM products_categories WHERE category_id = $catid") or die(mysql_error());

If the query fails, mysql_error() will tell you what the error returned was.

(You'd probably get a quicker answer in the database-forum...)

cahva

Yep, you have lots of bugs in your code. What your code does right now is, that it selects only the products_categories-table when it should use all of them. You dont get $row["url"],$row["name"] and $row["imagefile"], because you didnt join the tables together.

And you dont have relative id for the products table so how do you know which category the product belongs to?

Ok, my advice is: lose the products_categories table and do the tables like this:

categories
-id
-parent_id
-name

products
-id
-cat_id
-name
-price
-url
-imagefile

Now its much better.. So now if you want to get all products which belongs to certain category, your code should be like this:

$query="SELECT categories.name,products.name as name2,price,url,imagefile FROM categories,products WHERE categories.id = '".$catid."' and categories.id = products.cat_id";

$result=mysql_query($query);

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

echo "<a href=\"" . $row["url"] . "\"><img src=\"" . $row["imagefile"] . "\" border=0 alt=\"" . $row["name2"] . "\"><BR>";
}

Notice that I changed the products.name to name2 because you use mysql_fetch_array and those could get mixed up in the code.

sp0rk

Listen to this guy instead. I shouldn't be giving advice on mysql after being awake for 22 hours...

Anon

Thanks so much for the help. I'm very new aat this. I'm going to try it this morning. I'm a little hesitant about getting rid of the third table. I didn't want to duplicate products in my products table, with different cat id. But I think I can add more than one value in the cat id field for any product. But I think that is an easy question, I'll have to figure out for myself. Thanks again for the help.

Jeremy

cahva

Ok, I couldnt know that product could be in many categories. If thats the case then by all means keep the third table!

You probably got the idea from my code how to join the tables together, so you can make the query by yourself with the 3 tables.

Anon

thanks for the help guys I used

$result = mysql_query("
SELECT products.url, products.imagefile, products.name FROM products_categories INNER JOIN products ON products_categories.product_id=products.id
WHERE products_categories.category_id = '$catid' ");

And that worked out fine. Thanks again for the help. I've been to many boards were peolpe treat noobs like a waste of time. But not here. Thanks.

Jeremy