Not update empty variable into mysql...

[fesan]

Hello...

 

I'm inserting some values using php variables...

But when the variables are empty i do not want mysql to update that field...

 

This is how my code looks like now....

 

$sql = "UPDATE ${dbtable} SET 
renset_dato = '$renset_dato',
renset_usr = '$renset_usr',
verksted_dato = '$verksted_dato',
verksted_hvor = '$verksted_hvor',
verksted_hva = '$verksted_hva',
verksted_hvorfor = '$verksted_hvorfor',
skruer_dato = '$skruer_dato',
skruer_usr = '$skruer_usr',
lampe_tid_dato = '$lampe_tid_dato', 
lampe_tid_usr = '$lampe_tid_usr', 
lampe_timer = '$lampe_tid', 
lampe_strokes = '$lampe_strokes', 
intern_service_dato = '$internservice_dato', 
intern_service_usr = '$internservice_usr', 
km_stand_dato = '$km_stand_dato',
km_stand = '$km_stand_usr',
record_endret_timestamp = '$timestamp',
record_endret_usr = '$_SESSION[user]'
WHERE id = $_GET[id]";

$result = mysql_query($sql) or die (mysql_error());

[.josh]

how are those variables being assigned? Are they coming from a form, assigning them from $_POST array?

[fesan]

O, sorry for missing that out... Yes they are coming from an form, to create the variables i have this code:

$renset_dato = !empty($_POST ['renset_dato']) ? $_POST['renset_dato'] : '';

 

This is to make the variables to be not empty, i was told some time ago that a empty fields inserted in an mysql DB would make an error... I haven't really tried without this code.

[fesan]

Is there a way to do this?

 

Maby i should ask in the Mysql Help forum??

 

Thanks

[mattal999]

Well, you would need to check if the variable is empty before adding it to the mysql query string. Like so:

 

<?php

$sql = "UPDATE ${dbtable} SET ";
if(isset($renset_dato) && $renset_dato !== "") { $sql .= "renset_dato = '$renset_dato',"; } // Do this for all of the variables.
renset_usr = '$renset_usr',
verksted_dato = '$verksted_dato',
verksted_hvor = '$verksted_hvor',
verksted_hva = '$verksted_hva',
verksted_hvorfor = '$verksted_hvorfor',
skruer_dato = '$skruer_dato',
skruer_usr = '$skruer_usr',
lampe_tid_dato = '$lampe_tid_dato', 
lampe_tid_usr = '$lampe_tid_usr', 
lampe_timer = '$lampe_tid', 
lampe_strokes = '$lampe_strokes', 
intern_service_dato = '$internservice_dato', 
intern_service_usr = '$internservice_usr', 
km_stand_dato = '$km_stand_dato',
km_stand = '$km_stand_usr',
record_endret_timestamp = '$timestamp',
record_endret_usr = '$_SESSION[user]'
WHERE id = $_GET[id]";

$result = mysql_query($sql) or die (mysql_error());

?>

 

I'm far too lazy to do it all for you

[fesan]

Thanks for the reply...

 

This works excellent, except when inserting in MySQL fields with date format.

Then the date that was stored changed to 1970-01-01.

 

So inserting updating an table without entering anything to date fields seams to be problem.

Maby i could fetch all the stored db info for that row and say:

 

<?php
if(isset($renset_dato) && $renset_dato !== "") { $sql .= "renset_dato = '$row[renset_dato]', "; } 
?>

[fesan]

Now I've have an issue that is bothering me... Can't seam to find out the problem.

 

I have this code:

<?php
$query = "SELECT * FROM $dbtable WHERE id = $_GET[id]";
$result = mysql_query($query) or die(mysql_error());
             while($row = mysql_fetch_array($result)) {
$test = $row['renset_dato'];
echo $test;
}
$sql = "UPDATE ${dbtable} SET ";
if(isset($renset_dato) && $renset_dato !== "") { $sql .= "renset_dato = '$renset_dato', "; } else { $sql .= "renset_dato = '$test', "; }

echo $sql;
?>

 

As you see I've echoed out two variables to see what happens.

$test results in: 2009-07-28

 

And when i leave $renset_dato empty by not filling in a date in the form, the $sql results in:

UPDATE dlight_service_records SET renset_dato = '1970-01-01',

 

Where does it get the 1970-01-01 date from at this point? it should look like:

UPDATE dlight_service_records SET renset_dato = '2009-07-28',

 

Thanks in advance...