[SOLVED] Working with Dates - Y-m-d MySQL

[TRI0N]

Okay I'm working with MySQL standard date format Y-m-d (2007-5-14).  Now what I would like to do is the following with PHP.

 

Extract only this weeks records (7 days):

 

Extract only this months records: (All Records for the month of May 2007):

 

 

Any help on making this work would be great.  I tried searching the forums for "Query Date by Month" and got too much information that didn't even have what I was looking for.

 

 

Best regards,

 

TRI0N

[Barand]

<?php
/**
* this week's data'
*/
$sunday = date('Y-m-d', strtotime('last sunday'));
$saturday = date('Y-m-d', strtotime('next saturday'));

$sql = "SELECT * FROM mytable WHERE datecol BETWEEN '$sunday' AND '$saturday' ";

// or

$sql = "SELECT * FROM mytable WHERE datecol BETWEEN '$sunday' AND '$sunday' + INTERVAL 6 DAY";

/**
* this month's data'
*/
$month = 5;
$sql = "SELECT * FROM mytable WHERE MONTH(datecol) = $month";
?>

[TRI0N]

Okay lets see..

 

 

Now let me use a little bit of my code to make sure this is going to work:

 

$month = date('m') ;

$sql = "SELECT * FROM orders WHERE MONTH(d_date) = $month" ;

 

The above orders is the database name and d_date is the row that the Due Date is stored.  So if this correct it should work based on what was given above for an answer.  Correct me if I'm wrong.

 

The 7 day idea looks like it is only pulling the data out base on that week period rather then next 7 days.  This is acceptable but is there a way to pick just the next 7 days?

[Barand]

... WHERE d_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 DAY

[TRI0N]

Thank you so much for you help on this.. All is working Great!