CODEWITHSUNDEEP
phpmysqlsqldatabasescripting
ragebunny
ragebunny

Reputation: 1760

Get data from a database that is between two dates

I am writing a system that will show data from a selected month. The user can select the month they want to look at, this then creates a URL something like this .com/page.php?month=03 the page as you can guess will take the idea and then use it to get data from the database from that month.

Now based on the month in the URL i am making to vars, $startdate $enddate start date based on this example is 2011-03-01, i then add 29 or 30 on to the days part of the date depending if the month has 31 or 30 days. Okay so i do the dates like this:

$startdate = mktime(0,0,0,date("$month"),date("01"),date("2011"));
if( $month==04 || $month==06 || $month==9 || $month==11)
{
    $enddate = mktime(0,0,0,date("$month"),date("01")+29,date("2011"));
}
else
{
    $enddate = mktime(0,0,0,date("$month"),date("01")+30,date("2011"));
}

Now when i try to get the data from the database with these variables it doesn't seem to work, this is the query:

$getresults = mysql_query("SELECT * FROM table1 WHERE date BETWEEN $startdate AND $enddate ORDER BY date ASC");

The thing is if i change the vars to hard coded dates its works a treat... Anyone got any ideas? i was thinking it had something to do with the way the day is formatted? in my database its "Y-m-d" and is also the same in the php.

thank for any help people im really stuck on this one.

Upvotes: 1

Views: 2616

Answers (2)

George Cummins
George Cummins

Reputation: 28906

mktime produces dates as Unix timestamps (eg. '1317046059') but your table stored dates in a different format (eg. '2011-09-26'). To make your queries work, you will need to convert your timestamps into the appropriate format:

$enddate = date( 'Y-m-d', mktime(0,0,0,date("$month"),date("01")+29,date("2011")) );

Update:

Based on your new comment that your dates are stored as timestamps in your database, you will need to format your dates in this manner:

$enddate = date( 'Y-m-d h:i:s', mktime(0,0,0,date("$month"),date("01")+29,date("2011")) );

Upvotes: 5

jamesTheProgrammer
jamesTheProgrammer

Reputation: 1777

You should use a couple var_dump statements in key places to evaluate you code. This way, you can know EXACTLY what your data looks like, including its format, then you can figure out how to format that date the way you need to.

Upvotes: 1

Related Questions