How to insert an Array to mysql without executing in a loop

Question

i am currently running into an issue were i want to insert an array with prepared statements by pdo to my databse. I found some code here to start with, but currently its only inserting one of the files in the array $files which is passed to that method.

$amount = count($files);

for($i=0; $i<$amount; $i++){

    $dbArr=[
        'path'      => (string) dirname( $files[$i] ),
        'name'      => (string) basename( $files[$i] ),
        'size'      => (int) filesize($files[$i]),
        'mimeType'  => (string) mime_content_type( $files[$i] ),
    ];
}

foreach($dbArr as $k => $v ) {
    $prep[':'.$k] = $v;
}

new sql();

$q = sql::$db->prepare($str = "INSERT INTO filesSrc ( " . implode(', ',array_keys($dbArr)) . ") VALUES (" . implode(', ',array_keys($prep)) . ")");

$res = $q->execute($prep);

It works, but only inserts one of the array. the solution might be simple, i am overlooking something.

Bosco Wu · Accepted Answer · 2019-07-20 12:20:28Z

1

$params = [
        'path'      => ':path',
        'name'      => ':name',
        'size'      => ':size',
        'mimeType'  => ':mineType',
    ];
$sql    = "INSERT INTO filesSrc ( " . implode(',', array_keys($params)) . ") VALUES (" . implode(',', $params) . ")";
$amount = count($files);
$dbArrs = [];
for($i=0; $i<$amount; $i++){

    $dbArrs[] = [
        'path'      => (string) dirname( $files[$i] ),
        'name'      => (string) basename( $files[$i] ),
        'size'      => (int) filesize($files[$i]),
        'mimeType'  => (string) mime_content_type( $files[$i] ),
    ];
}



new sql();
#todo It is recommended to use 'try catch'
$q = sql::$db->prepare($sql);
foreach ($dbArrs as $dbArr) {
    foreach ($params as $key => $param) {
        $q->bindParam($param, $dbArr[$key]);
    }
    $q->execute();
}

edited Jul 20, 2019 at 12:20

answered Jul 20, 2019 at 11:53

Bosco Wu

412 bronze badges

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

4 Comments

Elias Over a year ago

Hey and welcome to StackOverflow. Please don't post a second answer if it's not fundamentally different. And also then I'd recommend packing it into one. For your information, you can edit your answer if you get more information or noticed anything that should be changed (click the edit button right under the question [bottom left])

Sascha Grindau Over a year ago

is executing in a loop the right way? i thought i would end up with one execute function at the end? Before i did such things with multiple statements in a loop, thats what i wanted to prevent this time.

Bosco Wu Over a year ago

Because I didn't see the overall code, I can't provide an accurate function.

Bosco Wu Over a year ago

If you want to insert a lot of data, it is recommended to use asynchronous mode.

Paul Spiegel · Accepted Answer · 2019-07-20 13:17:04Z

1

Unless you are inserting thousands of rows, I would just insert them one by one with a single prepared statement in a transaction.

$q = sql::$db->prepare("
    INSERT INTO filesSrc (path, name, size, mimeType)
    VALUES (:path, :name, :size, :mimeType)
";

sql::$db->beginTransaction();

foreach ($files as $file) {
    $q->execute([
        'path'      => (string) dirname($file),
        'name'      => (string) basename($file),
        'size'      => (int) filesize($file),
        'mimeType'  => (string) mime_content_type($file),
    ]);
}

sql::$db->commit();

If you really want to execute a bulk insert (single multi-row insert), then I would use ? as placeholder:

$values = [];
$params = [];

foreach ($files as $file) {
    $values[] = '(?,?,?,?)';
    $params[] = (string) dirname($file);
    $params[] = (string) basename($file);
    $params[] = (int) filesize($file);
    $params[] = (string) mime_content_type($file);
}

$values = implode(',', $values);

$q = sql::$db->prepare("
    INSERT INTO filesSrc (path, name, size, mimeType)
    VALUES ($values)
";

$res = $q->execute($params);

To use named parameters instead of ?, change the for-loop to:

foreach ($files as $k => $file) {
    $values[] = "(:path_{$k}, :name_{$k}, :size_{$k}, :mimeType_{$k})";
    $params['path_{$k}'] = (string) dirname($file);
    $params['name_{$k}'] = (string) basename($file);
    $params['size_{$k}'] = (int) filesize($file);
    $params['mimeType_{$k}'] = (string) mime_content_type($file);
}

edited Jul 20, 2019 at 13:17

answered Jul 20, 2019 at 12:41

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

4 Comments

Sascha Grindau Over a year ago

what function gives me a correct boolean return of the db success? $q? or sql::$db->commit()?

Paul Spiegel Over a year ago

Don't you use PDO in exception mode?

Sascha Grindau Over a year ago

i am using this param PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

Paul Spiegel Over a year ago

Then a failed prepare(...) or execute(..) will raise an exception and you don't need a boolean to check for errors. Use try-catch blocks instead if needed.

Collectives™ on Stack Overflow

How to insert an Array to mysql without executing in a loop

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related