0

Possible Duplicate:
What is the best way to insert into and update a single row table in MySQL?
handling duplicate records in mysql Insert statement

I have a php web form which inserts a record into my database when the 'submit' button is clicked. I would like however that if a record already exists in the database with the same primary key (itemID), the insert operation would be aborted and the user would be alerted.

My 'insert' code:

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO inventory (itemID, name, itemcategory, qis, reorderlevel, unitcost, sellingprice,
supplier, specifications) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
 GetSQLValueString($_POST['itemID'], "text"),
 GetSQLValueString($_POST['name'], "text"),
 GetSQLValueString($_POST['itemcategory'], "text"),
 GetSQLValueString($_POST['qis'], "double"),
 GetSQLValueString($_POST['reorderlevel'], "int"),
 GetSQLValueString($_POST['unitcost'], "double"),
 GetSQLValueString($_POST['sellingprice'], "double"),
 GetSQLValueString($_POST['supplier'], "text"),
 GetSQLValueString($_POST['specifications'], "text"));

mysql_select_db($database_con1, $con1);
$Result1 = mysql_query($insertSQL, $con1) or die(mysql_error());

$insertGoTo = "insertsuccess.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];}

header(sprintf("Location: %s", $insertGoTo));}
Community
  • 1
  • 1
Aetos
  • 25
  • 1
  • 5
  • Use unique keys in your schema – hohner Jan 21 '13 at 16:41
  • You could make a SELECT right before inserting, including all those $_POST variables in a WHERE condition. – Ivo Pereira Jan 21 '13 at 16:45
  • Just to note, the `mysql` extension is deprecated as of php 5.5.0 http://php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated. If this is new code then you might want to make the switch now. – Kevin Brydon Jan 21 '13 at 16:47

3 Answers3

2

If you are using MySQL there is the INSERT ... ON DUPLICATE KEY UPDATE structure.

In your case:

 INSERT INTO inventory (itemID, name, itemcategory, qis, reorderlevel, unitcost, sellingprice,supplier, specifications) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE name = %s, itemcategory = %s, ....)

This requires itemId to be defined in the table as unique key. Which you want anyway, according to your question.

Community
  • 1
  • 1
berkes
  • 26,996
  • 27
  • 115
  • 206
0

If you define the ItemID column as a primary key in the table definition, the query will give you an error in case of duplicate keys. You can alert the user, in the error catch part.

Note: Mysql extensions are deprecated, use MYSQLi_* or PDO extensions instead.

Teena Thomas
  • 5,139
  • 1
  • 13
  • 17
0

You can use 'INSERT IGNORE' statement, and check for LAST_INSERT_ID(). If LAST_INSERT_ID() returns 0, that means it is duplicate entry, and you can alert user.

yajakass
  • 279
  • 1
  • 7
  • `LAST_INSERT_ID()` works in some situations but is not (always) thread-safe and therefore never really race-condition-safe. Meaning: it will break horrendously in a website where things get inserted simultaneously. In other words: use with great care. – berkes Jan 21 '13 at 17:04