Posted by Christoph Burschka on 11/20/06 22:20

Michael Fesser wrote:
> .oO(Akhenaten)
>
>> Actually fixed it using the following:
>>
>> $arr = array ("A", "B", "C", "D", "E");
>> foreach ($arr as $client) {
>> $query = mysql_query("SELECT * from table where columnA = '$client' ");
>> $num_rows = mysql_num_rows($query);
>> echo "$client has $num_rows";
>> echo '<br>';
>>
>>
>> Unsure as why but for whatever reason I simply can't get a value using
>> count <pounding head on keyboard>.
>
> As said, it requires a mysql_fetch_* function to get the results from a
> query. Your "fix" above is just an ugly hack. Additionally with some
> more SQL and a GROUP BY clause you could drop the foreach loop and do it
> all with a single query, something like
>
> SELECT columnA, COUNT(*) AS count
> FROM table
> WHERE columnA IN ('A', 'B', 'C', 'D', 'E')
> GROUP BY columnA
> ORDER BY columnA
>
> Micha

And since one of the bottle-necks in a web application is the time it takes for
the database to process the query and return the result set, it makes sense to
minimize the number of queries. The above is the best way to go. Use it in this
code:

$sql = "...[shown above]";
$res = mysql_query($sql);
$counts=array();
while ($row=mysql_fetch_array($res)) $counts[$row['columnA']]=$row['count'];

in the end, $counts will be an array containing all the letters as keys and the
associated counts as values.

--
Christoph Burschka

[Back to original message]