Running MySQL queries between two dates problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
csaha
Forum Newbie
Posts: 2
Joined: Mon Nov 22, 2004 3:23 pm

Running MySQL queries between two dates problems

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
csaha
Forum Newbie
Posts: 2
Joined: Mon Nov 22, 2004 3:23 pm

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply