How to count(distinct row) in PHP

My question is simple:

Why does this work:
mysql_query(“SELECT COUNT(*) FROM stories”, $con);

When this doesn’t?
mysql_query(“SELECT COUNT(DISTINCT column) FROM table”, $con);

(The last one is, of course, what I want to do)

Try this:

mysql_query("SELECT DISTINCT COUNT(column) FROM table", $con);

I think that should work…I mean, I hope that will work. :slight_smile:

Following onto this thread, maybe a little off topic but is it possible to count the number of rows that a certain field has

E.G, I have a joined query, which there are different amounts of records, for different things. So i was wanting to do this:

mysql_query("SELECT COUNT(subid), text, name, author FROM table1, table2");

Except it doesn’t work. Is there a way i can do this?


Pete: your query should be like this:

$result = mysql_query("SELECT COUNT(subid) AS thecounter, text, name, author FROM table1, table2");
$thecounter = $result["thecounter"];


Thanx for that!



That still shouldn’t work. As MySQL will tell you, you can’t mix grouping functions with non-grouped columns without using a GROUP BY clause. This is the error you’ll get:

ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()…) with no GROUP columns is illegal if there is no GROUP BY clause

The problem is that COUNT(subid) returns a single value, while text, name, and author returns a value for each combination of rows from the two tables. MySQL can’t return a single row in one column and multiple rows in another column. It just won’t work!

Rethink what you’re trying to do and see if you can come up with a more sensible way of getting the information you’re after. We’ll be happy to help!

To which block of code are you referring to Kevin? The first?

I’m referring to the very last query:

$result = mysql_query("SELECT COUNT(subid) AS thecounter, text, name, author FROM table1, table2");

for count distinct it was discussed before - look here:

Oh, my mistake. I think Pete jumped the gun then, because he said it worked. :slight_smile:

Yeah, that surprised me. :slight_smile:

Yup, got it working… It’s true that you can’t put it all in one query though - I had to do it like this:

$result = mysql_query(“SELECT distinct ip from tablename”);
$count = mysql_num_rows($result);


I have a variable $i and i echoed that instead of $c which is what i was using for count.

Nothing works if i try to do it, just echos $i which happened to be 1