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

Question

I'm pulling my hair out for this, what is wrong 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 COUNT(id) and * in a single query?

I keep getting this error:

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

The weird thing is that it works with "SELECT COUNT(id) FROM..." and also "SELECT * FROM...".

Answer 1

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;