PHP how to display rows from two tables

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:开发者_如何学Go

   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?

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.

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