[SOLVED] mySQL query between dates

[coupe-r]

Hey guys.

 

I am trying to allow users to search between certain dates.  I have a from date, which has 3 drop down boxes (Month, Date, Year) and a to date, which has 3 drop down boxes.

 

In my test DB, I have a t_date which captures a date such as 6/8/2009.  I also have a t_mon, t_day, t_year just in case I needed to separate for the search.

 

Currently, I have this:

 

$result = mysql_query

("SELECT * FROM tickets WHERE t_company = '".$_SESSION['company']."' AND t_month BETWEEN '".$from_mon."' AND '".$to_mon."' AND t_day BETWEEN '".$from_day."' AND '".$to_day."' AND t_year BETWEEN '".$from_year."' AND '".$to_year."'");

 

or

 

$result = mysql_query("SELECT * FROM tickets WHERE t_company = '".$_SESSION['company']."' AND t_date BETWEEN '".$from_date."' AND '".$to_date."'");

 

Neither is working properly.  I've stared for an hour and know its wrong, but can think of a way to make it work perfectly.

 

Thanks in advance.

[RussellReal]

mysql does not use / in their dates they use YEAR-MONTH-DAY format for their dates, make sure your dates match that format and then you can simply do

 

"SELECT * FROM tickets WHERE t_company = '{$_SESSION['company']} AND UNIX_TIMESTAMP(t_date) BETWEEN UNIX_TIMESTAMP('{$from_date}') AND UNIX_TIMESTAMP('{$to_date}')"

 

or beforehand you could just make the timestamps prior to the query, but either way the above should work if they're in YEAR-MONTH-DAY format

[coupe-r]

Thanks for the reply.

 

Do I need to convert the drop downs from a string to somthing else?  because of the following

 

$from_mon = $_POST['from_mon_box'];

$from_day = $_POST['from_day_box'];

$from_year = $_POST['from_year_box'];

 

$to_mon = $_POST['to_mon_box'];

$to_day = $_POST['to_day_box'];

$to_year = $_POST['to_year_box'];

 

$to_date = $to_mon . '-' . $to_day . '-' . $to_year;

$from_date = $from_mon . '-' . $from_day . '-' . $from_year;

 

I am not getting any results so far

 

[haku]

$to_date = $to_mon . '-' . $to_day . '-' . $to_year;
$from_date = $from_mon . '-' . $from_day . '-' . $from_year;

 

Re-read what Russel said:

 

 

YEAR-MONTH-DAY

[coupe-r]

I typed it in wrong.  I have

 

$to_date = $to_year . '-' . $to_mon . '-' . $to_day;

$from_date = $from_year . '-' . $from_mon . '-' . $from_day;

 

and it still does not work.  Sorry

[haku]

Can't really help you if you don't show your new query.

[coupe-r]

$result = mysql_query("SELECT * FROM tickets WHERE t_company = '".$_SESSION['company']."' AND UNIX_TIMESTAMP(t_date) BETWEEN UNIX_TIMESTAMP('".$from_date."') AND UNIX_TIMESTAMP('".$to_date."'");

[coupe-r]

t_date is DATETIME in the Database.  1 Record which is 2009-06-08 00:00:00

 

If that helps anymore

[haku]

Why are you using unix timestamps, when you have converted your date to sql dates?

[coupe-r]

I was just using the query that was posted.

[coupe-r]

I took out the unix stuff and it seems to be working.  Thanks

[RussellReal]

I advised him to convert it to timestamps coz then I figured it would do BETWEEN with numbers rather than dates, but I guess BETWEEN works with dates aswell learned something new here aswell