mysql更新 - 跳过空白字段？

Question

I need to know the proper way of doing this. I have a form where someone can fill in 3 different inputs to update their data.

they can leave one blank if they want and just update the other two or just one. Whatever.

so if i update as:

mysql_query("UPDATE table SET field1=input AND field2=BLANK AND filed3=input WHERE ID=123);

will it leave the blank fields unchanged? just skip over them? or will it replace the field with an empty string/blank field?

If this is the wrong way, what is the correct method? Thank You!

Answer 1

It will replace them with blank values. The correct way to do it is not to put those items in the query at all:

if (empty($field1) && empty($field2) && empty($field3) {
  // show error message, nothing to do
  return;
}

$updates = array();
if (!empty($field1))
  $updates[] = 'field1="'.mysql_real_escape_string($field1).'"';
if (!empty($field2))
  $updates[] = 'field2="'.mysql_real_escape_string($field2).'"';
if (!empty($field3))
  $updates[] = 'field3="'.mysql_real_escape_string($field3).'"';
$updates = implode(', ', $updates);

mysql_query("UPDATE table SET $updates WHERE ID=123");

Obviously it would be cleaner to put the changes in an associative array or object, and then loop through them.