MySQL 1064 syntax error cant find error

Question

can someone please help me with this sql query. it gives me a 1064 error suggesting I have a syntax error in my query. I've looked for the error over 2 hours now and getting frustrated now.

SELECT 
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 6 MONTH)) AND (`date` < DATE_SUB(?, INTERVAL 5 MONTH)), earnings, 0)) AS Rev5,
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 5 MONTH)) AND (`date` < DATE_SUB(?, INTERVAL 4 MONTH)), earnings, 0)) AS Rev4,
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 4 MONTH)) AND (`date` < DATE_SUB(?, INTERVAL 3 MONTH)), earnings, 0)) AS Rev3,
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 3 MONTH)) AND (`date` < DATE_SUB(?, INTERVAL 2 MONTH)), earnings, 0)) AS Rev2,
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 2 MONTH)) AND (`date` < DATE_SUB(?, INTERVAL 1 MONTH)), earnings, 0)) AS Rev1,
 SUM(IF( (`date` >= DATE_SUB(?, INTERVAL 1 MONTH)) AND (`date` < ?), earnings, 0)) AS Rev0
FROM
 hat_adsense_stats
GROUP BY 
 domain
ORDER BY
 domain

thanks here is the error:

Error Code: 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 '?, INTERVAL 1 MONTH)) AND (date < ?), earnings, 0)) as Rev0 FROM hat_adsens' at line 2

Are you using PDO, mysqli, or some other syntax to bind the values to your parameters? If so, are you binding the correct parameter type? — rink.attendant.6
– rink.attendant.6, Commented Jan 21, 2013 at 14:15
@SantoshGhimire: and please make your comments readable by not using leet speak. — user330315
– user330315, Commented Jan 21, 2013 at 14:22

John Woo · Accepted Answer · 2013-01-21 14:13:51Z

3

You have parameter place holders on your query, which do not work on pure sql. The only time ? works is when you create a Dynamic SQL.

If you are working with query that is not a Dynamic SQL, you should provide a value for that.

On example to remove the syntax error on the statement is to create a user variable, eg

SET @date = CURDATE();
SELECT 
 SUM(IF( (`date` >= DATE_SUB(@date, INTERVAL 6 MONTH)) AND (`date` < DATE_SUB(@date, INTERVAL 5 MONTH)), earnings, 0)) AS Rev5,
 SUM(IF( (`date` >= DATE_SUB(@date, INTERVAL 5 MONTH)) ........

answered Jan 21, 2013 at 14:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Schalk Keun Over a year ago

thanks that is the problem! :) i got the original code from a friend and tested it in normal sql-yog. I use it in PHP and MySQL PDO. when working in the code environment it works 100%.

John Woo Over a year ago

when you are using PDO, just leave the ? as the values should be parameterized.

Collectives™ on Stack Overflow

MySQL 1064 syntax error cant find error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related