将多维php数组插入mysql数据库

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

我有一个来自csv的数组，其结构与此类似：

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.

我想将其插入到mysql表中，其中第一个数组（名称，年龄，性别）的项目是列标题，每个后续数组是表格中的一行。

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!

任何人都可以建议最好的方法来做到这一点，因为我撞墙了，它给我留下了一个伤脑筋！

7 个解决方案

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

有关演示，请参阅http://codepad.org/7SG7lHaH，但请注意我在codepad.org上删除了对mysql_real_escape_string（）的调用，因为它们不允许该函数。在您自己的代码中，您应该使用它。

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

#3

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.

您应该清理输入，我在我的示例中没有这样做。如果您的阵列结构不能保证相同，那么您将不得不做其他事情。

#4

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

由于mysql（扩展）会在insert上“强制转换”你的值，你不需要注意列类型：如果列定义为整数，在数据库中，它将作为整数插入，即使你引用价值（例如：年龄）。

Pay attention on the TODO i marked in the source: it is very unsafe to insert values, without escaping them (SQL injection).

注意源代码中标记的TODO：插入值是非常不安全的，而不是转义它们（SQL注入）。

#5

Assuming that the value in the array are TRUSTED and SECURE.

假设数组中的值是TRUSTED和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);
}

#6

My solution in 2 aproaches.

我的解决方案有2个方法。

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;
    }
}

#7

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;
    }

#1