Page 1 of 1

Running MySQL queries between two dates problems

Posted: Mon Nov 22, 2004 3:38 pm
by csaha
Hi,

I am trying to run a search on a MySQL database using a webpage that runs PHP. The way I have my search setup is; I ask the user to enter a starting date and an ending date and then run a query between the two dates. I compare the two dates just to make sure that the search does not error out and so that the ending date is always after the starting date. Pretty simple and straightforward huh..........................

Here is where the problem comes in. For some reason, the dates are getting mixed up and the query is running with the starting date as the later date and so no result is being displayed. Here is the code that I am using:

$year45 = $_POST['year45'];
$year46 = $_POST['year46'];
$month45 = $_POST['month45'];
$month46 = $_POST['month46'];
$day45 = $_POST['day45'];
$day46 = $_POST['day46'];

$date45 = $year45.'-'.$month45.'-'.$day45;
$date46 = $year46.'-'.$month46.'-'.$day46;
if ($date45 > $date46)
{
$datex4 = $date45;
$datey4 = $date46;
}
else {
$datex4 = $date46;
$datey4 = $date45;
}

$query = "SELECT country FROM personal WHERE date between ('$datey4') AND ('$datex4') ";

The year, month and date are obtained from a different form. Can any one help me out with this. I want to make sure that after comparing the two dates the later date is taken as $datex4.

An example:

For 2004-1-1 and 2004-11-30; $datex4 = 2004-11-30 and $datey4=2004-1-1
But for 2004-7-7 and 2004-11-30; $datex4 = 2004-7-7 and $datey4=2004-11-30.

And so the query does not give any results as you would expect................

thanks

csaha

Posted: Mon Nov 22, 2004 5:37 pm
by timvw
what type are your dates in the database?

based on your code, you can easily create a valid unix time

Code: Select all

$unixtime = strtotime("$year46-$month46-$day46");
next, if the datetype is an integer (unixtime)

Code: Select all

SELECT * FROM foo WHERE date = $unixtime
or if the datatype is a mysql native datetime

Code: Select all

SELECT * FROM foo WHERE date = FROM_UNIX($unixtime)

Posted: Mon Nov 22, 2004 10:54 pm
by csaha
Thanks Tim for your prompt reply. I did some troubleshooting myself but havent tested it out completely. What I am trying is using the mktime() function to convert the responses to an actual date and then compare the two dates created using the mktime() function.

So far it seems to be working fine for the combination of dates that I have tried. I will try your suggestion as well to see which one works better.

My database has dates stored as yyyy-mm-dd format

thanks again

Posted: Tue Nov 23, 2004 3:32 am
by timvw
mysql has a function to_unix or something like that (look in the manual at the date and time functions)..

with that you could

Code: Select all

SELECT UNIX_TIMESTAMP(datetime) as unixtime FROM foo
and then do whatever you want.....


so many options (my 4 favorites)
mysql: date_format, from_unixtime, unix_timestamp
php: strtotime