I have an array from a csv with a similar structure to this:
$array = array(
array('name', 'age', 'gender'),
array('Ian', 24, 'male'),
array('Janice', 21, 'female')
);
I would like to insert it into a MySQL table where the items of the first array (name, age, gender) are the column titles and each subsequent array is a row in the table.
Could anyone advise as to the best way to do this?
The following code will work, but it assumes that the length of all nested arrays is the same, in other words that each nested array contains values for all the attributes defined in the first nested array.
$array = array(
array('name', 'age', 'gender' ),
array('Ian', 24, 'male'),
array('Janice', 21, 'female')
);
$fields = implode(', ', array_shift($array));
$values = array();
foreach ($array as $rowValues) {
foreach ($rowValues as $key => $rowValue) {
$rowValues[$key] = mysql_real_escape_string($rowValues[$key]);
}
$values[] = "(" . implode(', ', $rowValues) . ")";
}
$query = "INSERT INTO table_name ($fields) VALUES (" . implode (', ', $values) . ")";
This solution will work with any number of attributes defined in the first nested array, as long as all other nested arrays have the same length. For the array above the output will be:
INSERT INTO table_name (name, age, gender) VALUES (Ian, 24, male), (Janice, 21, female)
For a demonstration see http://codepad.org/7SG7lHaH, but note that I removed the call to mysql_real_escape_string() on codepad.org, because they do not allow the function. In your own code you should use it.
$fields = implode(',', array_shift($array)); // take the field names off the start of the array
$data = array()
foreach($array as $row) {
$name = mysql_real_escape_string($row[0]);
$age = (int) $row[1];
$gender = mysql_real_escape_string($row[2]);
$data[] = "('$name', $age, '$gender')";
}
$values = implode(',', $data);
$sql = "INSERT INTO yourtable ($fields) VALUES $values";
$result = mysql_query($sql) or die(mysql_error());
That should produce a query string like:
INSERT INTO yourtable (name, age, gender) VALUES ('Ian', 24, 'male'), ('Janice', 21, 'female'), etc....
Array
$arrayData = array(
array(
'name' => 'Paul',
'age' => 28,
'gender' => 'male',
),
array(
'name' => 'Rob',
'age' => 23,
'gender' => 'male',
)
);
foreach($arrayData as $data){
$query = "INSERT INTO persons (name,gender,age)
VALUES ('$data[name]', '$data[gender]', $data[age])";
//echo $query;die;
mysql_query($query) or die(mysql_error());
//exit;
}
The best way to do this kind of thing is by using prepared statements and transactions with the help of PDO library. PDO is the simplest choice for any DB interaction in PHP.
Prepared statements are fast, easy and they offer parameter binding which will protect you from SQL injection as a side effect.
DB transactions ensure that all the data is inserted or none at all. They can also significantly improve the performance of such kind of task.
Here is an example code:
// create new connection to MySQL using PDO class
$pdo = new \PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_EMULATE_PREPARES => false
]);
$array = array(
array('name', 'age', 'gender'),
array('Ian', 24, 'male'),
array('Janice', 21, 'female')
);
// discard the header row (1st row) from the array
array_shift($array);
// start transaction
$pdo->beginTransaction();
// prepre SQL statemenet with 3 placeholders
$stmt = $pdo->prepare('INSERT INTO csv_table(name, age, gender) VALUES(?,?,?)');
foreach ($array as $row) {
$stmt->execute($row);
}
// end transaction
$pdo->commit();
As you can see in the code above I am discarding the header row. Since SQL tables are rigid I need to know beforehand what are the names of columns. If you want to match columns in the CSV to the columns in your schema dynamically then you need to add some logic which will look at a hardcoded list of columns (you can also fetch this information from information_schema) and match it with the names of columns from your CSV.
The code can be adjusted as follows. Iterate over the column names from CSV and throw an exception if any of the columns are not defined in your code. If all columns are verified then implode() the column names and inject that into your SQL string.
$array = array(
array('name', 'age', 'gender'),
array('Ian', 24, 'male'),
array('Janice', 21, 'female')
);
$table_columns = ['age', 'gender', 'name'];
// Validate CSV header row (1st row) against your schema columns
$header = array_shift($array);
foreach ($header as $col_name) {
if (!in_array($col_name, $table_columns, true)) {
throw new \Exception('Incorrect column name specified in CSV file');
}
}
// start transaction
$pdo->beginTransaction();
// prepre SQL statemenet with 3 placeholders
$stmt = $pdo->prepare('INSERT INTO csv_table('.implode(',', $header).') VALUES(?,?,?)');
foreach ($array as $row) {
$stmt->execute($row);
}
// end transaction
$pdo->commit();
Most direct way to insert a multi array to SQL, could be:
$queryIn = "INSERT INTO table (resultsArray) values (" . print_r($multiArray, true) . "')";
That works.
Assuming that the value in the array are TRUSTED and SECURE.
$count = count($array);
$keys = $array[0];
for($i = 1; $i < $count; $i++)
{
$query = "INSERT INTO tablename (" . implode(",", $keys) . ") VALUES ('" . implode ("','", $array[$i]) . "');";
$query = str_replace(',)', ')', $query);
mysql_query($query);
}
for this array you could do something as simple as this:
$array = csv_array(); // this is your array from csv
$col_name = $array[0][0];
$col_age = $array[0][1];
$col_gender = $array[0][2];
for($i = 1; $i < count($array); $i++){
//this is where your sql goes
$sql = "INSERT INTO `table` ($col_name, $col_age, $col_gender)
VALUES($array[$i][0], $array[$i][1], $array[$i][2])";
$db->query($sql);
}
You should sanitize input, which I didn't do in my example. If your array structure isn't guaranteed to be the same then you'll have to do something else.
You could do it this way:
$rows = array(
array('name', 'age', 'gender'),
array('Ian', 24, 'male'),
array('Janice', 21, 'female')
);
$columns = array_shift($rows);
$rows = array_map(function($row) {
/*
* TODO: escape column values
*/
return '"' . implode('", "', $row) . '"';
}, $rows);
$sql = 'INSERT INTO ...
(' . implode(', ', $columns) . ')
VALUES (' . implode('), (', $rows) . ')';
As mysql (extension) will "cast" your values on insert, you don't need to pay attention of the column types: if the column is defined as integer, in the database, it will be inserted as integer, even if you quote the value (for example: the age).
Pay attention on the TODO i marked in the source: it is very unsafe to insert values, without escaping them (SQL injection).
My solution in 2 aproaches.
Save the array values as serialized representations of the data in a simple DB table.
Save the array values in separate table fields.
Working example:
$array = array(
0 => array ( "name", "age", "gender"),
1 => array ( "Ian", "24", "male"),
2 => array ( "Janice", "21", "female")
);
foreach($array as $key1 => $value1){
foreach($value1 as $key2 => $value2){
// assuming the first element (0) in the array is the header value and the header value is a valid array key
if($key1 > 0){
$items[$key1-1][ $array[0][$key2] ] = $value2;
}
}
}
// 1. store values as serialized representation
foreach ($items as $key => $value) {
$sql = "INSERT INTO datatable SET data = ".mysql_real_escape_string(serialize($value))."";
echo $sql.PHP_EOL;
}
// 2. auto create fields in database and store values
foreach ($array[0] as $key1) {
$sql = "ALTER TABLE forms ADD '".$key1."' TEXT NOT NULL";
echo $sql.PHP_EOL;
}
foreach ($items as $key1 => $value1) {
foreach($value1 as $key2 => $value2){
$sql = "INSERT INTO datatable SET ".$key2." = '".mysql_real_escape_string($value2)."'";
echo $sql.PHP_EOL;
}
}
