Can we use PHP function strtotime in Mysql Query

Question

I have the following MySQL syntax which gives me an error.

I am aware that you cannot compare date variables directly, so I use strtotime to create a Unix timestamp to compare dates.

Can you use the PHP strtotime function within a MySQL query?

$result = select * from table where strtotime(Date) >= strtotime($_POST[DateFrom1])  
&& strtotime(Date) <= strtotime($_POST[DateTo1])";

Solution 1

Your code is NOT a valid PHP syntax. Code below is a clean and proper way of doing this:

$from = date('Y-m-d', strtotime($_POST["DateFrom1"]));
$to = date('Y-m-d', strtotime($_POST["DateTo1"]));
$query = "select * from table where date between '$from' and '$to'";
// use this query to your mysqli

Solution 2

Your code must be this:

$result = "select * from table 
              where unix_timestamp(Date) >= unix_timestamp(".$_POST[DateFrom1].")  
&& unix_timestamp(Date) <= unix_timestamp(".$_POST[DateTo1].")";

To convert date in timestamp in mysql there is function unix_timestamp.

Solution 3

No, you need to use the mysql function UNIX_TIMESTAMP as described here

So maybe something along the lines of

$sql = "SELECT * FROM table WHERE UNIX_TIMESTAMP(date) >= '" . strtotime($_POST[DateFrom1]) . "' && UNIX_TIMESTAMP(date) <= '" . strtotime($_POST[DateTo1]) . "';";

Better yet would be to use Named Placeholders and PDO.

$db = new PDO('mysql:host=whateverHost;dbname=yourDB;charset=utf8', 'username', 'password');
$stmt = $db->prepare("SELECT * FROM table WHERE UNIX_TIMESTAMP(date) >= :dateFrom1 && UNIX_TIMESTAMP(date) <= :dateTo1");
$stmt->execute(array(':dateFrom1' => $_POST[DateFrom1], ':dateTo1' => $_POST[DateTo1]));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Using PDO means you don't have to worry about things such as SQL injection, and it allows for FAR cleaner SQL statements, you don't have to be throwing values from PHP directly into the SQL.

Solution 4

Assuming your date field is of the type date or datetime, then you can do direct comparison without converting to a timestamp.

Also, for your specific case, it looks like you could utilize the expr BETWEEN min AND max operation, which is equivalent to min <= expr AND expr <= max.

$result = "
    SELECT * FROM table
    WHERE date BETWEEN '" . date('Y-m-d', strtotime($_POST['DateFrom1'])) . "'
        AND '" . date('Y-m-d', strtotime($_POST['DateTo1'])) . "'";

SQLFiddle using type date

SQLFiddle using type datetime

---

Note: if you are storing your dates as a varchar datatype or something other than a date or datetime (or maybe timestamp), then you should really consider changing this, in order to take full advantage of MySQL's capabilities.

View more solutions

Comments

[Maniruzzaman Akash]

+ for unix_timestamp()