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
Running MySQL queries between two dates problems
Moderator: General Moderators
what type are your dates in the database?
based on your code, you can easily create a valid unix time
next, if the datetype is an integer (unixtime)
or if the datatype is a mysql native datetime
based on your code, you can easily create a valid unix time
Code: Select all
$unixtime = strtotime("$year46-$month46-$day46");Code: Select all
SELECT * FROM foo WHERE date = $unixtimeCode: Select all
SELECT * FROM foo WHERE date = FROM_UNIX($unixtime)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
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
mysql has a function to_unix or something like that (look in the manual at the date and time functions)..
with that you could
and then do whatever you want.....
so many options (my 4 favorites)
mysql: date_format, from_unixtime, unix_timestamp
php: strtotime
with that you could
Code: Select all
SELECT UNIX_TIMESTAMP(datetime) as unixtime FROM fooso many options (my 4 favorites)
mysql: date_format, from_unixtime, unix_timestamp
php: strtotime