Andrey Tsarev Andrey Tsarev - 6 months ago 11
PHP Question

MySQL Query Date Errors

I have this query that indexes my images and orders them by popularity but I cant make the user to choose the interval cause there's something wrong with the query:

switch($Data['data']){
case 'daily':$QueryDate='=CURDATE()';break;
case 'weekly':$QueryDate=' BETWEEN SUBDATE(CURDATE(), INTERVAL 7 DAYS) AND NOW()';break;
case 'monthly':$QueryDate='>CURDATE() - INTERVAL 31 DAYS';break;
default: Core::redirect('image/browse/daily/1');break;
}
$IMGDB = new Database('images');
$query = "SELECT *, (derived.`likes` * 2 + derived.`views`) as `popularity` from
(SELECT *,
(SELECT COUNT(*) FROM `likes` WHERE `like`=I.id AND `date`".$QueryDate.") AS `likes`,
(SELECT SUM(`views`) FROM `views` WHERE `id`=I.id AND `date`".$QueryDate.") AS `views`
FROM images AS I
) AS derived
where 1 ORDER BY `popularity` DESC ";


Only the daily case works.

Here is the error:


SQL Error (1064): You have an error in your SQL syntax;..... to use near 'DAYS) AND NOW()) AS likes, (SELECT SUM(views) FROM views WHERE id= I.id A

Answer

The correct syntax for specifying an interval of days uses the DAY keyword. You've used DAYS in:

BETWEEN SUBDATE(CURDATE(), INTERVAL 7 DAYS) AND NOW()

and:

> CURDATE() - INTERVAL 31 DAYS