MYSQL Select by this week with index

Question

User can see what they have uploaded in this week. I have below code in line:

Is this correct?

SELECT * 
FROM images 
WHERE userid = '$userid'
   AND uploadeddate >= CURDATE() - INTERVAL WEEKDAY(day) DAY 
   AND uploadeddate < CURDATE() - INTERVAL WEEKDAY(day) DAY + INTERVAL 7 DAY 
ORDER BY uploadeddate DESC

I have create index for (userid, uploadeddate) .

what error? do you understand what your query selects? please see the guideline for asking questions (http://stackoverflow.com/questions/how-to-ask_). this is not "someone will do my homework" site. — Karel Frajták, Apr 18 '13 at 00:28
You [just asked this question](http://stackoverflow.com/questions/16071934/mysql-select-by-this-week) — Kermit, Apr 18 '13 at 00:30
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Apr 18 '13 at 01:06

score 0 · Accepted Answer · answered Apr 18 '13 at 00:53

For your original query to work day needs to be in the database you're querying (or set as a variable) and it needs to be in a MySQL date '0000-00-00' or date time '0000-00-00 00:00:00' formats.

WEEKDAY() returns 0-6 for the date entered.

$first; //is earliest offset in the range
$last; //is the latest offset in the range

SELECT * FROM images WHERE userid = '$userid'
AND uploadeddate > CURDATE() - INTERVAL $first DAY
AND uploadeddate < CURDATE() - INTERVAL $last DAY
ORDER BY uploadeddate DESC

So a query like this would return results for last week.

SELECT * FROM images WHERE userid = '$userid' AND
uploadeddate > CURDATE() - INTERVAL 14 DAY
AND uploadeddate < CURDATE() - INTERVAL 7 DAY
ORDER BY uploadeddate DESC

MYSQL Select by this week with index

1 Answers1