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
– Barmar, Commented May 12, 2015 at 17:08
You can use REPLACE to combine INSERT and UPDATE in a single query. But you need to do DELETE separately. — Barmar
– Barmar, Commented May 12, 2015 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
– user3236149, Commented May 12, 2015 at 17:10
A new ID is only assigned if you specify id = NULL in the REPLACE statement. — Barmar
– Barmar, Commented May 12, 2015 at 17:16
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
– Barmar, Commented May 12, 2015 at 17:26

Barmar · Accepted Answer · 2015-05-12 21:08:06Z

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, 2015 at 21:08

answered May 12, 2015 at 19:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

user3236149 Over a year ago

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()

Barmar Over a year ago

Needed to check if $_POST['delete'] is set before checking against it. If nothing is deleted, that parameter won't be sent.

Barmar Over a year ago

array_repeat should have been str_repeat, sorry about that.

user3236149 Over a year ago

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

Barmar Over a year ago

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.

|

Collectives™ on Stack Overflow

Insert, update or delete in multiple row form

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related