Algorithm to dynamically merge arrays

Question

I'm trying to create a INSERT statement for every row in a PHPExcel object. As I've struggled to iterate across the column (ie. go B1 C1 D1, get the values, and put them into an array), I've opted to get all the values for each column and put them into a multi-dimensional array that looks like this:

Array
(
    [foo] => Array
        (
            [0] => 250
            [1] => 247
            [2] => 279
            [3] => 249
        )

    [bar] => Array
        (
            [0] => AM PROV
            [1] => AM PROV
            [2] => AM PENS
            [3] => AM PROV
        )

    [schoo] => Array
        (
            [0] => xxxx
            [1] => yyy
            [2] => zzz
            [3] => aaa
        )
)

I want to merge each of the arrays so that all the data at index 0 is in one array, etc. I've built a generic tool to allow you to select the columns you want from an uploaded spreadsheet. It needs to first merge the column data into a single array and then it should generate INSERT statements for each of the arrays. So the final deliverable is this:

INSERT INTO (foo, bar, schoo) VALUES (250, "AM PROV", "xxxx");

All help appreciated.

UPDATE: Hey all, thank you very much for your answers. I finally managed to get it working using row and cell iterators as per Mark's suggestion and it's working. I have a separate issue with it now, but I think it's something I can solve. Thanks again.

Answer 1

Not sure if this is what you were after but...

<?php
# Given this array
$arrays = array(
    'foo' => array(
        0 => 250, 
        1 => 247, 
        2 => 279, 
        3 => 249
    ), 
    'bar' => array(
        0 => 'AM PROV', 
        1 => 'AM PROV', 
        2 => 'AM PENS', 
        3 => 'AM PROV'
    ), 
    'schoo' => array(
        0 => 'xxxx', 
        1 => 'yyy', 
        2 => 'zzz', 
        3 => 'aaa'
    )
);

# This code generates...
$fields = array();
$inserts = array();

foreach ($arrays as $k => $v) {
    $fields[] = $k;
}

for ($i = 0; $i < count($arrays[$fields[0]]); $i++) {
    $vals = array();

    foreach ($fields as $field) {
        $vals[] = $arrays[$field][$i];
    }

    $inserts[] = 'INSERT INTO (' . implode(',', $fields) . ') VALUES ("' . implode('","', $vals) . '")';
}

# This array
/* $inserts = array(
    'INSERT INTO (foo, bar, schoo) VALUES ("250", "AM PROV", "xxxx")', 
    'INSERT INTO (foo, bar, schoo) VALUES ("247", "AM PROV", "yyy")', 
    'INSERT INTO (foo, bar, schoo) VALUES ("279", "AM PENS", "zzz")', 
    'INSERT INTO (foo, bar, schoo) VALUES ("249", "AM PROV", "aaa")'
); */

var_dump($inserts);

Edit: Though I think you're missing a table name from your INSERT statements.

Edit2: You could shorten the code using array_keys like Frosty Z does and skip the first foreach.

Answer 2

$inputArray = array('a' => array(1, 2, 3), 'b' => array("X'", 'Y', 'Z'));
$finalArray = array();

// build array with appropriate data rows

$finalIndex = 0;

foreach($inputArray as $key => $row)
{
  foreach($row as $value)
    $finalArray[$finalIndex][] = $value;

  $finalIndex++;
}

// format it as SQL insert queries

$fields = array_keys($inputArray);

foreach($finalArray as $row)
{
  echo "INSERT INTO table (".implode(", ", $fields).") "
    . " VALUES (".implode(", ", array_map("format_data", $row)).");\n";
}

function format_data($value)
{
  // assuming you're using MySQL. Replace the escape function by
  // the appropriate one
  if (is_string($value))
    return "'".mysql_real_escape_string($value)."'";
  else
    return $value;
}

Answer 3

<?php
$uberArray = array(
    "foo" => array(
        0 => 250,
        1 => 247,
        2 => 279,
        3 => 249,
    ),
    "bar" => array(
        0 => "AM PROV",
        1 => "AM PROV",
        2 => "AM PENS",
        3 => "AM PROV",
    ),
    "schoo" => array(
        0 =>  "xxxx",
        1 =>  "yyy",
        2 =>  "zzz",
        3 =>  "aaa",
    )
);

$yourMysqlLink = mysql_connect('localhost', 'user', 'pass');
mysql_query('SET NAMES utf8'); // Adjust according to your encoding

$colNames = array_keys($uberArray);
$stringCols = array('bar', 'schoo');
$sqlInsertStr = 'INSERT INTO `your_table` (`'.implode('`, `', $colNames)."`) VALUES \n";

$rows = array();
// Not really for iterating the first array, we just need a loop
foreach ($uberArray[$colNames[0]] as $k => $v) {
    $vals = array();
    foreach ($colNames as $v2) {
        $val = $uberArray[$v2][$k];
        if (in_array($v2, $stringCols)) {
            $val = "'".mysql_real_escape_string($val, $yourMysqlLink)."'";
        }
        $vals[] = $val;
    }
    $rows[] = "\t(".implode(', ', $vals).")";
}
$sqlInsertStr .= implode(",\n", $rows).';';

echo '<pre style="clear:both;">'.$sqlInsertStr.'</pre>'; ;

Note that you may need to do a few adjustments for performance reasons, if $uberArray is big (e.g. splitting the insert string into chunks). Or you can convert the data to CSV and use MySQL LOAD DATA INFILE method, which is real fast.

Answer 4

You can use one of those strange spl iterators for this :)

$iter = new MultipleIterator(MultipleIterator::MIT_KEYS_ASSOC);
foreach ($uberArray as $colName => $colValues) {
    $iter->attachIterator(new ArrayIterator($colValues), $colName);
}

foreach ($iter as $vals) {
    print_r($vals);
    //or $pdoStmt->execute($vals);
}

But really, a simple for loop is the tool to use here.

Answer 5

I see no reason to merge the array unless you feel like wasting memory. You've already probably made a copy of the data. This just inserts the data row by row.

$data = array('foo' => array(...), ... );
$fields = array('foo', 'bar', 'schoo');
$c = count($data[$fields[0]));

$base_sql = 'INSERT INTO tbl ('.implode(',', $fields).') VALUES ';

for ($i = 0; $i < $c; ++$i)
{
  $row_data = array();
  foreach ($fields as $field)
     $row_data[] = "'".escape_func($data[$field][$i])."'";

  $sql = $base_sql . '(' . implode(',', $row_data). ')';
  db_query($sql);
}

I would actually use prepared statements.

And you really should try to figure out how to iterate through the original dataset in one pass.