Grouping data from 2 tables

Anon

Hello,

I would like to be taught about how can I manage this:

1.- I have two mysql tables (Products and Categories).
2.- Tipical 'Products' entries are:

ID - P_NAME - P_Category_ID - P_DESCRIPTION
1 - Mouse - 1 - Microsoft mouse..
2 - Canon BJ-200 - 2 - Canon BJ-200 Buble Printer.
3.- Helwet-Packard - 2 - Last HP printer..
4.- Quick-Cam - 3 - Color USB Quick-Cam
...

3.- Tipical 'Categories' entries are:

ID - C_NAME - DESCRIPTION
1 - Mouses - List of mouses.
2 - Printers - List of printers.
3 - Cams - List of cams
...

3.- now I would like to search products and display them grouping by categories. I do know how to make a search and how to order results but I don't know how to grouping these records by categories. For instance:

Top > Mouses
1. Mouse: Microsoft mouse

Top > Printers
2. Canon BJ-200: Canon BJ-200 Buble Printer.
3. Helwet-Packard: Last HP printer..

Top > Cams
4. Quick-Cam: Color USB Quick-Cam

Sorry for my poor writing english and thanks in advance.

Toni

Anon

Try:

SELECT a.,b. FROM Products a,Categories b
WHERE a.P_Category_ID=b.ID
GROUP BY a.P_Category_ID
ORDER BY a.P_Category_ID

Anon

I'll probably get shot by doing this but I did something similar this way:

print ("<table border='0'>");

//first query
$sql = mysql_query(SELECT * FROM categories);
while($row = mysql_fetch_row($sql))
{
$c_name = $row[1];

print ("<tr><td>$c_name</td></tr>");

//second query
$sql2 = mysql_query(SELECT * products)
while $row2=mysql_fetch_row($sql1))
{
$p_name = $row2[1];
$p_description = $row2[3];
print ("<tr><td>$p_name | $p_description</td></tr>");

}

}
print ("</table>");

Or something to that effect.
scott d~

Anon

We won't shoot you, but just point out that joining the
tables as max suggested allows you to perform a
single query, whereas yours performs <i>n</>+1 queries
(where <i>n</i> is the number of rows in the category
table.) This is sure to be a performance killer.

Anon

Yeah...I'll definitely be adopting that aproach in the future!!!

rgds,
scott d~