How to count(distinct row) in PHP

PHP
1

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)

2

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:

3

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?

Peter

4

Pete: your query should be like this:


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

:slight_smile:

5

Thanx for that!

Works…

Peter

6

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!

7

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

8

I’m referring to the very last query:

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

for count distinct it was discussed before - look here:

http://www.sitepointforums.com/showthread.php?threadid=10698&highlight=count+distinct

10

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

11

Yeah, that surprised me. :slight_smile:

12

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);

13

Opps…

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

Pete