Inserting a multi-dimensional php array into a mysql database

Question

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?

Answer 1

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.

Answer 2

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