[SOLVED] Dealing with a NULL value in a SELECT statement...

[mrherman]

I just cannot do this...I've tried so many things that my fingers hurt from typing so many attempts...

 

I have a MYSQL table like so:

 

row    value

1        blue

2        red

3        NULL

4        purple

5        green

 

This is a simplified version of the code:

 

for ($num_row = 1; $num_row <= 3; $num_row++)
{
         $sql = "SELECT value
                 FROM $table
                 WHERE row = $num_row" ;

         $result = mysql_query($sql) ;
         
         $value = mysql_result($result,0) ;
         
         print $row . " -- " . $value . "<br>" ;
}

 

 

The process throws an error at the NULL value.  I have searched far & wide for the answer, but can't put my hands on it.  I have several books but can't find a reference to this situation.

 

What am I doing wrong?

 

Thanks for your help!!

 

[JasonLewis]

Change this:

$result = mysql_query($sql);

 

To this:

$result = mysql_query($sql) or die("Error in mysql: ".mysql_error());

 

If there is an error it'll tell you what it is.

[PseudoEvolution]

I haven't tested this, but I'm pretty sure there is an answer using "IS NOT NULL" in your MySQL statement.

 

Example:

 

$sql = "SELECT value FROM $table WHERE row = $num_row AND IS NOT NULL" ;

 

I bet it's wrong since I have never had to use it, but at least you get the idea.

 

It should skip that row if the field is NULL.

 

 

[Edit]

Also try this!

$sql = "SELECT value FROM $table WHERE row <=> $num_row" ;

 

The "<=>" is supposedly a "NULL-safe" operator.

[mrherman]

Thanks very much to your help, ProjectFear and PseudoEvolution...Very helpful.

 

As the result of implementing your suggestions, I discovered (read: stumbled upon, tripped over) the looping construct "CONTINUE," which was also very helpful.  I have some additional questions, but those are for another post!

 

Thanks again!!