ansaurus

Question

PHP/MySQL COUNT doesn't seem to work as it should

Answer 1

+1 A:

What are you trying to count?

Hint: You should probably be using grouping by something if you're using an aggregate function like COUNT()

timdev 2009-09-20 23:42:57

I'm trying to get all the data from locations table (a location is a particular place in the world) and also get the total count so I can split the results over multiple pages (there's a huge number in US).

Solenoid 2009-09-20 23:48:30

Then you need two queries, either two distinct, or a subquery. COUNT() is an aggregate function, so it needs to be used with a GROUP BY clause. The COUNT() for each row will represent how many rows are grouped together.

timdev 2009-09-20 23:56:37

(the exception is a "SELECT COUNT(column) FROM table" type query, where the grouping is implicit.

timdev 2009-09-20 23:57:31

@tim: I don't know what you mean by the grouping is implicit. Using COUNT() without GROUP BY treats the whole set of matching rows as a single group, so the output is a single row.

Bill Karwin 2009-09-20 23:58:51

@bill - correct. using count() without GROUP BY does indeed (implicitly) treat the whole set as a single group.

timdev 2009-09-21 00:06:54

@tim: Okay cool. I just didn't know if you thought it would automatically create groups based on the column argument to the COUNT() function.

Bill Karwin 2009-09-21 00:53:39

Answer 2

+4 A:

Selecting COUNT(id) and * in the same query doesn't make sense. If you really need to get all of the other fields, then just use 'SELECT * FROM ...' and it is easy enough to get the number of rows returned using PHP.

too much php 2009-09-20 23:46:33

Performing the count in the database will always be faster than PHP/etc.

OMG Ponies 2009-09-20 23:47:46

@rexem - in this case probably not. The only way to do what the OP wants is to do two queries. mysql_num_rows() on the resultset from one query will be faster than two queries.

timdev 2009-09-21 00:06:02

On the smallest of result sets, PHP/etc ***might*** be on par with what any database can do. But that approach won't scale as data increases. Those that recommend this approach are usually those who know/use SQL the least if at all.

OMG Ponies 2009-09-21 00:37:33

@rexem - completely wrong. mysql_num_rows() will get the size of the resultset (what the OP wants, here) in O(1) time. Are you claiming that doing a subquery is better than O(1)?

timdev 2009-09-21 00:42:38

@tim: It's your claim, you prove it.

OMG Ponies 2009-09-21 00:45:03

@tim: The `mysql_num_rows()` function only provides the row count *after* you have fetched all the rows from the server. So you either fetch implicitly on execute by using a buffered query, or else you have to fetch all the rows yourself and then count them. Neither of these are O(1) operations.

Bill Karwin 2009-09-21 00:51:32

@bill I'm assuming based on the OP's stuff that he's already going to be fetching all the rows, ie: using plain old mysql_query or equivalent. If that assumption holds, then mysql_num_rows() should give him the size of his result set in O(1). Now, since OP wants pagination, he'd be way better off with a subquery and LIMIT. But given the implementation implied, mysql_num_rows will give him what he wants with less overhead. Unless I'm missing something else, which is possible.

timdev 2009-09-21 01:05:10

Answer 3

+5 A:

Using COUNT() without GROUP BY reduces the result to a single row. You can't get meaningful values in the rest of the columns that way.

You can do it in two queries:

  -- this returns a single row
  SELECT COUNT(id)
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%');

  -- this returns multiple rows, one per matching location    
  SELECT *
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%')
ORDER BY name ASC;

Bill Karwin 2009-09-20 23:53:10

Answer 4

A:

Do you want this?

  SELECT (SELECT COUNT(*) FROM location) as cnt, * 
    FROM location 
   WHERE country = '$country' 
     AND LCASE(namenodiacritics) LIKE LCASE('%$locname%') 
ORDER BY name ASC

Steve Kass 2009-09-21 04:31:39

ansaurus

tags:

views:

answers:

PHP/MySQL COUNT doesn't seem to work as it should

related questions