Jump to content
TapeGun007

Finding Past Due Dates

Recommended Posts

Not all items in my database have a due date.  I already have a page that lists everything that is due today and that's fine.

What I want to do is sort out all of the null dates "0000-00-00" but list the overdue items (past today) and not the current due dates (today's date).

This was my failed attempt:

$stmt = $pdo->prepare('SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate IS NOT NULL AND FollowUpDate < ?');
    $stmt->execute([$ID,$today]);

The above will list all the dates that are null anyway.  I can't seem to figure out how to write this correctly.

Thanks.

Share this post


Link to post
Share on other sites

Ah nvm, I got it unless there is a better way to write this, I would be interested.

 

SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate < ? AND FollowUpDate > '0000-00-00'

 

Share this post


Link to post
Share on other sites

If you used actual NULLs and not 0000-00-00 dates then you wouldn't have to care about them. It's also better from a purist standpoint as a lack of data is what NULL is supposed to mean.

SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate < ?

Because comparing NULL with anything fails so those rows won't be matched.

Share this post


Link to post
Share on other sites

Ah, I probably got some bad information in my reading, but I was under the perception that when the date is set to 0000-00-00 it is considered a NULL.  This, of course, did not make sense to me.  In my testing just now, I see the NULL value when auto inserted by mySQL. 

Thanks.

Share this post


Link to post
Share on other sites

New question related to this.  So... when a new prospect is created, the database inserts an automatic value of null.  But then I set a follow up date and then when that date passes, I need to reset that value to a null.  

I simply put if $followupdate !isset then

$followupdate = null;

But when $followupdate is written to the database, it is now "0000-00-00" and I cannot seem to get the value back to null in mySQL.  Everything works fine the way I have it, but call me OCD.

Share this post


Link to post
Share on other sites

UPDATE mytable SET thedate = NULL WHERE thedate = '0000-00-00';

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.