MySQL query between two dates as timestamps

Question

I have been trying to select items from my database that are between two default dates: now and 60 days ago. However all the queries that I try do not work.

Here is what I have tried:

$Now = date("Y-m-d");
$Before = date("Y-m-d", strtotime("-60 days");

// This is try1
$sql = "SELECT * FROM myTable WHERE myTimestamp BETWEEN " . $Before . " AND " . $Now;  

// This is try2
$sql = "SELECT * FROM myTable WHERE myTimestamp >= " . $Before . " AND myTimestamp <= " . $Now; 

I am out of guesses of how to do this. I have looked at the other questions that are the same as this one, but none of the solutions presented work.

Please note: these queries do not give an errors. They just don't retrieve anything. I have also used get_defined_vars() to print the dates onto the page. This is what they show:

[Now] => 2016-01-07
[Before] => 2015-11-08

Answer 1

"Please note: these queries do not give an errors." - This " . $Before . " AND " . $Now; and you're using 2016-01-07 and 2015-11-08 being strings and not integers.

Same for " . $Before . " AND myTimestamp <= " . $Now

Plus, those hyphens are interpreted as MINUS, being a mathematical operation.

I.e.: 2016 minus 01 minus 07 etc. resulting in a syntax error.

Therefore, those should read as:

• WHERE myTimestamp BETWEEN '$Before' AND '$Now' ";

• WHERE myTimestamp >= '$Before' AND myTimestamp <= '$Now' ";

You're not getting any because you're NOT checking for those syntax errors.

Consult http://dev.mysql.com/doc/en/string-literals.html about string literals.

Also consult When to use single quotes, double quotes, and backticks in MySQL

You're also not querying nor fetching anything and we have no idea which MySQL API you're using to connect with, or whether you did successfully connect to your database.

Consult:

• http://php.net/manual/en/mysqlinfo.api.choosing.php
• http://codular.com/php-mysqli (A quick tutorial)

You either use mysqli_fetch_array() or mysqli_fetch_assoc() depending on what you want to do here, and this being a mysqli_ example.

Consult the manuals:

• http://php.net/manual/en/ref.mysql.php
• http://php.net/manual/en/book.mysqli.php
• http://php.net/manual/en/book.pdo.php
• MYSQL - Retrieve Timestamps between dates
• https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

You should take advantage of using MySQL's built-in date/time functions.

and do not mix them together, it doesn't work that way.

Consult: Can I mix MySQL APIs in PHP?

Depending on the MySQL API you are using to connect with, here are a few links for error handling.

• http://php.net/manual/en/function.mysql-error.php (MySQL_)
• http://php.net/manual/en/mysqli.error.php (MySQLi_)
• http://php.net/manual/en/pdo.error-handling.php (PDO)

---

Footnotes:

It seems you are new to MySQL, therefore I suggest you have a look at those links I gave you, finding tutorials and Q&A's here on Stack.

You should look into using a prepared statements also:

• https://en.wikipedia.org/wiki/Prepared_statement

in order to help against an SQL injection