Geek Answers Handbook

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

I pull my hair for this, which is not the case with this query:

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

Am I allowed to use COUNT (id) and * in the same request?

I keep getting this error:

You have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to '* FROM location WHERE country =' AQ 'AND LCASE (namenodiacritics) LIKE LCASE (' %% '' on line 1

It is strange that it works with "SELECT COUNT (id) FROM ..." as well as "SELECT * FROM ...".

+4
source share
4 answers

Using COUNT() without GROUP BY reduces the result to one row. Thus, you cannot get meaningful values ​​in the remaining columns.

You can do this 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;
+5
source

Choosing COUNT(id) and * in the same query does not make sense. If you really need to get all the other fields, just use " SELECT * FROM ... ", and it's easy enough to get the number of rows returned with PHP.

+4
source

What are you trying to count?

Tip. You should probably use grouping if you use an aggregate function like COUNT ()

+1
source

Do you want it

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

More articles:


All Articles