Delete and Insert record at the same time PHP

Question

Hi i am planning to delete and insert values at the same time when i click submit it will delete the old values and at the same time it will insert new values

Here is my code.

<?php    
if (isset($_POST['submit'])) {    
  $color1 = $_POST['color1'];
  $count = count($color1);       
  for ($x = 0; $x <=$count; $x++) {
    $savecolor = $color1[$x];
    $stmt = $db->prepare("DELETE FROM productcolor WHERE productinformationID =  :field0");
    $stmt->execute(array(':field0' => $prodID));    
    $stmt = $db->prepare("INSERT INTO productcolor(productinformationID,colorName) VALUES(:field0,:field00)");
    $stmt->execute(array(':field0' => $prodID, ':field00' => $savecolor));
   } 
} 
?>

It only delete my values there's no value saving in my database..

Please help me. Thanks

Just check, which branch it is taking, if or else by printing some dummy message. — niyasc, Nov 26 '15 at 09:46
What is the value of `$color1` ?? also show your form code!! — Saty, Nov 26 '15 at 09:47
or more why not: insertOrReplace http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update and in the else just delete? — Thomas, Nov 26 '15 at 09:52
@user5598179 [Apparently not](http://stackoverflow.com/questions/234/sql-server-2005-implementation-of-mysql-replace-into) — h2ooooooo, Nov 26 '15 at 09:54

score 0 · Answer 1 · answered Nov 26 '15 at 09:56

0

The mysql support in PHP don't allow multiple statemets in one query, so your task to delete a row and update on the same query is not possible.

You could use a INSERT... OR UPDATE statement instead of DELETE and INSERT to get close to your task you like to achieve.

http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

E.g

INSERT INTO productcolor(productinformationID,colorName) VALUES("foo", "bar") ON DUPLICATE KEY UPDATE productinformationID=VALUES(productinformationID), colorName=VALUES(colorName)

answered Nov 26 '15 at 09:56

ins0

3,918
1
20
28

i think he just want an UPDATE statement :o – hanshenrik Nov 26 '15 at 09:59
as he is using an ``INSERT`` in his code too, combine the ``INSERT`` and suggested ``UPDATE`` would safe him one database query. Or i'm wrong? – ins0 Nov 26 '15 at 10:00
"*It is okey to use replace into in SQL Server?*" - he's not using MySQL. – h2ooooooo Nov 26 '15 at 12:01
didn't got that information as i wrote the answer :) – ins0 Nov 26 '15 at 12:06

score 0 · Answer 2 · answered May 29 '17 at 04:48

                  <?php
                global $wpdb;
            if(isset($_POST['submit'])) {
                    $checkBox =$_POST['chk1'];
                    $wpdb->query( "DELETE FROM wp_vandor WHERE parent_id = '".$user_id."'" );
                foreach($checkBox as $checkedfv){
                        $wpdb->query("INSERT INTO wp_vandor  (parent_id, child_id) VALUES ('".$user_id."', '".$checkedfv."')");
                    }
                    $crpurl = home_url('favorite-vendors');
                    header("Location: ".$crpurl);
              }

            ?>

Delete and Insert record at the same time PHP

2 Answers2