...

View Full Version : how to do this in php



xiaodao
01-31-2010, 01:48 AM
Hi Friends,

Table 1

catid category
1 fruits
2 meats


table 2
foodid catid food
1 1 banna
2 1 apple
3 1 tomato
4 2 beef
5 1 grape
6 2 chicken


now i want to count the number of food under each category in SINGLE select, is it possible to do? which means to get " Category fruits have 4 fruits" and "category meat have 2 meats".

My currently my way is to select the category, then stored as array, then loop through the array, then select again from the food, i think this is not efficient at all.

Len Whistler
01-31-2010, 02:48 AM
Use the WHERE clause in the mysql query when you count the rows in table2.


WHERE catid = 2


-------------

xiaodao
01-31-2010, 10:14 AM
what do you mean, this is my way of doing

$sql=mysql_query("SELECT * FROM category ORDER BY catid DESC");
$sum=array();
while($r=mysql_fetch_array($sql)){
$sql2=mysql_query("SELECT * FROM food WHERE catid=".$r['catid']);
$total=mysql_num_rows($sql2);
$sum[$r['category']]=$total;
}

this is my code, i want to short cut in to one query, possible?

nobackseat88
01-31-2010, 01:50 PM
<?php
$query = mysql_query("SELECT `category.catid`,`category.category`,`COUNT(foodid)` FROM `food`, `category` WHERE `food.catid` = `category.catid`");
while($row = mysql_fetch_array($query)){
echo "Category " . $row['category'] . " has " . $row['COUNT(foodid)'] . " fruits.";
}
?>


Erm...something like that. 0.0

Good luck!

xiaodao
01-31-2010, 02:16 PM
cool, damn, i should attend the Microsoft SQL lesson in my school



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum