Jump to content


Photo

PHP/MySQL DATE Query String Issue


  • Please log in to reply
2 replies to this topic

#1 northstardomus

northstardomus
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 08 September 2006 - 04:05 PM

I looked for the newbie board, couldn't find it because I'm new.  Sorry!

Anyway,

I'm trying to query a MySQL table that has a field in date format where the dates are formatted like 2006-09-07.

I want to store the interesting dates in a PHP variable that could come from a URL, i.e. http://www.mydomain....date=2006-09-07
Or the date could come on a query of my date field.  Regardless, it will eventually be assigned to a php variable.

Anyway, what's seems to be happening is MySQL is assuming I'm doing some arithmatic and is processing the 2006-09-07 to be the value 2006 minus 9 minus 7 = 1990.

---------------------------------------------------------------------------------------------------
So, if I hard code the interesting date, I get what I want:
<?php

$sql="SELECT * FROM table where date <= '2006-09-07'";

?>

This will get me everything in my table that has a date less than or equal to '2006-09-07', exactly what I want.
------------------------------------------------------------------------------------------------------
But if I use the same query using a php variable like below:

<?php

$date = '2006-09-07';

$sql="SELECT * FROM table where date <= $date";

?>

This will get me everything in my table that has a date less than or equal to '1990' or
2006-9-7 or the equivalent of these queries:

<?php

$sql="SELECT * FROM table where date <= 2006-09-07";

?>

<?php

$sql="SELECT * FROM table where date <= 1990";

?>
------------------------------------------------------------------------------------------
So, it seems to me that MySQL is not seeing my PHP variable as a string, I'm not sure.
I tried a few things like CONCAT() and CAST() but they didn't seem to work.

Can anybody tell me where I'm going wrong?

Thanks,

NSD

#2 Jenk

Jenk
  • Members
  • PipPipPip
  • Advanced Member
  • 778 posts

Posted 08 September 2006 - 04:31 PM

need to surround the value with single quotes like so:
$sql="SELECT * FROM table where date <= '$date'";

otherwise it thinks you are looking for 1990

(2006 - 7 - 9 = 1990)

:)

#3 northstardomus

northstardomus
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 08 September 2006 - 04:41 PM

OK, that works, thanks!!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users