MSAccess, php and empty date/time fields

October 11, 2010 9:00 AM

How do I check (using php) the existence of data in a date/time field in an access database?

I have to build some php pages around an access database (dsnless). No lectures please: I know how bad an idea this is; I don't have a choice!

I'm getting values out of a date/time field. The field isn't required. When values are there, it works fine. When they aren't, the whole thing just falls over. I can't find any way of testing whether it's there beforehand: is_null() comes up false whatever (empty() comes up true, even when there is data), any attempt to convert it to another type or look at its properties breaks it as well.

I can't find a simple way to test whether there's data there or not. Any ideas?

posted by monkey closet to Computers & Internet (8 answers total)

Can you give an example of some "correct" data from that field?
posted by The Winsome Parker Lewis at 9:13 AM on October 11, 2010

use strlen() in php to check whether the string you're getting back from the field is blank or not?
posted by RustyBrooks at 9:17 AM on October 11, 2010

Have you tried isset()? Barring that, maybe run some additional queries with "where [datetimefield] is not null" and count the rows returned to see if it's the expected number. Use that as your conditional instead of checking the variable itself (this will slow down performance, so don't use it in a high-traffic production setting).
posted by The Winsome Parker Lewis at 9:28 AM on October 11, 2010

What do you mean "the whole thing just falls over"?

Can you try printing out the contents of that field, enclosed by pipes or something? I have run into cases where fields that I thought must be either null or an empty string held a line return or some other invisible character.
posted by bricoleur at 10:01 AM on October 11, 2010

If would help if you posted a snippet of the lines involving it, from getting it from the query to where you check whether it's a valid date and/or time. It could be something as simple as not using empty($var) correctly, i.e. making assumptions about the input and returns of the functions you're using, or just a logic fark-up that you're not seeing, kind of like proofreading your own documents doesn't usually catch a bunch of things.
posted by hungrysquirrels at 11:50 AM on October 11, 2010

Best answer: It looks like Access is not handling NULL values in the conventional manner. I've only used Access when I absolutely had to, and always via the ODBC library with a DSN, so I don't have specific experience with your situation. But I think this will work, as I've used it successfully in the past in similar circumstances.

if ($var == '') {
   #stuff
}

You might also see if your php.ini error reporting settings are turned up too far, and whether you can turn them down in your script.
posted by richyoung at 12:01 PM on October 11, 2010

Response by poster: richyoung's answer doesn't work here, but sorting out the error reporting in php.ini does at least give me something more meaningful:

Catchable fatal error: Object of class variant could not be converted to string in C:\inetpub\wwwroot\ [...] - as I said, it works fine with the non-empty values.

FWIW, strlen() produces the correct result for fields with data, same error for those without, isset() is true for all.
posted by monkey closet at 1:43 AM on October 12, 2010

Response by poster: Thanks, everyone! Getting a meaningful error message gave me something new to search on, and I got the solution here. I feel a little stupid now...
posted by monkey closet at 1:51 AM on October 12, 2010