merge two arrays with numeric keys for values of associative array to insert in MySQL in multiple rows

Question

I'm getting two arrays $post_img & $post_sort resulting:

 Array
(
    [0] => test1.jpg
    [1] => test2.jpg
    [2] => test3.jpg
)
Array
(
    [0] => 3
    [1] => 1
    [2] => 2
)

I'd like to merge them like:

Array
(
    [Image] => Array
        (
            [0] => test1.jpg
            [1] => test2.jpg
            [2] => test3.jpg
        )
    [Sort] => Array
        (
            [0] => 3
            [1] => 1
            [2] => 2
        )
)

Because I think its the best way to insert them into my Database each entry in one row like:

ID | Image       | Sort 
1    test1.jpg     3
2    test2.jpg     1
3    test3.jpg     2

The point is that I think this should be possible with only one query. I had different tries but none of them ended up good.

Can you show the inserting code here? It's not a problem to make array of arrays: `$arr = array('image' => $post_img, 'sort' => $post_sort);`, but it's only the beginning. — raina77ow, Mar 04 '13 at 14:42
how to insert this is exactly what I'm searching for with only one query. (Sorry for my confusing question!) — maisch, Mar 04 '13 at 14:52

Mark Baker · Answer 1 · 2013-03-04T14:54:45.163

2

Using a multiple iterator

$mi = new MultipleIterator();
$mi->attachIterator(new ArrayIterator($post_img));
$mi->attachIterator(new ArrayIterator($post_sort));

foreach ( $mi as $value ) {
    list($filename, $sortOrder) = $value;
    echo $filename , ' => ' , $sortOrder , '<br />';
}

might make it easier to process both arrays at the same time for your database inserts

edited Mar 04 '13 at 14:54

answered Mar 04 '13 at 14:45

Mark Baker

209,507
32
346
385

1

+1 Real classy (and, I heard, will be implemented even more 'nativy' in PHP 5.5). – raina77ow Mar 04 '13 at 14:49
thats a good approach but unfortunately I'm not working on PHP >= 5.3.0 – maisch Mar 04 '13 at 14:59
1

@maisch - you really should get your ISP to update then, PHP5.2 is officially unsupported, and PHP5.3 support is now approaching its official end – Mark Baker Mar 04 '13 at 15:04
@MarkBaker You're so right! Just had a try because of the new crypt algorithms - but until now I have to get on with PHP5.2 – maisch Mar 04 '13 at 15:13
@maisch - It's a pain for me too.... as a developer of OpenSource PHP libraries, I still need to maintain 5.2 compatibility because so many ISP's won't upgrade :( – Mark Baker Mar 04 '13 at 15:15

michi · Accepted Answer · 2013-03-04T15:22:04.413

0

Use array:

$newarray = array('Image' => $post_img, 'Sort' => $post_sort);

For adding the data to your table, you can go with your original arrays:

$sql = "INSERT INTO tablename SET Image=:image, Sort=:sort";
$dbImgInsert = $db->prepare($sql); // preparing sql for insert, using parameters

$c = count($post_img)-1;

for ($i=0;$i<=$c;$i++;) { // looping through the arrays

    $dbImgInsert->execute(array(   // inserting the data into the prepared query and into the db
        'image' => $post_img[$i],
        'sort' => $post_sort[$i]
        ));
} // for

The above example assumes you are using MySQL with PDO.

To create a single INSERT-statement the classic way, go:

$sql="";
$c = count($post_img)-1;
for ($i=0;$i<=$c;$i++;) { // looping through the arrays

    $sql.="('{$post_img[$i]}', '{$post_sort[$i]}'), ";
}
$sql ="INSERT INTO tablename (Image, Sort) VALUES " . trim($sql,',');

edited Mar 04 '13 at 15:22

answered Mar 04 '13 at 14:43

michi

6,565
4
33
56

That could cause some bottlenecks for large arrays. A few hundred insert statements is not something you want every visitor on your site doing with each page refresh. – Husman Mar 04 '13 at 14:59
@Husman do you have experience with large arrays and prepared statements? it's only the parameters that get passed, I wonder how performance is compared to mysql_* – michi Mar 04 '13 at 15:05
Each PDOStatement::execute() statement is a seperately run mysql query with different parameters. So that for loop would run 200 times, if count($post_img) == 200. – Husman Mar 04 '13 at 15:09
@michi Thanks for the examples. In the second example shouldn't it be '$sql="";' and in the third line 'for ($i=0;$i<=$c;$i++) {' ? – maisch Mar 04 '13 at 15:17
Actually that solution was suitable for me (because unfortunately I could not use MarkBaker's because of PHP5.2) - in fact easy, thank you. – maisch Mar 04 '13 at 15:31

score 0 · Answer 3 · answered Mar 04 '13 at 14:46

0

There is no point merging the arrays, you can just do a SQL statement like so:

INSERT INTO tbl (Image, Sort) VALUES
($post_img[0], $post_sort[0]),
($post_img[1], $post_sort[1]),
($post_img[2], $post_sort[2]),
($post_img[3], $post_sort[3])

The latter half of that query can be generated using a loop of some sort.

answered Mar 04 '13 at 14:46

Husman

6,819
9
29
47

wrap at least $post_img in '' – michi Mar 04 '13 at 15:06
No need, this is the generic statement, not something that should be plugged into his code. However he decides to implement this statement (hopefully no the deprecated mysql* way), his code will take into account data types. – Husman Mar 04 '13 at 15:12
See the top answer here (http://stackoverflow.com/questions/10060721/pdo-mysql-insert-multiple-rows-in-one-query) - for one way to achieve this – Husman Mar 04 '13 at 15:17

merge two arrays with numeric keys for values of associative array to insert in MySQL in multiple rows

3 Answers3