check for nulls in mysql_fetch_array

[clinto]

Hi All

 

I am trying to retreive data from the mysql db and then show the data in a html table structure. There are nulls in some db fields so the html cell doesnt draw a border around the data with nulls.

 

I am trying to check for nulls in the code and if a row field value is NULL then give it a value of "none". The if statement is not working the way i had hoped. Heres my code:

 

<?php

 

while ($row=mysql_fetch_array($mysql_result))

{

$title=$row["Title"];

 

if (is_null($row["StatusComment"])) {

$statuscomment="none" ;

}else {

$statuscomment=$row["StatusComment"];

}

 

$datelisted=$row["DateListed"];

 

 

echo "<TR><TD>$title</TD<TD>$statuscomment</TD><TD>$datelisted</TD</TR>";

}

}

mysql_close($connection);

?>

 

Thanks in advance.

[bache]

In your sql statement you can use IFNULL function like this:

 

SELECT IFNULL( StatusComment, 'NONE' ) FROM `mytable`

 

so in the result set if the StatusComment is null somewhere it will be replaced with 'NONE' and you don't have to check anything in the PHP code

[clinto]

hey thanks Bache

 

Thats a lot easier but I just checked in the table and it appears that the fields arent NULL. they are EMPTY but not Null.

 

Would you happen to know how to check for empty field.

 

Thanks Again

[bache]

NULL doesn't mean 0 or empty string or some other character, it only indicates that there isn't a value in the field

[clinto]

When I run the query in PhpMyAdmin the recordset still contains some records with data and some records that are empty. So I'm guessing that the empty records aren't NULL else the select statement would have changed them to 'NONE'

 

 

[bache]

ok, may be there are empty strings so you can try with the CASE statement:

 

SELECT CASE trim(StatusComment)

WHEN ''

THEN 'NONE'

ELSE StatusComment

END

FROM myTable

 

this should do it