MySQL Select Statement Not Returning List in PHP

Question

I am trying to return a list of email addresses that expire 30 days from now. To do that I need to look for users from 335 days ago since all accounts expire 365 days after setup and I only log the setup date in MySQL as a timestamp. Here is the PHP statement I am using which is not returning a list for me. Any ideas:

$sql2 = "SELECT * FROM `users` WHERE `TYPE` > 0 AND `ID` > 0 AND `UPDATED_DATE` = DATE_SUB(NOW(),INTERVAL 335 DAY)";
$result = $conn->query($sql2);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "email: " . $row["EMAIL"]. $row["ID"]."<br>";
    }
} else {
    echo "0 results";
}

did you mean UPDATED_DATE >= DATE_SUB(NOW(),INTERVAL 335 DAY)? — Fabricator
– Fabricator, Commented Jan 20, 2015 at 18:16
first test your mqsql query without php and see result if there are result so the isseu with php code if there are not then the error with mysql query — Robert
– Robert, Commented Jan 20, 2015 at 18:18
It should be <= since you want accounts older than that date. — Barmar
– Barmar, Commented Jan 20, 2015 at 18:18
What is the datatype of updated_date? is it a DATE or DATETIME? — Barmar
– Barmar, Commented Jan 20, 2015 at 18:19
and please when you gonna ask question about mysql write the table struct the most simple way to run the code show create table table_name the result gonna be like CREATE TABLE `test` ( `id` int(30) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `id_2` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 copy it to the question` — Robert
– Robert, Commented Jan 20, 2015 at 18:21

Barmar · Accepted Answer · 2015-01-20 18:21:21Z

1

You need to ignore the time part of the timestamp, so it should be:

WHERE DATE(updated_date) = DATE(DATE_SUB(NOW(), INTERVAL 335 DAY))

Otherwise, you're only matching accounts that were created at the same exact time of day as you run the query.

answered Jan 20, 2015 at 18:21

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

user525429 Over a year ago

Thanks. I was not ignoring the time part of the timestamp and DATE( ) took care of it.

Collectives™ on Stack Overflow

MySQL Select Statement Not Returning List in PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related