I want to UPDATE but only if the field isn't filled in already!

[thecard]

Here's the query I have already:

 

"UPDATE tb_sites SET nav1='$site' WHERE full<'20' AND yhits>'$average' LIMIT 0, '$var'"

 

In my database I have a table called tb_sites. tb_sites has 25 fields in it. 20 of them are:

 

nav1

nav2

nav3

...to...

nav20

 

There is also another field called 'howmany' - this is a field which contains how many of the 'navs' are filled in.

 

 

I want to run the query above but I would like it to do something like this:

 

if (!$nav1) 
{ update it }
else
if (!$nav2) 
{ update it }
else
if (!$nav3) 
{ update it }
...

 

Am I just going to have to do this the slow way like I have done above?

I'm preety confused!

[Xurion]

You should check each one like you are doing, but don't do separate updates. Compile an SQL string:

 

if(empty($nav1)){
    $sql = "UPDATE tb_sites SET nav1='$site'";
}
if(empty($nav2)){
    if(empty($sql)){
        $sql = "UPDATE tb_sites SET nav2='$site'";
    }else{
        $sql .= ", nav2='$site'";
    }
}
if(empty($nav3)){
    if(empty($sql)){
        $sql = "UPDATE tb_sites SET nav3='$site'";
    }else{
        $sql .= ", nav3='$site'";
    }
}
$sql .= " WHERE full<'20' AND yhits>'$average' LIMIT 0, '$var'";

 

This builds the SQL statement based on what you need.