PHP INSERT with array function

Question

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

Nick · Accepted Answer · 2021-02-21 04:41:03Z

1

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.

answered Feb 21, 2021 at 4:41

Nick

147k23 gold badges67 silver badges106 bronze badges

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

8 Comments

Satyam Over a year ago

thank its working but why

$MY_PRODUCT_VAR = trim(stripslashes(htmlspecialchars($_POST['product']))); $MY_PRODUCT_VAR = preg_replace(['/\s{2,}/', '/[\t\n]/'], ' ', $MY_PRODUCT_VAR);

not working for security purpose

Nick Over a year ago

@Satyam I'm not sure what you mean by "not working for security purpose"?

Satyam Over a year ago

can I write like this $sku = trim(stripslashes(htmlspecialchars($data['sku'])))

Nick Over a year ago

@Satyam sure, you can, but if you're trying to ensure that the SKU is numeric or only contains numbers you should probably use something like ctype_digit or perhaps 'filter_var`

Satyam Over a year ago

could me once last tell me how I check and add validation in [qty] error when any key empty

|

Collectives™ on Stack Overflow

PHP INSERT with array function

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related