1

I've looked at all of the other posts about inserting arrays in PDO but have been unable to figure out exactly how to do it. I want to take this:

$images_ar = 
Array
(
[0] => Array
    (
        [0] => 100
        [1] => Lips
        [2] => 50
        [3] => 50
        [4] => 127
        [5] => 257
        [6] => 9998
        [7] => 70
        [8] => xxx
    )

[1] => Array
    (
        [0] => 103
        [1] => Ball
        [2] => 117
        [3] => 114
        [4] => 128
        [5] => 44
        [6] => 9997
        [7] => 70
        [8] => xxx
    )

[2] => Array
    (
        [0] => 104
        [1] => Sun
        [2] => 94
        [3] => 91
        [4] => 48
        [5] => 277
        [6] => 9996
        [7] => 70
        [8] => xxx
    )

)

And perform a PDO insert where [0] = image_id and [9] = avatar_id.

Utilizing other posts, so far, I have this:

$images_ar = array_chunk($ar, 9);

$handler->beginTransaction();

foreach($images_ar as $row){
   $question_marks[] = "('',?,?,?,?,?,?,?,?)";
}

$query = $handler->prepare ("INSERT INTO avatars (ID, img_id, title, width, height, top, left, zindex, userid, avatar_id) VALUES " . implode(',', $question_marks);

$query->execute($ar);

$handler->commit();

Errors:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left, zindex, userid, avatar_id) VALUES ('',?,?,?,?,?,?,?,?),('',?,?,?,?,?,?,?,?' at line 1' in ...\save.php:52 Stack trace:0 ...\save.php(52): PDO->prepare('INSERT INTO ava...') 1 {main} thrown in ...\save.php on line 52

Am I on the right track?

Thanks guys! =D

Skryeur
  • 89
  • 10
  • 2
    To start with, `LEFT` is a [reserved word in MySQL](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html). – Joachim Isaksson Aug 02 '14 at 09:16
  • And I think you've got to flatten your array with the values too. – VMai Aug 02 '14 at 09:26
  • `$image_ar` is a chunked version of the flat `$ar`. I chucked it so I could pass the chunked array through a `foreach()` so I could generate an array of placeholders.....Does wrapping `left` in back ticks solve the reserved word issue? – Skryeur Aug 02 '14 at 09:51
  • Never mind my question. I just decided to change the column name. – Skryeur Aug 02 '14 at 10:18

1 Answers1

1

I was able to get it to work by changing the keys using the array_combine() method.

The final code is:

$images_ar = array_chunk($ar, 9);
$keys = array('img_id', 'title', 'width', 'height', 'top', 'lef', 'zindex', 'userid', 'avatar_id');
foreach ($images_ar as $row) {  
    $list[] = array_combine($keys, array_values($row));
}
$handler->beginTransaction();
foreach($images_ar as $row){
   $question_marks[] = "(DEFAULT,?,?,?,?,?,?,?,?,?)";
}
$query = $handler->prepare ("INSERT INTO avatars (ID, img_id, title, width, height, top, lef, zindex, userid, avatar_id) VALUES " . implode(',', $question_marks));
$query->execute($ar);
$handler->commit();
Skryeur
  • 89
  • 10