How to extract information from the database for the last 30 days?

Here are my details which extracted information from the database, I need to extract only the last 30 days?
How can I do?

$oldDate=date("F j");

$res =  mysql_query("SELECT count(id) as nr FROM work WHERE day = '$oldDate' ORDER BY group ASC, school ASC, day ASC, hour ASC");

$row = mysql_fetch_assoc($res);

if($row['nr'] == 0){
	$oldDate=date("F j",mktime(0, 0, 0, date("m"),date("d")-1,date("Y")));
}

$qu = "SELECT * FROM work ";

if (isset($_GET['page']) && $_GET['page'] != null && $_GET['page'] != '') {
	$qu .= 'WHERE group = "' . $_GET['page'] . '" AND day != "" ORDER BY group ASC, school DESC, day ASC, hour ASC';
}
else {
	$qu .= "WHERE day = '" . $oldDate . "' ORDER BY group ASC, school ASC, day ASC, hour ASC";
}

Code (markup):

Thanks in advance for help,
Floriano

 

Are you storing the timestamps? If so, you can do a SELECT with the WHERE set to -30 days...If not, then it might be a bit more complicated.

 

Where to set -30 days?
Can you show me please?

Regards,
Floriano

 

Hi,

check TIMESTAMPDIFF function and examples.
Regards

 

Someone can help me?
Another example please, I does not understand.


Regards,
Floriano

 

hi

there is only one basic question

Do you have Date [ timestamp ] stored in database?

if yes then it is simple.

batter show your table structure.

i will explain you what to do but first answer the above question.

thanks
Dragon.

 

Hi Dragon,

is not stored in database.

Regards,
Floriano

 

You need to store the timestamps. For example: 1242834344 is a unix timestamp. If you are not doing this, there might be more that needs to be done, I've always stored as timestamps so I never had too much of a problem. Someone here I'm sure can help, unless you are able to convert over, then we can help there too.

 

Where I should to store "timestamp" in the script or database table?
Can you give me an example of how to write "timestamp"?

Best Regards,
Floriano

 

a timestamp is simply time() in php, so you are just storing a number. It would involve modifying the working method of the script. You have to save the time variable then you can just do ((60*60)*24*30) and subtract it.

So:
SELECT * FROM tablename WHERE (timestamp <= time() AND timestamp>= time()-((60*60)*24*30))

But that is just a puesdo method. I could do it for you, we could work something out.

 

I insert this function: day > (CURDATE() - INTERVAL 30 DAY) , to extract the last 30 days, but not working.
Where is the mistake?

Someone can help me please?

Thanks in advance,
Floriano

 

in order to pick the last 30 days ...IF your database is using TIMESTAMP , then you can convert the TIMESTAMP row into an actual date or a raw date of seconds in PHP time. then do the math and subtract 30 days worth from it. its gonna be a big number like 3563564563456 .. which i just made up.
Figure out the PHP time in a day (search Google or PHP.net)

 

Thanks very much, I solve the problem.

Best Regards,
Floriano