counting distinct results from MySQL query using PHP

[rochenko]

I'm running a MySQL join query on two tables, one which contains a list of categories and one which contains a number of links which are grouped by category.

So Table 1 has the following columns

id | category name

where id is the primary key.

The query I'm running is:

select * from categories as r1, links as r2 where r2.id = r1.id

I need to get a count of the number of distinct id's returned by this (there will be a number of records with id=1, several with id=2, etc.).

Is it possible to do this in PHP after the query is run, or must this be done in the SQL using COUNT(DISTINCT) - in which case how do I get the result of the count into a PHP variable?

[LordShryku]

You can do it with PHP. Try putting them in an array, use [man]array_unique[/man], then [man]count[/man].

[rochenko]

Thanks. Could you tell me what's the easiest way of putting the required data (i.e. the entries in the id column only) into a PHP array?

[LordShryku]

$ids=array();
while($row=mysql_fetch_array($result)) {
   $ids[] = $row['id'];
}

[rochenko]

Excellent, thanks. You just brought to an end a week of mental torment. Cheers!