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.

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.

Comments

Your email address will not be published.