Retrieving records created on or after Monday of the current week

[peppericious]

I am inserting NOW() in a datetime field on creation of new records.

 

I want to have a "show this week's entries" link which will display records created on, or since, Monday of the current week.

 

How can I do this? E.g.

$q = "SELECT col1, col2 FROM table WHERE creation_date [blah blah]";
$r = mysqli_query($dbc, $q);

 

Thanks in advance for your help.

[peipst9lker]

You can use mktime() and date() to get the last monday and then convert it into datetime format.

 

Here's a little snippet for datetime conversion, the rest is for you because I don't have much time right now.

 

function strToDateTime($input)
{
return preg_replace('/^(\d{2}).(\d{2}).(\d{4}) (\d{2})\d{2})\d{2})$/', '$3-$2-$1 $4:$5:$6', $input);
}

 

Edit: Forgot to mention, you can use > and < operators in datetime where-clauses.

[Barand]

or

 

SELECT col1, col2 FROM table WHERE WEEK(creation_date,5) = WEEK(CURDATE(),5)

[peppericious]

That's just perfect, barand, thanks.