Insert, update or delete in multiple row form

Question

I need to known how can I make a INSERT, UPDATE or DELETE in multiple rows if in the invoice form I make some changes in products like edit one, delete others or add new ones with one query... I tried with the update but if I delete one or two products or if I add one or two and save the form, in the DB the changes are not made, only the data that previously are in the DB was updated.

Here my code:

            if (!empty($_POST)) {
                $conn->beginTransaction();
                $stmt = $conn->prepare("UPDATE PRODUCTOS SET `cod` = :cod, `nombreProd` = :nombreProd, `proveedor` = :proveedor, `existencia` = :existencia, `ref_compra` = :ref_compra 
                WHERE `id_p` = :id_p");
                $stmt->bindParam(":cod", $cod, PDO::PARAM_STR);
                $stmt->bindParam(":nombreProd", $nombreProd, PDO::PARAM_STR);
                $stmt->bindParam(":proveedor", $proveedor, PDO::PARAM_STR);
                $stmt->bindParam(":existencia", $existencia, PDO::PARAM_STR);
                $stmt->bindParam(":ref_compra", $ref_compra, PDO::PARAM_STR);
                $stmt->bindParam(":id_p", $id_p, PDO::PARAM_INT);

                foreach ($_POST['id_p'] as $i => $id_p) {
                    $cod = $_POST['cod'][$i];
                    $nombreProd = $_POST['nombreProd'][$i];
                    $proveedor = $_POST['proveedor'][$i];
                    $existencia = $_POST['existencia'][$i];
                    $ref_compra = $_POST['ref_compra'];
                    $id_p = $_POST['id_p'][$i];
                    $stmt->execute();
                }
                $conn->commit();
            }

EDIT

Here is my multiple insert code:

            $conn->beginTransaction();
            $sql = "INSERT INTO PRODUCTOS
            (cod, nombreProd, proveedor, existencia, compra, tCompra, f_vencimiento, id_user, nombre, ref_compra, f_compra)
             VALUES ";
            $insertQuery = array();
            $insertData = array();
            foreach ($_POST['cod'] as $i => $cod) {
                $insertQuery[] = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
                $insertData[] = $_POST['cod'][$i];
                $insertData[] = $_POST['nombreProd'][$i];
                $insertData[] = $_POST['proveedor'][$i];
                $insertData[] = $_POST['existencia'][$i];
                $insertData[] = $_POST['compra1'][$i];
                $insertData[] = $_POST['total1'][$i];
                $insertData[] = $_POST['f_vencimiento'][$i];
                $insertData[] = $_POST['id_user'];
                $insertData[] = $_POST['nombre'];
                $insertData[] = $_POST['ref_compra'];
                $insertData[] = $_POST['fecha'];
            }
            if (!empty($insertQuery)) {
                $sql .= implode(', ', $insertQuery);
                $stmt = $conn->prepare($sql);
                $stmt->execute($insertData);
            }
            $conn->commit();

There's no `INSERT` or `DELETE` code in your PHP, why would you expect that to happen? — Barmar, May 12 '15 at 17:08
You can use `REPLACE` to combine `INSERT` and `UPDATE` in a single query. But you need to do `DELETE` separately. — Barmar, May 12 '15 at 17:10
@Barmar With the multiple row I don't know where or how make the query...this multiple insert is totally new for me...sorry — user3236149, May 12 '15 at 17:10
You do it the same as you do for the `UPDATE`. The form fields will be in an array. — Barmar, May 12 '15 at 17:11
@Barmar With the `REPLACE` don't delete the id and "replace" with a new one? — user3236149, May 12 '15 at 17:12
A new ID is only assigned if you specify `id = NULL` in the `REPLACE` statement. — Barmar, May 12 '15 at 17:16
@Barmar thank you I will search about it...one more question... How can I delete a row (id_p) in the code if the id_p is not catch in the array? just only the new ones? — user3236149, May 12 '15 at 17:19
You should add a `Delete` checkbox to the form. Put the ID in the checkbox's value. Then you can loop through `$_POST['delete']` and delete all those rows. — Barmar, May 12 '15 at 17:26
@Barmar I see `REPLACE` and `ON DUPLICATE KEY UPDATE` and I think that `ON DUPLICATE KEY UPDATE` will work better with my necessity.. I am tried to works with the clause in my multiple `INSERT` clause but I don't do it well... Do you think that with my multiplite `INSERT`clause will be made what I need or I need to change the `INSERT` clause for use the `ON DUPLICATE KEY UPDATE`, I already edit the question with the insert code. — user3236149, May 12 '15 at 18:45
To use either `REPLACE` or `ON DUPLICATE KEY` you need a unique index other than the auto-increment ID field. Does your table have anything like that? — Barmar, May 12 '15 at 19:17
@Barmar yes, the unique index and primary key and auto-increment id is `id_p` — user3236149, May 12 '15 at 19:21

Barmar · Accepted Answer · 2015-05-12T21:08:06.427

2

Here's how to combine the INSERT and UPDATE codes. The form should have an empty id_p field in rows that are being inserted. This code replaces that with NULL in the INSERT, which tells the DB to assign it using auto-increment. The ON DUPLICATE KEY clause uses the VALUES() function to get the values from the row being inserted.

       $conn->beginTransaction();
        $sql = "INSERT INTO PRODUCTOS
        (id_p, cod, nombreProd, proveedor, existencia, compra, tCompra, f_vencimiento, id_user, nombre, ref_compra, f_compra)
         VALUES ";
        $insertQuery = array();
        $insertData = array();
        foreach ($_POST['cod'] as $i => $cod) {
            if (isset($_POST['delete']) && in_array($_POST['id_p'][$i], $_POST['delete'])) {
                // Skip rows that are being deleted
                continue;
            }
            $insertQuery[] = '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            $insertData[] = $_POST['id_p'][$i] == '' ? null : $_POST['id_p'][$i];
            $insertData[] = $_POST['cod'][$i];
            $insertData[] = $_POST['nombreProd'][$i];
            $insertData[] = $_POST['proveedor'][$i];
            $insertData[] = $_POST['existencia'][$i];
            $insertData[] = $_POST['compra1'][$i];
            $insertData[] = $_POST['total1'][$i];
            $insertData[] = $_POST['f_vencimiento'][$i];
            $insertData[] = $_POST['id_user'];
            $insertData[] = $_POST['nombre'];
            $insertData[] = $_POST['ref_compra'];
            $insertData[] = $_POST['fecha'];
        }
        if (!empty($insertQuery)) {
            $sql .= implode(', ', $insertQuery);
            $sql .= " ON DUPLICATE KEY UPDATE
                    cod = VALUES (cod), nombreProd = VALUES (nombreProd), proveedor = VALUES (proveedor), existencia = VALUES (existencia), ref_compra = VALUES (ref_compra)"
            $stmt = $conn->prepare($sql);
            $stmt->execute($insertData);
        }
        $conn->commit();

For deletions, you should have a Delete checkbox in each row:

Delete <input type="checkbox" name="delete[]" value="$row[id_p]">

Then you can delete them in one query with:

if (!empty($_POST['delete'])) {
    $sql = "DELETE FROM PRODUCTOS WHERE id_p IN (";
    $sql .= str_repeat("?, ", count($_POST['delete']) - 1);
    $sql .= "?)";
    $stmt = $conn->prepare($sel);
    $stmt->execute($_POST['delete']);
}

edited May 12 '15 at 21:08

answered May 12 '15 at 19:39

Barmar

741,623
53
500
612

your script is almost excellent, right now, when I do the update or insert the script does it, but with this warning: `Warning: in_array() expects parameter 2 to be array, null given` in each row in the form(e.g. if there are 5 rows there are 5 warnings), and if I tried to delete some product show me this : `Fatal error: Call to undefined function array_repeat()` – user3236149 May 12 '15 at 20:54
Needed to check if `$_POST['delete']` is set before checking against it. If nothing is deleted, that parameter won't be sent. – Barmar May 12 '15 at 21:08
`array_repeat` should have been `str_repeat`, sorry about that. – Barmar May 12 '15 at 21:09
the warnings are gone but when I tried to delete the row show me this: `Error: SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty` in the Array is catch the delete[] with a number, e.g.= 8 but don't delete the id – user3236149 May 12 '15 at 22:03
The delete query are deleting the new id's and not the id in the delete[], e.g. the last id was 25 and if I check the "checkbox" one time the query delete the id 26 and insert if are new row in the id 27 and so on...if I check more than one(2,3,etc) the query delete the number of id's (2,3,etc) there was in the checkbox. – user3236149 May 12 '15 at 22:16
I change a little bit your code in `DELETE` function, now is working complete and perfectly. I dared to edit that part in your Answer – user3236149 May 12 '15 at 22:50
Good catch, sorry about that. – Barmar May 13 '15 at 17:47
@Barmar, Hi, I working with this script and working perfect, but now I need to know how can I update a second table where only change the "existence" of each product. How can I do it? Right now I use the following sentences but when the item is repeated in another row of the form only save me the last one row: `foreach ($_POST['code'] as $i => $code) { $existence = ($_POST['existence'][$i] + $_POST['quantity'][$i]); //quantity is from form. $code = $_POST['code'][$i]; $sth = $conn->prepare("UPDATE PRODSLIST SET `existence` ='$existence' WHERE code= '$code'"); $sth->execute(); $i++;}` – asterix_jv Jul 31 '17 at 23:12
What is the HTML for the `existence` input? If it's a checkbox, see https://stackoverflow.com/questions/35856766/php-inserting-multiple-checkbox-and-textbox-arrays-into-mysql-database/35857094#35857094 – Barmar Jul 31 '17 at 23:56
@Barmar Hi, is a input, where the user manually insert the quantity of the sell of each product. I think this happen in the code: when the ajax sent the data in the form, the array put the quantity of each row, but when the item is duplicated in the same form not update the previous quantity and rewrite the value, like per example: the item coca cola had 15 units, but in the form have 5 more unit in the beginning and 2 more to the last, so the array first update 15+5=20 and then make the error because again catch the previos value 15+2=17... so I think this happen. – asterix_jv Aug 01 '17 at 20:55
1

If you're adding to items, the SQL should be like `SET existence = existence + :quantity`. Then each row will add on to what a previous row did. – Barmar Aug 01 '17 at 21:00
Is not necessary made an select to call the new existence before the new quantity? – asterix_jv Aug 01 '17 at 21:17
I don't know what you mean by "new existence". It sounds like your problem is different from this question, please post a new question so you can explain it clearly and post your code. – Barmar Aug 01 '17 at 21:26
Nevermind, I tried your code and is working perfect! thank you so much @Barmar. – asterix_jv Aug 01 '17 at 21:43

Insert, update or delete in multiple row form

1 Answers1