How to get the last 30 days data from mysql database in php?

[angel1987]

I need to display the last 30 days entries from database in PHP and here is the code that i am currently using, but its not working.

 

While entering the data in database, the date format that i am using is this...

$subon = date("F j, Y, g:i a");

 

And to display the code i am using this query...

$start_date = date("F j, Y, g:i a", strtotime('-30 days'));
$curr_date = date("F j, Y, g:i a");
$sql = "SELECT * FROM table WHERE status = 'approved' AND subon BETWEEN '$start_date' AND '$curr_date' ORDER BY ID DESC LIMIT 0, 5";

 

And then i am using the usual stuff to display the data but its not working.

 

Somebody please help me... Thanks in advance.

[awjudd]

Are you using a DATETIME column to store the date or something else?

 

~juddster

[Pikachu2000]

You should be storing dates/times in the proper YYYY-MM-DD HH:MM:SS format in a DATETIME type field in the database, then you can make use of the date/time functions native to MySQL without having to go through hell to do things like this.

[angel1987]

No, the table column is not of DATE TIME type, i had set it as text because when i do it, it stores the date value as 0000-00-00 00:00:00 and it also replaces the previously stored values of date into 0000-00-00 00:00:00.

 

I may be going wrong, i think i need to set a correct format for

$subon = date("F j, Y, g:i a");

do i?

 

Can you please help me with it?

 

And this may be a bit dumb to say but since its a PHP function for date and time, it should work whatsoever right?

 

Thanks again,

[awjudd]

Then that is the problem.  BETWEEN will only work how you want it if the columns are DATETIME.  You should do as Pikachu2000 said to insert the dates in then it should work.  Assuming you change the data type to DATETIME.

 

~juddster

[Pikachu2000]

To insert the current date/time stamp in a DATETIME field, all you need to do is use MySQL's NOW() function.

 

INSERT INTO table ( timestamp_field ) VALUES ( NOW() )

 

I don't know how much code you've written that depends on the format you've currently got, but you'd be best to change it now if it isn't already too late. I would copy the table to new table to work with (or at the very least back it up) then add a new DATETIME field and craft an UPDATE query using STR_TO_DATE() to insert the value in the new field. Once that's done, drop the old field and rename the new one. Then it's just a matter of changing the php code and existing queries to use the correct format.

[angel1987]

Ok, i changed the column type to DATE TIME and also changed the format of date while entering into the database to ...

date("Y-m-d H:i:s");

 

And it looks like it is working since the BETWEEN query is displaying the data.

 

All this on my local test server,

 

But on the live website there are already many user submitted entries, is there any way to work it out there?