How to insert an array in to table using PDO in PHP

Question

I am new to PDO and need to insert simple array data to a table. print_r($ID) and print_r($CODE) are as follows respectively,

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
)
Array
(
    [0] => 1008M
    [1] => 1039E
    [2] => 1040E
    [3] => 198M
)

$ID and $CODE are fetched through another query which has in another host. I need to insert following array data to a table using PDO. Here is my try.

<?php

$db = new PDO("mysql:host=localhost;dbname=testdatabase", 'root', '');
$db ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "INSERT INTO dummy (ID, CODE) VALUES (?,?)";
$stmt = $db->prepare($sql);
$stmt->execute(array($ID, $CODE));

?>

But this provides me an notice on,

Notice: Array to string conversion in C:\xampp\htdocs\mytestdata\test1.php on line 36

and the output in the dummy table shows as follows,

ID CODE
0  array

can someone show me where I have messed this code?

Expected Result:

ID CODE
1  1008M
2  1039E
3  1040E
4  198M

What's the expected result? How does the table handle these arrays internally? — Nico Haase, Feb 14 '22 at 13:36
@NicoHaase Updated. Please check. The arrays are getting through another sql query which has in different host. Upto print_r($ID) it fine. — veganbu chat, Feb 14 '22 at 13:43
If these are meant to be individual rows, you'll need to create a loop to insert the data. — aynber, Feb 14 '22 at 13:43
If you want to insert one row per array element, you need to write your code accordingly — Nico Haase, Feb 14 '22 at 13:43
Possible duplicate of : [Insert multiple rows with PDO prepared statements](https://stackoverflow.com/questions/19680494/insert-multiple-rows-with-pdo-prepared-statements) — Alive to die - Anant, Feb 14 '22 at 13:51

tecnico57 · Answer 1 · 2022-02-14T14:07:11.680

-1

I guess you need merge two array (ID,CODE), beacuse excute method can use one array. This is reason because your print is ID = 0, and CODE = array.

You can use array_combine to combine values of ID to key of CODE.

Update :

$ID = [
    "0" => 1,
    "1" => 2,
    "2" => 3,
    "3" => 4,
];

$CODE = [
    "0" => "1008M",
    "1" => "1039E",
    "2" => "1040E",
    "3" => "198M",
];

$result = array_combine($ID,$CODE);

$db = new PDO("mysql:host=localhost;dbname=stackoverflow", 'root', 'root');
$db ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

foreach($result as $key => $value){
    $sql = "INSERT INTO dummy (ID, CODE) VALUES (?,?)";
    $stmt = $db->prepare($sql);
    $stmt->execute(array($key,$value));
}

I hope I was helpful. Best regards,

edited Feb 14 '22 at 14:07

answered Feb 14 '22 at 13:47

tecnico57

37
2

Please share more details, like the code involved. How could merging the arrays produce the given database rows? – Nico Haase Feb 14 '22 at 13:48
I tried to write a little code of how I would do it, hope it will be useful to you – tecnico57 Feb 14 '22 at 14:07

How to insert an array in to table using PDO in PHP

1 Answers1