CODEWITHSUNDEEP
phpmysqltimeunix-timestamp
DobotJr
DobotJr

Reputation: 4049

Converting mysql unix time and using BETWEEN

I have a form where the user can choose 2 dates, hit submit, and the form returns tickets that were created between those 2 dates.

The dates from the form are in this format: mm/dd/year

The dates in mysql are being saved as unix timestamps.

After the submit, I'm converting the date format like this:

  $from = $_POST['from']
  $to = $_POST['to'];
  $from = date("Y/m/d",strtotime($_GET['from']));
  $to = date("Y/m/d",strtotime($_GET['to']));

So that $from is something like year/mm/dd

My query is:

$get = mysql_query("SELECT id FROM tickets WHERE date_created BETWEEN UNIX_TIMESTAMP('$from') AND UNIX_TIMESTAMP('$to')");

This works fine, my problem is, when the from and to dates are equal, meaning the user wants to see tickets for one day only. Is it still possbile to use the same query somehow? Or how do I modify the query?

Upvotes: 1

Views: 384

Answers (2)

kpaulsen
kpaulsen

Reputation: 1

Change your $to and $from to use the first second of the day and the last second of the day:

$from = date("Y-m-d 0:0:0", strtotime($_GET['from']));
$to = date("Y-m-d 23:59:59", strtotitme($_GET['to']));

Upvotes: 0

Kermit
Kermit

Reputation: 34055

Why not do...

$from = date("Y-m-d 00:00:00",strtotime($_GET['from']));
$to = date("Y-m-d 23:59:59",strtotime($_GET['to']));

Also, be careful as you're mixing $_GET and $_POST. MySQL has built in date/time functions that can handle this type of conversion. It would be better if you used binded parameters.

Upvotes: 2

Related Questions