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

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

我得到两个数组$ post_img&$ post_sort结果:

 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.

关键是我认为这应该只有一个查询。我有不同的尝试,但没有一个结果很好。

3 个解决方案

#1

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.

上面的例子假设你使用MySQL和PDO。

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

要以经典方式创建单个INSERT语句,请转到:

$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,',');

#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

可能会使您更容易同时处理数据库插入的两个数组

#3

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

没有必要合并数组,你可以像这样做一个SQL语句:

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.

可以使用某种循环生成该查询的后半部分。

#1