0

In my database I have a column called featured it is of type datetime and the default value is current-timestamp

Now i want to find all rows where the featured date is greater than now, should be simple enough using this

$sql = "SELECT id FROM `directory` WHERE category LIKE '%$cat%' AND LOWER(`town`)=LOWER('$city') AND `featured` > NOW() ORDER BY dateadded DESC";

But if I make a new entry in the db, those new entries are returned by above sql even though those would have featured date a few seconds before "now".

So I tried

$sql = "SELECT id FROM `directory` WHERE category LIKE '%$cat%' AND LOWER(`town`)=LOWER('$city') AND `featured` > NOW() + INTERVAL 6 HOUR ORDER BY dateadded DESC";

This worked, but now it doesnt work, i increased it to 48 hours and now it works again

but really

`featured` > NOW()

Should be enough because when i use this sql all values in the db would be in the past even if just by a few seconds, so why is this

user2899094
  • 491
  • 1
  • 4
  • 17
  • 2
    (Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – sticky bit Apr 18 '21 at 02:03
  • Can we see the result of SHOW CREATE TABLE [my table]? – Strawberry Apr 18 '21 at 06:14
  • ENGINE=MyISAM AUTO_INCREMENT=1703 DEFAULT CHARSET=latin1 – user2899094 Apr 18 '21 at 12:03
  • `featured` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'return 1 if > today', – user2899094 Apr 18 '21 at 12:04
  • 1
    Have you tried to execute `SELECT NOW() FROM dual;` to check if the server has the correct actual datetime? – Ma3x Apr 19 '21 at 12:59
  • ok tried SELECT NOW() FROM dual; on phpmyadmin it showed 2021-04-19 10:44:44 but on my pc in uk it shows 2021-04-19 18:44:44 – user2899094 Apr 19 '21 at 17:45

0 Answers0