How can I make this code more effective?

Anon

Hi!
I am having the following code which will query the database as many times as there are elements in the array, depending on the number of elements any results this may take quite some time. Now I wonder if there is any way to make this code more effective (i.e. somehow just do one query)?
I am using a MySQL-Database and PHP3.
Ciao
Sascha

$myarray = array("2"=>"Albania","7"=>"Armenia","9"=>"Austria");
for(reset($myarray); $key = key($myarray); next($myarray)) {
print "<td align='right'><a href='cat.php3?l=$key'>$myarray[$key]</a></td>
<td>: ";
$query = "SELECT * FROM $table WHERE country = '$key'";
$result = mysql_db_query("$DB", $query);
$number = mysql_num_rows($result);
print "$number</td></tr><tr>";
}

Anon

I'm not sure about having just one query, but changing $query might help. If all you want is a count for each county try this:-

$query = "SELECT COUNT(*) FROM $table WHERE country = '$key'";

$result = mysql_db_query("$DB", $query);
$tot = db_fetch_row($result);
$number = $tot[0];

I'm fairly new to PHP, so there may be a better way, but this returns one column and row in the query.

Anon

The same code, only much tighter:

// generate a where clause to only look for your specific keys
// in the query...generates a clause like
// where key in ('2', '7', '9')
$query = "SELECT key, count(*) from $table WHERE key in ('" . implode("', '", $myarray) . "') GROUP BY key ORDER BY key";

// execute the query, only once needed
// ok so not ideal error checking
$result = mysql_db_query($db, $query);

// now just print everything out
while ( $row = mysql_fetch_row($result) ) {
print "<tr><td align=\"right\"><a href=\"cat.php3?l=$result[0]\">$myarray[$result[0]]</a></td> <td>: ";
print "$result[1]</td></tr>";
}

I find using my implode technique for IN's to be very easy to read and much less code (no stripping of last comma, etc.)

Anon

Hi!
Thanks a lot for your hints, but I can't get it to work.
I tried this code, but now it doesn't seem to find anything, because I get the following two errors:
Warning: 0 is not a MySQL result index on line 18
and
Parse error: parse error, expecting `']''
on line 19
Can you help me to fix this?
Here is the code:
15 $myarray = array("2"=>"Albania","7"=>"Armenia","9"=>"Austria");
16 $query = "SELECT key, count(*) from $table WHERE key in ('" . implode("', '", $myarray) . "') GROUP BY key ORDER BY key";
17 $result = mysql_db_query($db, $query);
18 while ( $row = mysql_fetch_row($result) ) {
19 print "<tr><td align=\"right\"><a href=\"cat.php3?l=$result[0]\">$myarray[$result[0]]</a></td> <td>: ";
20 print "$result[1]</td></tr>";

Anon

$result is not an array there, unless I'm totally missing something

You should be using

$myarray[$row[0]]

All in all, that looks like very confusing coding and I would probably figure out how to make it easier to read.