GOsurf GOsurf - 2 months ago 7
MySQL Question

Exclude row from MySQL older than today

There is a MySQL table:

+----+-------+------+------------+---------+
| id | name | type | date | deleted |
+----+-------+------+------------+---------+
| 1 | nameA | 1 | 2016:10:15 | 1 |
+----+-------+------+------------+---------+
| 2 | nameB | 1 | 2016:10:20 | 0 |
+----+-------+------+------------+---------+
| 3 | nameC | 2 | 2016:09:26 | 0 |
+----+-------+------+------------+---------+
| 4 | nameD | 2 | 2016:09:30 | 0 |
+----+-------+------+------------+---------+
| 5 | nameE | 3 | 2016:09:26 | 0 |
+----+-------+------+------------+---------+


I want to
SELECT
everything except where deleted = 1 and except where type = 2 has a date older than today (2016:09:27). So I tried using
NOT IN
, but the way I do it excludes also type=3 which has a date older than today:

$currentDate = date("Y:m:d");
$sql = "SELECT * FROM table WHERE deleted != 1 AND date NOT IN
(SELECT date FROM table WHERE type = 2 AND date < '$currentDate') ORDER BY date";


Any help? Thanks!

Answer

Try it like this:

$sql = "SELECT * FROM table WHERE deleted = 0 AND NOT (type = 2 AND date < DATE(NOW())) ORDER BY date";

I don't think you need to define currentDate in PHP either.