Dealing with Dates

[jmfillman]

I'm passing two date strings to PHP in a format like this: "2008-02-29" and I need to query MySQL to find a date range, like the select statement below, but this doesn't return anything. The MySQL field is a DATE field, format "2008-02-29". What do I need to do to the string to make it a recognizable date?

 

public function getAppointments($myDate1, $myDate2) {

$date1 = date($myDate1);

$date2 = date($myDate2);

$query  = "SELECT color, day, dayOfWeek, endTime, height FROM apps WHERE date>='$date1' AND date<='$date2' AND status=0";

$result = mysql_query($query);

$apps_array = array();

while($row = mysql_fetch_assoc($result)) {

$apps_array [] = $row;

}

return $apps_array;

  }

[phpSensei]

you can use a BETWEEN command i think

 

SELECT * FROM table WHERE `date` BETWEEN $date1 AND $date2

 

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

 

mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0

[PFMaBiSmAd]

The php date() function you are using expects a format string and optionally a Unix timestamp. Probably reading the php manual for that function would help.

 

If you are passing it a date in the format shown, it is likely generating an error (when learning php, developing php code, or debugging php code always turn on full php error reporting to get php to help you) or producing a Jan 01, 1970 date.

 

Echo out your query string variable $query to see what it actually contains.

[phpSensei]

Sorry, i misunderstood your question, like the dude above said, date function provides an error for you.

[jmfillman]

I read the manual on date a few times before I posted, but I don't follow the usage. "string date ( string $format [, int $timestamp ] )" means little to me. Is there a better example of usage somewhere? Can I use it like this: date ("Y-m-d", $myDate1)? What format does $myDate1 need to be in for this to work?

 

I'm not viewing the results in an html page, but passing the array back to Flex, so I don't see the PHP raw output. I'm also doing this on a hosted PHP account and don't have the ability to turn on debug or error reporting. I don't have access to error logs either.

 

Are you saying that if I pass PHP a string in the format March 02, 2008, that PHP will recognize it? I have pretty much full control of the format that I pass to PHP.

 

[phpSensei]

$myDate1 -- Has to be a TIMESTAMP

[jmfillman]

Okay, I solved this by passing the number of seconds instead of a string, e.g.:

 

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

 

$myDate1 is 1203840000