CODEWITHSUNDEEP
phparraysinsertprepared-statement
Markus
Markus

Reputation: 616

Insert multple rows with one insert

So I have three tables in a MySQL database: 

order(id, etc..), 
product(id, title, etc..) 
orderproduct(productFK, orderFK)

Now I want to be able to insert an order with one order-id and (in some cases) multiple product-ids for orders containing more than one product:

order 1: orderid = 1, productids = 1
order 2: orderid = 2, productids = 2, 3

this while using prepared statements, like: 

        $stmt = $mysqli->prepare("INSERT INTO orderproduct (orderFK, productFK)
                                  VALUES (?, ?)");
        $result = $stmt->bind_param('ss', $orderid, $productid);

        if($stmt->execute() == false) {
            $flag = false;
        }
        $stmt->close();

One obvious solution is to loop the insert query but is there another way to do this without having to call the database multiple times?

This is working (hardcoded) but still, I can't figure out how to fill the bind_param dynamically.. 

        $strings = "";
        $values = "";
        foreach ($params['products'] as $product) {
            $strings .= 'ss';
            $values .= "(?, ?),";
        }
        $values = substr($values, 0, -1);

        $productid = array(1, 2);
        $stmt = $mysqli->prepare("INSERT INTO orderproduct (orderFK, productFK)
                                  VALUES " . $values);
        $result = $stmt->bind_param($strings, $orderid, $productid[0], $orderid, $productid[1]);
        if($stmt->execute() == false) { 
            $flag = false;
        }
        $stmt->close();

Upvotes: 0

Views: 35

Answers (2)

symcbean
symcbean

Reputation: 48357

Mysql will happily process something like:

INSERT INTO mytable (col1, col2)
VALUES (c1a, c2a)
     ,(C1b, c2b)
     ,(C1c, c2c)
     ...

(Up to max_allowed_packet) however expressing the bind_param() call cleanly (in a way which is easy to debug) will be rather difficult. Invoking with call_user_func_array() means you can simply pass a single array as the argument, but you still need to map your 2 dimensional data set to a one dimensional array.

Previously I've used the procedural mysql api to do multiple inserts resulting in a significant speed up of inserts and better balanced indexes.

Upvotes: 0

jeroen
jeroen

Reputation: 91744

If you are on php 5.6+ you can use argument unpacking ... to bind your variables:

$args = [
    $arg1,
    $arg2,
    $arg3,
    $arg4,
];
$result = $stmt->bind_param($strings, ...$args);

An alternative would be to use PDO where you can send an array of arguments to bind to the execute() method.

Upvotes: 2

Related Questions