handling nulls from mysql query results

[rcbasie]

Hi.

I'm having difficulty with nulls appearing in the HTML that one of my scripts produces (in Netscape 7.1 these are represented by a little square). I've tried using the is_null() function to test each value from the database and making it "" instead but this doesn't seem to work. Any ideas?

Thankyou in advance

Robin

[Wave]

please, give more detailed description of your disaster 🙂

[rcbasie]

Disaster !!!!!😃

Ok, here goes...

I get some data from the database, say 3 records each with 10 fields. I'm then transferring each of the field sets into 10 arrays, and passing these to the smarty class complier to be displayed. Some of the fields will contain null values, so as i'm going along transferring from the database array (i'm using mysql_fetch_array in a loop) i'm using the following code to catch the nulls and convert them to the zero-length string:

$r = mysql_query ( $q ) ;

$c = mysql_num_rows( $r ) ;

for ( $i = 0 ; $i < $c ; $i++ )
{
    $l = mysql_fetch_array ( $r ) ;
    if ( is_null ( $l [ "secretary_tel" ] ) ) {
      $nhs_clinic_tel [ $i ] = "--sp--";
    }    
    else {    
        $nhs_clinic_tel [ $i ] = $l [ "secretary_tel" ] ;
    }

I'm expecting this to work (obviously) and it doesn't (doh!).

Ok, i hope that's illuminated things for you!

Cheers
Robin

[OctoberAsh]

Are the results you're getting more than you expect, less, or something else (like errors)? If you're getting less, check your data to make sure a user didn't accidently input a ' ' (blank character or a series of blanks), which would appear 'null-ish' on first glance, but aren't processed as a null by SQL. You can use TRIM to get rid of those if that's the case.

Ex.
<?php $Array["field"]=trim($Array["field"]); ?>

Hope it helps - Good luck!

[[deleted]]

Why not let the query handle it for you:

COALESCE(list)
Returns first non-NULL element in list:

mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL

So you could do:

SELECT
COALESCE(secretary_tel, 'no number') AS secretary_tel
FROM table
WHERE foo=bar;

that way you will get 'no number' in the 'secretary_tel' kolumn whenever 'secretary_tel' is NULL.

Note however that an empty string is not the same as a NULL,:

INSERT INTO table (secretary_tel) VALUES ('');

SELECT * FROM table WHERE secretary_tel IS NULL;

will find nothing, but

SELECT * FROM table WHERE secretary_tel='';

will find it.