Date filtering doesn't work in mysql, php

Question

I have a site where I want to show articles that have been posted the last 24 hours. I thought it would be easy but I cannot understand what I am doing wrong.

Lets start from the begining, in mysql I have this table:

id | editor | article | date
-----------------------------
128|   8    |  ....   | 2013-03-20 01:01:26
129|   8    |  ....   | 2013-03-20 01:03:24

date field is TIMESTAMP.

Now in php I have this code:

$date = date('Y-m-d H:i:s', strtotime('-1 day')); //24 hours ago
$articles = mysql_query("SELECT * FROM articles WHERE editor=8 AND 'date' > '".$date."' ORDER BY id DESC");

If I run this code now that my local timestamp is "2013-03-21 15:20:07" (more than 24 hours than the articles timestamp) I still get the articles, although I shouldn’t because 2013-03-20 01:01:26 (article 128 timestamp) < 2013-03-20 15:20:07 (now - 1 day). I also run the code through phpmyadmin and the results where the same.

I cannot see what I am doing wrong, I tried experimenting with other tables of my database that have date fields and I still couldn't get it to work.

Thanks a lot.

i store time as seconds from time() gives me more freedom and flexibility to make queries. — Suyash, Mar 21 '13 at 13:28

score 4 · Accepted Answer · edited May 23 '17 at 12:28

4

the reason why it is not working is because you are wrapping the column name with single quote. The effect of it is it changes the column name into string, thus you are comparing your date into string literal date. Remove the single quotes around it and it will work.

$articles = mysql_query("SELECT * FROM articles WHERE editor=8 AND date > '".$date."' ORDER BY id DESC");

Remember, single quotes as well as double quotes are for string literals while backticks are for identifiers.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:28

Community

1
1

answered Mar 21 '13 at 13:28

John Woo

258,903
69
498
492

what is the last query you have executed? – John Woo Mar 21 '13 at 14:08
Removing the single quotes unfortunately didn't work. Also adding backtick instead of single quotes didn't work. The strange thing is that running the query from phpmyadmin still doesn't work. For example, this query: SELECT * FROM `coupons` WHERE `tipster_id` = 8 AND `date` > 2013-03-20 14:15:05 gives this 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 '14:15:05 LIMIT 0, 30' at line 1". And this query: SELECT * FROM `coupons` WHERE `tipster_id` = 8 AND `date` > `2013-03-20 14:15:05` – Spahar Mar 21 '13 at 14:10
gives this error: Unknown column '2013-03-20 14:15:05' in 'where clause' – Spahar Mar 21 '13 at 14:11
because you haven't wrap the value (*not column name*) with single quotes. `SELECT ... FROM .... WHERE ... AND DATE = '2013-03-20 14:15:05'` – John Woo Mar 21 '13 at 14:13
My bad. I misunderstood your first post, I thought you were referring to the date value. Now it works. Thanks a lot!!! Also thanks for the extra advice, the values don't come from the outside, but I'll take it into consideration. – Spahar Mar 21 '13 at 14:21

score 0 · Answer 2 · answered Mar 22 '13 at 10:49

I know the question is answered and solved, but I just want to say the next thing:

Why don't you let mysql do the math, it's shorter and easy to read :) ?

SELECT * FROM articles WHERE editor=8 AND 'date' > DATE_ADD(NOW(), INTERVAL -1 DAY) ORDER BY id DESC ;

score -1 · Answer 3 · answered Mar 21 '13 at 13:36

-1

The best solution is convert the time store in the database into seconds and then used your query

example: date('H:i:s');

convert into seconds => variable=(3600* date('H') + 60*date('i') + date('s'));

now do any thing with seconds

answered Mar 21 '13 at 13:36

Luv

51
2

Sorry but i don't really get it. You advice me to convert the date field of the database rows into seconds, and then find which rows (articles) are in the time I want? If I understand what you are saying, I need first to get from the database for example the last 20 articles and then find which of those have been created the last 24 hours. Correct? – Spahar Mar 21 '13 at 14:15

Date filtering doesn't work in mysql, php

3 Answers3