CODEWITHSUNDEEP
phparrayspdoinsertimplode
Satyam
Satyam

Reputation: 5

PHP INSERT with array function

I am trying to insert the data in PHP with the array keys wise. I know how to insert data function when if data is single and without array. but this is an issue is comes first in my beginning PHP. I have seen the other post with the `implode function but I couldn't understand that.

I am trying if product 123456 the QTY will insert 100 AND if product 123654 the QTY will 150 and other data will constantly insert.

DATABASE STRUCTURE

---------------------------------------------------------------------------
| ID |  SKU    |   PRODUCT    |   QTY   |  SUBJECT          | ANY_COMMENT |
---------------------------------------------------------------------------
| 1  | 106101  |   123456     |   100   |  MY TEST SUBJECT  | MY_COMMENT  |
---------------------------------------------------------------------------
| 2  | 106101  |   123654     |   150   | MY TEST SUBJECT   | MY_COMMENT  |
---------------------------------------------------------------------------

PHP ARRAY

Array
(
    [subject] => MY TEST SUBJECT
    [sku] => 106101
    [product] => Array
        (
            [0] => 123456
            [1] => 123654
        )

    [qty] => Array
        (
            [0] => 100
            [1] => 150
        )

    [comment] =>MY_COMMENT
)

PHP CODE

$stmt = $con->prepare("INSERT INTO `table` SET(`SKU`, `PRODUCT`, `QTY`, `SUBJECT`, `ANY_COMMENT`) VALUES(:sku, :product, :qty, :subject, :comment)");

$stmt->execute();

Upvotes: 0

Views: 80

Answers (1)

Nick
Nick

Reputation: 147166

You can do this by binding parameters for each of your table columns; assign the constant values, and then iterate over the varying values in a loop, using the key into one array as the key for the other, and executing the statement on each pass through the loop. Something like this should work:

// prepare statement
$stmt = $con->prepare("
INSERT INTO `table` (`SKU`, `PRODUCT`, `QTY`, `SUBJECT`, `ANY_COMMENT`) 
VALUES(:sku, :product, :qty, :subject, :comment)
");

// bind parameters
$stmt->bindParam(':sku', $sku, PDO::PARAM_INT);
$stmt->bindParam(':subject', $subject, PDO::PARAM_STR);
$stmt->bindParam(':comment', $comment, PDO::PARAM_STR);
$stmt->bindParam(':product', $product, PDO::PARAM_INT);
$stmt->bindParam(':qty', $qty, PDO::PARAM_INT);

// assign values and execute
$sku = $data['sku'];
$subject = $data['subject'];
$comment = $data['comment'];
foreach ($data['product'] as $key => $product) {
    $qty = $data['qty'][$key];
    $stmt->execute();
    echo "$product $qty\n";
}

Note that you don't need SET in that format of an INSERT statement.

Upvotes: 1

Related Questions