combining 2 queries

[esport]

Hi Guys,

 

Not sure if this is possible. I have 2 tables. 1 table that is a set of categories. The other table is a set of products and categories. What I want is to list each category but then I want to know how many products are in that particular category. I know you can do 2 seperate queries in a loop, but if i have over 100 entries, then it would run the query 100 times. Can I do it so we can have the 1 query?

 

The tables look like this for example:

category(catid,name)

(1, Bikes)

(2, Cars)

products(catid,pid)

(1, car1)

(1, car2)

(1,car3)

(2,car1)

 

Thanks

 

Daniel

 

 

 

 

 

 

[xiao]

You never put a query in a loop as far as I know, you put your array in a loop  :-\

[esport]

I think you miss understood. Here is an example of the way I am doing it at the moment.

 

$res = mysql_query("SELECT * FROM categories",$server);
while($getCat=mysql_fetch_array($res)){
     $catid = $getCat[cat'id'];
     $res2 = mysql_query("SELECT * FROM products WHERE catid=$catid",$server);
     $numRows = mysql_num_rows($res2);
     echo $getCat['name]." has ".$numRows." products<br>";
}

 

Is it possible to cobine this into 1 query? So it doesn't run the second query every loop?

 

[teng84]

  $res2 = mysql_query("SELECT * FROM products WHERE catid in(SELECT id FROM categories)",$server);

maybe