Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I have an array from a csv with a similar structure to this:

Array (
    [0] => Array ( [0] => name [1] => age [2] => gender )
    [1] => Array ( [0] => Ian [1] => 24 [2] => male )
    [2] => Array ( [0] => Janice [1] => 21 [2] => female )
     etc

I would like to insert 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 as I have hit a wall and it has left me with a hurting head!

share|improve this question

6 Answers

up vote 2 down vote accepted

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.

share|improve this answer
 
@Jan_Henk, thanks very much I will give it a go –  chentudou Oct 14 '11 at 22:57
 
once you use implode to place these in the database, how can you explode them out to the original array that you had before? I am currently dealing with the same problem –  inspired Dec 2 '12 at 6:59
up vote 1 down vote 
$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....
share|improve this answer
 
one issue may be that the variables, $name $age $gender will be different each time and the number of them will be different so would I be able to get the variable name from the first array? something like count the elements in the first array then increment it. So the variable would be something like - $data[0][$x] ? –  chentudou Oct 12 '11 at 21:33
 
As long as the FIRST row in the array contains the fieldnames, and the 2nd, 3rd, etc.. rows have the values, then this function will handle any number of rows. up to PHP's memory limits and whatever point the query string grows to exceed mysql's max_allowed_packet limit. –  Marc B Oct 12 '11 at 21:37
 
@MarcB I think he's talking about different number of field, not row. –  Aurelio De Rosa Oct 12 '11 at 21:39
 
@Aurelio: ah. gotcha. in that case, no, this chunk of code only does 3 fields. –  Marc B Oct 12 '11 at 21:42
 
yes sorry, I may not have been clear. As I won't always know the number and title of fields. So I think Aurelio's code will do that. Thanks –  chentudou Oct 12 '11 at 21:49
show 2 more comments
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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);
}
share|improve this answer
 
quoting the fieldnames turns them into strings and they won't be treated as field names anymore. Otherwise, looks good for the most part. –  Marc B Oct 12 '11 at 21:31
 
do you know why I may be getting an 'invalid arguments passed' warning with this code on implode()? –  chentudou Oct 12 '11 at 21:58
 
@chentudou Are you sure you copied good the code? I tried it before I post and works good. Besides, as you can see, this code will work also if there are more (or less) then 3 field. –  Aurelio De Rosa Oct 12 '11 at 22:00
 
i tried again... the implode warning has gone but it says there is an error in the SQL syntax. I will keep trying, may be my mistake. –  chentudou Oct 12 '11 at 22:08
 
@AurelioDeRosa: You swapped the parameters for implode. Edited. –  hakre Oct 12 '11 at 22:09
show 6 more comments
up vote 0 down vote

My solution in 2 aproaches.

  1. Save the array values as serialized representations of the data in a simple DB table.

  2. 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;
    }
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.