0

I have two tables namely 'categories' & 'products' as follows:

categories
id | name

products
id | cat_id | name

cat_id in products references the categories table.

Now, I want to display the list of all products on a page, which will have to also display the name of the category the product belongs to(not the cat_id). I am very new in PHP, so am doing the following way:

   foreach($products as $product):
       echo $product->id . $product->name;
       echo getCategoryName($product->cat_id);
   endforeach;

Its obvious that the database call to retrieve the corresponding category name is equal to the number of products to be displayed, and something tells me this is not good at all.

So, is there a better way to do the above? maybe by using SQL JOIN?

Improve this question

2 Answers 2

Reset to default
4

You should use SQL join, which is by far the best and easiest way to select data from two tables.

SELECT 
  p.id AS id, c.name AS category, p.name AS name 
FROM
  products AS p LEFT JOIN categories AS c ON p.cat_id = c.id

You can also use the statement by Trevor, but that's a different type of join. Both will work in this case, as long as the product ID always exist.

Update: A little update on LEFT JOIN and RIGHT JOIN. When you select from 2 tables, you have a left and right table (products is left here, categories is right). The query I gave you will match all products even if the category does not exist. A RIGHT JOIN would match all categories even if there are no products in that category.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@kilZone, it's the same join. Just a different syntax.
@KilZone, when using the LEFT JOIN in this case, what will be displayed if the category does not exist?
Does it also cover things like when there is no category associated with an cat_id? For example when it's removed from the database (normally you would enforce this by foreign keys). What about the other way around? Anyway, I would say always use the explicit JOIN as you have more control over it.
Again, what if the category table had another field named 'status' with value 1 indicating active, and 0 inactive. Now if I want to list only the products of active categories, what will the sql statement look like?
@Damchey it would show the row as normal except that category is empty. So the result would be 1 | | product-name, which is the 'safest' as you will never have unreachable statements in your database (those you can't reach because some keys are not existent). Also note that in my query you can get the data (in PHP) as id, category and name (but you probably knew that already).
|
0

Yeah, you'll need a sql join. Something like this:

categories id | name

products id | cat_id | name

select categories.name, products.id, products.name
from categories, products
where categories.id = products.cat_id
Improve this answer

3 Comments

-1, 1989 called. They want their implicit where join syntax back. Please use explicit join syntax.
aww he is a new user that tried to help... don't kill him off instantly
sorry for being harsh. Implicit where joins just have many problems because they mix join criteria with filter criteria. For this reason they are not considered best practise. They will still work because of backward compatibility but please don't use them because you will not get upvotes in SO :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.