[SOLVED] Show Older Entries

[lalabored]

I have a MySql table with the field "sentdate". How could I set up a query that selects only entries that have a sentdate that is for example 30 days old?

[cooldude832]

is sentdate a timestamp/date field? if so then look into date functions.  You can use any of your || && != > <  operators for comparisons of dates as long as your date is in the same format as the dates in the mysql database

this is a good reference to dates

http://www.w3schools.com/php/php_ref_date.asp

[pocobueno1388]

Try this:

 

<?php

mysql_query("SELECT * FROM `table` WHERE `sentdate` = Date() - 30")or die(mysql_error());

?>

[lalabored]

Try this:

 

<?php

mysql_query("SELECT * FROM `table` WHERE `sentdate` = Date() - 30")or die(mysql_error());

?>

 

That didn't work =S

 

I tried using this...

 

$old = mktime(0, 0, 0, date("m"), date("d")-1, date("Y"));
$q = "SELECT * FROM `p_messages_sent` WHERE UNIX_TIMESTAMP(sentdate) < $old";
$rs = mysql_query($q);

 

..to get rows that were older than 1 day (because I didn't have anything older than 30 days) but that didn't work =\

[cooldude832]

that's because $old isn't a date its a data object so to speak.  You need to say $old = date(format you like, $old);

[lalabored]

that's because $old isn't a date its a data object so to speak.  You need to say $old = date(format you like, $old);

 

But how would I compare something made from date() to a MySql date that's like this "0000-00-00 00:00:00"?

[cooldude832]

I use a mysql date field and this snippet

$tempdate =  mktime(0,0,0,date("m"),date("d")-1,date("Y"));

$tempdate = date("Y-m-d", $tempdate);

 

edit changed the day to -1 for your case

[rhyspaterson]

Yeah date is pretty configurable, pretty sure you can modify it's output to match whatever you want

[lalabored]

I use a mysql date field and this snippet

$tempdate =  mktime(0,0,0,date("m"),date("d")-1,date("Y"));

$tempdate = date("Y-m-d", $tempdate);

 

edit changed the day to -1 for your case

 

Ooh! I see, thank you very much.

[cooldude832]

did that work?  if so mark your topic solved please