0

I am trying to get the data out of my database by searching between two timestamps. Below is my query 

$result = mysql_query("SELECT * FROM table WHERE time BETWEEN '1362355200' AND '1362959999'")

When the query runs I do not get any errors or results.

John Conde
  • 217,595
  • 99
  • 455
  • 496
user1941709
  • 81
  • 6
  • 1
    [Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. – TNK Mar 14 '13 at 15:55
  • Please don't use `BETWEEN` for date/time/timestamp values. Really, it's thinking about things the wrong way. [This article](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) details some SQL Server-specific problems, but the concepts still apply. – Clockwork-Muse Mar 14 '13 at 16:00
  • Thanks for the advice and links, appreciated – user1941709 Mar 14 '13 at 16:31

2 Answers2

2

time is a reserved keyword. Try putting it in ticks:

SELECT * 
FROM table 
WHERE `time` BETWEEN '1362355200' AND '1362959999'
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • 1
    also to remember is BETWEEN does not INCLUDE the inputted time stamps so in your case it would be 1362355201 1362959998 if you want to include your actual time stamps then you'll want to do where `time`>= AND `time`<=. BETWEEN is the same as < > not <= >= – Dave Mar 14 '13 at 15:27
  • 1
    It does if you dont put it between quotes. The comparisment used in between is `>=` and `<=`. See http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html – Hugo Delsing Mar 14 '13 at 15:38
  • I tried this $result = mysql_query("SELECT * FROM table WHERE where time>='1362355200' AND time<='1362959999'") but i get the following error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where 'time'>='1362355200' AND 'time'<='1362959999'' at line 1 – user1941709 Mar 14 '13 at 15:40
  • You have a double WHERE – Hugo Delsing Mar 14 '13 at 15:42
0

I think the problem is with the qoutes around the values. If you want to compare numeric values, dont use them. Check the manual on BETWEEN for more information

SELECT * 
FROM table 
WHERE `time` BETWEEN 1362355200 AND 1362959999
Hugo Delsing
  • 13,803
  • 5
  • 45
  • 72
  • Thanks when i run the above in PhpMyAdmin it comes back error, #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table WHERE `time` BETWEEN 1362355200 AND 1362959999' at line 2 – user1941709 Mar 14 '13 at 16:05