Generating extended MySQL inserts in PHP
Today I just realized I didn’t have any PHP code to generate extended MySQL inserts at my disposal. For heavy inserts I’ve been using BEGIN and COMMIT with lines terminated with ;.
The goal here is to generate an SQL string on the following form using a two dimensional input array containing the data:
INSERT INTO `tbl` (`field1`,`field2`) VALUES
(value1, 'value2'),
(value3, 'value4')Anyway, I decided to give it a shot and below is the result.
The arguments are as follows:
1.) $table is of course the database table we want to insert things into.
2.) $arr is the 2D array with data that we want to insert, my solution does not take responsibility for escaping any data, it assumes all that stuff has already been taken care of.
3.) $fields is optional and can contain the field names of the table in question, if it exists the arrays containing each row to insert are probably numerical or the keys don’t match the database column names (all data under all keys need to be imported though otherwise the map in #4 is needed). The fields need to match up with the corresponding data position. If for instance $fields[0] is ‘username’ then $arr[0][0] needs to contain the username value.
4.) $map can contain a map. This option is useful if for instance the 2D insert array contains rows with non-numerical keys which can be the case when for instance parsing a CSV. These non-numericals does sometimes not match the name of the corresponding database field, neither is it probable that all imported fields are to be inserted into the database. The contents of map needs to match the position of the fields in this case. If for instance $fields[0] is ‘username’ then $map[0] could be ‘Login_Name’, if the values in the Login_Name column in the CSV are to be inserted into the username column in the database.
5.) $chunk_size will control how many insterts we create in each go. This one can be needed in order to avoid too big SQL queries which the database can’t handle.
function insertTable($table, $rows, $fields = array(), $map = array(), $chunk_size = 1000){
$chunks = array_chunk($rows, $chunk_size);
foreach($chunks as $arr){
$fields = empty($fields) ? array_keys($arr[0]) : $fields;
if(!empty($map)){
$tmparr = array();
foreach($arr as $sub){
$tmp = array();
foreach($map as $from)
$tmp[] = $sub[$from];
$tmparr[] = $tmp;
}
$arr = $tmparr;
}
$fstr = "";
foreach($fields as $field)
$fstr .= "`$field`,";
$fstr = trim($fstr, ',');
$sql = "INSERT INTO `$table` ($fstr) VALUES ";
foreach($arr as $sub){
$sql .= "(";
$substr = "";
foreach($sub as $value)
$substr .= "'$value',";
$sql .= trim($substr, ',')."),";
}
$this->query(trim($sql, ','));
}
}There is not really much to add to the above except to note how the extra optional argument to trim() is used extensively to trim away superfluous commas at the ends in various places.