Mysql Update or Insert with empty values

Question

A quick question.

I'm using php to insert new records in a database table - if the record exists, an update command is run instead.

My question is, if the incoming record has fields that are empty but the data already in the database is not empty, does the update command overwrite the existing data with the empty values?

Much appreciated.

Answer 1

Choog, it depends on your update query. If your table has the fields named: some_id, foo, bar, baz, qux - and you have an UPDATE in your PHP script like such:

"UPDATE table SET foo = '$foo', bar = '$bar', baz = '$baz', qux = '$qux' WHERE some_id = '$id'"

This will update (overwrite) all the fields you specified. If any of those variables are NULL, or empty strings, then yes you will overwrite the existing data with a NULL (if allowed) or an empty string.

If you only update the fields you need to, say foo and bar for example, then it will not change the values of baz and qux. e.g.

"UPDATE table SET foo = '$foo', bar = '$bar' WHERE some_id = '$id'"

I don't know the specifics of what you're doing, but you may want to look into REPLACE INTO (http://dev.mysql.com/doc/refman/5.1/en/replace.html) and INSERT IGNORE (http://dev.mysql.com/doc/refman/5.1/en/insert.html) queries as well. They may be more suitable for what you're doing.

Answer 2

The update would overwrite if it is a simple MySQL update

UPDATE table SET field = '$newValue' WHERE id = '$id'

It is better to validate the data first.

Answer 3

If you only want to update values that aren't empty you may want to do something like this

$updates = array();
if ($var1 != '') $updates[] = sprintf("`var1` = '%s'", mysql_real_escape_string($var1));
if ($var2 != '') $updates[] = sprintf("`var2` = '%s'", mysql_real_escape_string($var2));
if (count($updates) > 0) {
     $query = sprintf("UPDATE table SET %s WHERE id = '%d' ", implode(", ", $updates), $id);
}

Answer 4

You can use an INSERT ... ON DUPLICATE statement.
And CoALESCE to keep an old value if null has been passed as "new" value.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', '...', '...');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// example table
$pdo->exec('CREATE TEMPORARY TABLE foo (
  id int auto_increment,  
  x int,  
  y int,  
  z int,
  primary key(id), 
  unique key(x) )
');

$stmt = $pdo->prepare("
    INSERT INTO
      foo (x,y,z)
    VALUES
      (:x,:y,:z)
    ON DUPLICATE KEY UPDATE
      y=COALESCE(:y, y),
      z=COALESCE(:z, z)
");
$stmt->bindParam(':x', $x);
$stmt->bindParam(':y', $y);
$stmt->bindParam(':z', $z);


$x = 1; $y = 1; $z=1;
$stmt->execute();
// duplicate key x=1
// overwriting y and z
$x = 1; $y = 2; $z=2;
$stmt->execute();


$x = 2; $y = 20; $z=17;
$stmt->execute();
// duplicate key x=2
// overwriting only z
$x = 2; $y = null; $z=21;
$stmt->execute();

unset($stmt);

foreach($pdo->query('SELECT * FROM foo', PDO::FETCH_NAMED) as $row) {
    foreach($row as $k=>$v) {
     echo $k, '=', $v, ' ';
    }
    echo "\n";
}

prints

id=1 x=1 y=2 z=2 
id=2 x=2 y=20 z=21