mysql 如何更新数据库字段为空,如何更新一个或多个字段而忽略mysql数据库中的空字段？...

i am seeking help on ignoring null values for updating the mysql database:-

$cst = $_POST['custname'];

$a = $_POST['tel'];

$b = $_POST['fax'];

$c = $_POST['email'];

$sql = mysql_query("UPDATE contacts SET TEL = '$a', FAX = '$b', EMAIL = '$c'

WHERE Cust_Name = '$cst' ");

how do i incorporate an option where the user can only select one or all fields for updation.

i tried using the following code based on responses received but it does the same thing. overwrites the existing data with the blank ones.

$upd = mysql_query("UPDATE custcomm_T SET

Telephone = ".(is_null($a)?'Telephone':"'$a'").",

Fax = ".(is_null($b)?'Fax':"'$b'").",

Mobile = ".(is_null($c)?'Mobile':"'$c'").",

EMail = ".(is_null($d)?'EMail':"'$d'").",

trlicense = ".(is_null($e)?'trlicense':"'$e'").",

trlicexp = ".(is_null($f)?'trlicexp':"'$f'")."

WHERE Cust_Name_VC = '$g' ") or die(mysql_error());

解决方案

Firstly remember to escape any strings coming to you via POST, GET, or REQUEST (read up on SQL injection attacks if you're unsure why).

Something like this might work:

$semaphore = false;

$query = "UPDATE contacts SET ";

$fields = array('tel','fax','email');

foreach ($fields as $field) {

if (isset($_POST[$field]) and !empty($_POST[$field]) {

$var = mysql_real_escape_string($_POST[$field]);

$query .= uppercase($field) . " = '$var'";

$semaphore = true;

}

}

if ($semaphore) {

$query .= " WHERE Cust_Name = '$cst'";

mysql_query($query);

}

NB: Do not ever simply loop through your $_POST array to create a SQL statement. An opponent can add extra POST fields and possibly cause mischief. Looping through a user input array can also lead to an injection vector: the field names need to be added to the statement, meaning they're a potential vector. Standard injection prevention techniques (prepared statement parameters, driver-provided quoting functions) won't work for identifiers. Instead, use a whitelist of fields to set, and loop over the whitelist or pass the input array through the whitelist.