PHP array into a mysql table

Question

I have a php array. I want to insert all of them into a column in mysql table using PDO.

$myArray = array("12397", "45263426", "253725372","2735724372");
$sql = "DROP TEMPORARY TABLE IF EXISTS T_Temp;
        CREATE TEMPORARY TABLE T_Temp (
        AccountID varchar(120)
      );";

Now I want to insert all the array values into T_Temp table in single shot. I don't want to use foreach.

I don't want to use foreach...why would that matter? Just use whatever works. — ADyson
– ADyson, Commented Aug 23, 2021 at 15:17
I have a lot of data. foreach might take a lot of time. It would be great if there is any other way to achieve it. — Jayakrishnan Karunanithi
– Jayakrishnan Karunanithi, Commented Aug 23, 2021 at 15:57
"foreach might take a lot of time" - why should it? Such a loop is fast, and if you need something more performant, better import the data directly into the database, without using PHP — Nico Haase
– Nico Haase, Commented Aug 23, 2021 at 15:59
I will do the performance testing between 'foreach' and solution from Bill Karwin. — Jayakrishnan Karunanithi
– Jayakrishnan Karunanithi, Commented Aug 23, 2021 at 21:12

Bill Karwin · Accepted Answer · 2021-08-23 15:27:55Z

1

Here's a solution that does not use foreach:

<?php

$myArray = array("12397", "45263426", "253725372","2735724372");
$sql = "DROP TEMPORARY TABLE IF EXISTS T_Temp;
        CREATE TEMPORARY TABLE T_Temp (
        AccountID varchar(120)
      );";

$pdo = new PDO('mysql:dbname=test;host=127.0.0.1', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec($sql);

$placeholders = implode(",", array_fill(1, count($myArray), "(?)"));
$insertSql = "INSERT INTO T_Temp (AccountID) VALUES {$placeholders}";

$pdo->prepare($insertSql)->execute($myArray);

answered Aug 23, 2021 at 15:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Comments

Raphael PICCOLO · Accepted Answer · 2021-08-23 14:43:06Z

0

to insert multiple values at once you can write this kind of query

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

answered Aug 23, 2021 at 14:43

Raphael PICCOLO

2,1881 gold badge16 silver badges20 bronze badges

Collectives™ on Stack Overflow

PHP array into a mysql table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related