Array dynamic merge algorithm

Question

I am trying to create an INSERT statement for each row in a PHPExcel object. Since I struggled to iterate over the column (i.e., Go to B1 C1 D1, get the values ​​and put them in an array), I decided to get all the values ​​for each column and put them in a multidimensional 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 combine each of the arrays so that all data with index 0 are in the same array, etc. I created a general tool that allows you to select the columns that you want to retrieve from a downloaded spreadsheet. It must first combine the column data into a single array, and then it must generate INSERT statements for each of the arrays. Thus, the end result is as follows:

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

All help was appreciated.

UPDATE: Hello everyone, thank you very much for your answers. Finally, I was able to get it to work using row and cell iterators as per Mark's suggestion, and it works. Now I have a separate problem, but I think that is what I can solve. Thanks again.

Answer 1

 <?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>'; ; 

Please note that to improve performance, you may need several adjustments if $uberArray large (e.g. splitting an insert string into pieces). Or you can convert the data to CSV and use the MySQL LOAD DATA INFILE method, which is very fast.

Answer 2

Not sure if this is what you were up to, 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: Although I think you are missing the table name from your INSERT statements.

Edit2: you can shorten the code with array_keys like Frosty Z, and skip the first foreach file.

Answer 3

 $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 4

You can use one of these weird iterators to do 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 actually a simple tool for the loop is the tool to use here.

Answer 5

I see no reason to join an array if you do not want to lose memory. You have probably already made a copy of the data. It just inserts 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 really use prepared statements.

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