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
– Suyash, Commented Mar 21, 2013 at 13:28

Community · Accepted Answer · 2017-05-23 12:28:52Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 21, 2013 at 13:28

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Woo Over a year ago

what is the last query you have executed?

Spahar Over a year ago

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 Over a year ago

gives this error: Unknown column '2013-03-20 14:15:05' in 'where clause'

John Woo Over a year ago

because you haven't wrap the value (not column name) with single quotes. SELECT ... FROM .... WHERE ... AND DATE = '2013-03-20 14:15:05'

Spahar Over a year ago

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.

Benz · Accepted Answer · 2013-03-22 10:49:12Z

0

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 ;

answered Mar 22, 2013 at 10:49

Benz

2,33513 silver badges19 bronze badges

Comments

Luv · Accepted Answer · 2013-03-21 13:36:01Z

-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, 2013 at 13:36

Luv

512 bronze badges

1 Comment

Spahar Over a year ago

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?

Collectives™ on Stack Overflow

Date filtering doesn't work in mysql, php

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related