[SOLVED] Selecting a time range

[Grant Holmes]

I have code (that is working) that selects "today" to display a Happy Birthday wish to those in my DB- on a web page. On another page, I want my page to display things only "current"- what I'm defining as from the last 48 hours. In my database I have a field called "DateEntered" that is a time stamp from SQL on record creation. Here is the code for my birthday pull:

<?
include("dbinfo.inc.php");
$tDay = date("m-d");
$fromDay = $_REQUEST["f_date"];
$toDay = $_REQUEST["t_date"];
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if (!empty($_REQUEST["t_date"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';
} else if (!empty($_REQUEST["showall"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays';
} else if (!empty($_REQUEST["id"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND id='.$_REQUEST["id"];  
} else {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where `Active` = 1 AND DATE_FORMAT(Cbirthdate,"%m-%d") = "'.$tDay.'"';
}

$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();
?>

 

The date in DateEntered is saved in this format: 2008-01-03 08:03:24

 

Is there an easy way to modify this script, or would I be better off starting over. Ima Newbie, just so ya know! Thanks for your help.

 

I've done a simple edit, changing DateEntered for Cbirthdate, but that didn't work.

[revraz]

Since you are using MYSQL date and time, check out this link

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[Grant Holmes]

Revraz, thanks for your continued help. As a newbie, that is both really helpful and TMI all at once.

 

So would the "HOUR()" function return the Hour value of that field? That's what I read, but I want to be sure I understand the list.

 

Then I look at the DateDiff, which I guess I could use, but that appears to be hard numbers as opposed to the value of the field against today.

 

More help please?

[revraz]

Below all the functions, look at the first example that uses the INTERVAL statement.

[Grant Holmes]

I see the Diff_Add and Diff_Sub as the most likely. So in trying to interpret this, is this the right path?

In this simple example I'm trying to pull ANY record that is fewer than 48 hours old from the hard date entered. However, I'm not getting any results in any of these tests:

 

$query="SELECT DATE_SUB('2008-01-03 00:00:00',  INTERVAL '48' HOUR)";

$query="SELECT DATE_SUB('2008-01-03 00:00:00', - INTERVAL '48' HOUR)";

$query="SELECT '2008-01-03' - INTERVAL 48 HOUR;)";

 

[Grant Holmes]

In other statements, I say "Select x FROM TABLE...."

 

I don't see in these statements where I tell mysql what table I'm selecting the data from. So I modified the SELECT like this, but still no returns:

$query="SELECT FROM birthdays DATE_SUB('2008-01-03 00:00:00', - INTERVAL '48' HOURs)";

 

Still no returned fields. In what I'm reading this should return ANY record in my table created in the last 48 hours, no? Or do I still need to tell it which field has that data in it?

 

[Grant Holmes]

ON this website offering snippets I found some help and edited it to:

$query="SELECT DateEntered from birthdays where hour(DateEntered)< 48";

 

Now I am getting records but the ECHO fields are empty, producing an empty table with no data. Changing the number (48) does return different results, so I'm very close.

 

Any other help?

[revraz]

The only thing you are SELECTing is DateEntered.  You need to SELECT everything you want returned.

[Grant Holmes]

So:

$query="SELECT * from birthdays where Active='1' AND Event='request' AND hour(DateEntered)< 48";

 

Is now returning data (THANKS!!)

 

Is the final (hour) thing automatically pulling from the DateEntered field? I'm confused on this.

[Grant Holmes]

DUH!!  I just re-read that code and now see that "DateEntered" is in the final part.

 

However, I'm still returning too many records. I need to check the "Active" field for a '1', the "Event" field for "request" and have the returns be less than 48 hours old. My code is still:

$query="SELECT * from birthdays where Active='1' AND Event='request' AND hour(DateEntered)< 48";

 

Instead of returning ONLY those less than 48 hours old (current 5 records), I'm getting ALL records that are active (currently 11)

[redarrow]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

interval

 

 

mysql> SELECT ADDDATE('1998-01-02', INTERVAL -1 DAY);

[Grant Holmes]

Redarrow, does your response mean that my approach will NOT (cannot) work?

-Does that mean I have to use days as opposed to hours?

 

Also, I need to always be pulling the last 48 hours so cannot have a hard coded date in m string.

[Grant Holmes]

My current code returning bad results:

$query="SELECT * from birthdays where Active='1' AND Event='request' AND hour(DateEntered)< 48";

 

I found this on a google search:  where time_stamp >= sysdate()-interval 96 hours;

 

however, I've made several attempts in using this in my query, but NO results:

$query="SELECT * from birthdays where Active='1' AND Event='request' AND time_stamp >= sysdate()-interval 48 hours";

[Grant Holmes]

$query="SELECT * from birthdays where Active='1' AND Event='request' AND DateEntered > (NOW()-86400*2)";

 

Was the answer. Got it fixed. Thanks all