使用数字键合并两个数组，以便在多行中插入MySQL中的关联数组值

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.

Answer 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.

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