Best approach to move into this array and build a SQL sentence

Question

I have this array result:

Array
(
    [homeclub] => Array
        (
            [0] => MAGA
            [1] => AGUI
            [2] => BRAV
            [3] => TIBU
        )

    [homeclub_annotation] => Array
        (
            [0] => 3
            [1] => 11
            [2] => 0
            [3] => 8
        )

    [game_inning] => Array
        (
            [0] => 9 inn
            [1] => 10 inn
            [2] => 1 inn
            [3] => 10 inn
        )

    [visitor] => Array
        (
            [0] => CARI
            [1] => LEON
            [2] => TIGR
            [3] => CARD
        )

    [visitor_annotation] => Array
        (
            [0] => 2
            [1] => 10
            [2] => 
            [3] => 10
        )

    [status] => Array
        (
            [0] => FIN
            [1] => FIN
            [2] => SUSP
            [3] => FIN
        )

)

and I need to build a sentence like this (taking as example position 0 of each sub arrays):

INSERT INTO tbl_games (teamA, teamA_annotation, teamB, teamB_annotation, game_date, game_time, game_place, game_status)
VALUES(`MAGA`,
       `3`,
       `9 inn`,
       `CARI`,
       `2`,
       NOW(),
       NULL,
       NULL,
       `FIN`);

How do I get for each sub array the right position? Notice that sentences to be built takes values from sub array and from the same position, should I use foreach or for? Any help on this?

Answer 1

You first need to build a simple array to handle for sql insert:

$youArrDefinition = array(..blah blah..);

$rowsToInsert = array();

$rowsToInsert = array();


foreach ( $youArrDefinition as $keyCol =>  $groupCols ) {
    $i = 0;
    foreach( $groupCols as $colVal ) {
        $rowsToInsert[$i][$keyCol] = $colVal;  
        $i++; 
   }

And that's should give you a better array to manipulate like:

array(
     array(
         'homeclub' => 'blah',
         'homeclub_annotation' => 'blah'
         // blah blah rest array
    ),
    array(
         'homeclub' => 'blah2',
         'homeclub_annotation' => 'blah2'
         // blah2 blah2 rest array2
    ),
    // rest of your array
);

Here is a DEMO