Modify insert into to update

Question

I want to modify my code so instead of just inserting a new row in the MySQL table, it can check to see if there is one with the same item number, and update it.

php code

    <?php
    $con=mysqli_connect("localhost","root","root","inventory");

    // Check connection
    if (mysqli_connect_errno($con))
      {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }



     $sql = "INSERT INTO `current stock` (ItemNumber, Stock)
               VALUES
             ('".$_POST['ItemNumber']."', '".$_POST['Stock']."' )";

    if (!mysqli_query($con,$sql))
      {
      die('Error: ' . mysqli_error($con));
      }
     echo "1 record added";


     mysqli_close($con);
     ?>

Possible duplicate: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql — showdev, Jun 26 '13 at 16:54

score 1 · Answer 1 · answered Jun 26 '13 at 16:57

You can use ON DUPLICATE KEY UPDATE syntax,

$sql = "
   INSERT INTO `current stock` (ItemNumber, Stock)
   VALUES ('$_POST[ItemNumber]', '$_POST[Stock]' )
   ON DUPLICATE KEY UPDATE
   Stock = '$_POST[Stock]'
";

ItemNumber should be primary/unique key in this case

Modify insert into to update

1 Answers1