Object Oriented Style

Insert Or Update If Already Present

(Update if primary keys are not unique)

  $stmt = $maindb->prepare("
    INSERT INTO my_table
    (
      MyColumn1,
      MyColumn2
    ) VALUES (
      ?,
      ?
    )
    ON DUPLICATE KEY UPDATE
      MyColumn1 = ?,
      MyColumn2 = ?
    ");
  $stmt->bind_param("sisi", $MyColumn1, $MyColumn2, $MyColumn1, $MyColumn2);
  $MyColumn1 = "abc";
  $MyColumn2 = 12;
  $stmt->execute();
  $stmt->close();

Insert Or Update If Already Present WHERE

You can ‘t use WHERE with ON DUPLICATE KEY UPDATE. A typical solution is to use a double operation instead, INSERT IGNORE followed by and UPDATE WHERE

Insert or Do Nothing If Already Present

(Checks to see if primary keys are unique)

  $stmt = $maindb->prepare("
    INSERT IGNORE INTO my_table
    (
      MyColumn1,
      MyColumn2
    ) VALUES (
      ?,
      ?
    )");
  $stmt->bind_param("si", $MyColumn1, $MyColumn2);
  $MyColumn1 = "abc";
  $MyColumn2 = 12;
  $stmt->execute();
  $AffectedRows = $stmt->affected_rows;
  $stmt->close();

ON DUPLICATE KEY DELETE FROM

No you can’t do a delete!

Procedural Style – Old PHP4 Code

Insert Or Update If Already Present

$result = @mysql_query("
	INSERT INTO my_table (
		some_int,
		some_string
	) VALUES (
		$my_int,
		'$my_string'
	)
	ON DUPLICATE KEY UPDATE
		some_int = $my_int,
		some_string = '$my_string'
	");

Insert or Do Nothing If Already Present

This simply tests to see if primary keys are unique:

$result = @mysql_query("
	INSERT IGNORE INTO my_table (
		some_int,
		some_string
	) VALUES (
		$my_int,
		'$my_string'
	)");

If you have an auto column (e.g. a primary index that’s just a record ID say) then the above won’t work because that column will have a new value and so the new row is not a duplicate – result is a new row is added.  To solve this you need to add a UNIQUE index to the table.  A unique index can have multiple columns creating it so you simply add the columns you want to never be duplicated to this.  MySQL tools will typically let you do this – look for ‘Indexes’ and then for one you can create with multiple columns and that allows its type to be set to unique.