0
votes

I have a query in MySQL that works fine, however when i copy the query over into my PHP file, it is throwing me a syntax error: ** unexpected ") as week_ending ** what am i missing?

MySQL:

'SELECT COUNT(*) as count,region, DATE_FORMAT(NOW(),'%d %b %y') as week_ending FROM stores.stats WHERE date > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date < DATE(NOW()) GROUP BY region, DATE(NOW())';

PHP:

$stmt = DB::query(Database::SELECT, 'SELECT COUNT(*) as count,region, DATE_FORMAT(NOW(),'%d %b %y'), as week_ending FROM stores.stats WHERE date > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date < DATE(NOW()) GROUP BY region, DATE(NOW())';

2

2 Answers

4
votes

There are a couple single quotes in the middle of your SQL that are prematurely terminating your request. You just need to escape the quotes near here:

DATE_FORMAT(NOW(),'%d %b %y'),

Like this:

DATE_FORMAT(NOW(),\'%d %b %y\'),
2
votes

Besides not escaping contained quotes, you're also not closing the method call.

$stmt = DB::query(
    Database::SELECT,
    'SELECT COUNT(*) as count,region, DATE_FORMAT(NOW(),\'%d %b %y\'), as week_ending FROM stores.stats WHERE date > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date < DATE(NOW()) GROUP BY region, DATE(NOW())'
);