Help with a Query statement of records with NULLs

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Help with a Query statement of records with NULLs

Post by Swede78 »

I'm having a problem writing a query that formats a date. The problem is that not all my records include a date. Sometimes the date field will be NULL. So, when it tries to select the data from a record with a NULL date, the query results in 0 records. How can I write this query, so that it will give me a result even if the date field is NULL?

Here's my current query code:

Code: Select all

<?php
$query = "SELECT *, DATE_FORMAT(DateField, '%M %D, %Y') as DateField FROM table";
?>
Note: my date field is a MySQL date field (YYYY-MM-DD). Using PHP 4.3 and MySQL 3.23 on a Win2K/IIS machine.

I have a feeling that someone is going to say, "use a timestamp for your database instead of a date field." Well, that would probably work good, but I already have hundreds of records that have the date field format. Plus, I'd like to figure this out, to see if it can be done.

Any help would be appreciated!!!
rterrar
Forum Newbie
Posts: 8
Joined: Wed Aug 06, 2003 12:32 am

MySQL and NULL Values

Post by rterrar »

First I would like to point out that in MySQL NULL is not the same as an empty string ("") and anytime you use NULL as the right had operator (ex: datefield = NULL) it will return an empty set because NULL always returns false. So in your case I would use something like this:
...WHERE (datefield IS NULL) OR (datefield IS NOT NULL)

You can also look up these two functions in the MySQL docs and see if they would better serve your purpose COALESCE and IFNULL.
I'm thinking that COALESCE would serve your purpose, It lets you set NULL values to anything you want in the return, (not the table) and then you could test for that value and you would know that it is NULL in the actual table. I've used this to treat the NULL as 0. COALESCE(datefield,0)

With out actually testing this:))) I would maybe do something like:
SELECT * , DATE_FORMAT(COALESCE(datefield,0),'%M %D, %Y') as datefield FROM table
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Thanks rterrar,

I'll give your code a try and I'll look into this COALESCE and IFNULL, at mysql.com. I appreciate the reply!
Post Reply