Exact copy of the query, and how do I find out my mySQL version?

select version()

Result is : 4.1.18-standard-log

can you run off a data dump of the database so I can rebuild the structure and test

email it to me I'll see if I can recreate the error.

the table alias with the count function is the problem

simply a.*, count(*) AS music works ok

odd it worked ok with my earlier testing, but heyho.

So it would be...

SELECT b.realname,a.*, count(*) AS `music` FROM `sounds` AS a LEFT JOIN `staff` AS b ON b.id = a.staffid GROUP BY `category` WHERE `category` = CONVERT(_utf8 'Front Gate' USING latin1) COLLATE latin1_general_ci ORDER BY `id` ASC

And that didn't work either.

#1064 - 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 'WHERE `category` = CONVERT( _utf8 'Front Gate' USING latin1 ) COLLATE latin1_gen' at line 1

take the single quotes from around the column names, these are only required when you are trying to use reserved words as field names.

I can't test with the JOINs the dump only had the one table.

SQL Query :

SELECT b.realname,a.*, count(*) AS music FROM sounds AS a LEFT JOIN staff AS b ON b.id = a.staffid GROUP BY category WHERE category = CONVERT(_utf8 'Front Gate' USING latin1) COLLATE latin1_general_ci ORDER BY `id` ASC

Result :

#1064 - 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 'WHERE category = CONVERT( _utf8 'Front Gate' USING latin1 ) COLLATE latin1_gener' at line 1

lol, this is probably really annoying you as much as it is me.

a WHERE clause has to be before a GROUP BY

Alright, I got that one to work, but this one, on the actual page to display the songs...

$result = mysql_query("SELECT * FROM `sounds` WHERE `category` = $category ORDER BY `songname` ASC");

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 'ORDER BY `songtitle` ASC' at line 1

Any idea why?

wrap $category in single quotes as it is a string

Originally Posted by Reality15

Alright, I got that one to work, but this one, on the actual page to display the songs...

$result = mysql_query("SELECT * FROM `sounds` WHERE `category` = $category ORDER BY `songname` ASC");

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 'ORDER BY `songtitle` ASC' at line 1

Any idea why?

Try this...


$result = mysql_query("SELECT * FROM `sounds` WHERE `category` = '$category' ORDER BY `songname` ASC");

It should correct the error (hopefully).

Edit: - Started typing this before Chris' response.

Works great now! Thanks for the continuous support guys!