将一个多维的PHP数组插入到MySQL数据库中

Question

我有一个来自csv的数组，结构类似于这样：

$array = array(
    array('name', 'age', 'gender'),
    array('Ian', 24, 'male'),
    array('Janice', 21, 'female')
);

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

请问有什么最好的方法来做到这一点？

- chentudou

Answer 1

下面的代码可以工作，但它假设所有嵌套数组的长度都相同，换句话说，每个嵌套数组都包含了在第一个嵌套数组中定义的所有属性的值。

$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) . ")";

只要所有其他嵌套数组的长度相同，此解决方案就可以处理在第一个嵌套数组中定义的任意数量的属性。对于上面的数组，输出将是：

INSERT INTO table_name (name, age, gender) VALUES (Ian, 24, male), (Janice, 21, female)

可以参考http://codepad.org/7SG7lHaH进行演示，但请注意我在codepad.org上删除了对mysql_real_escape_string()函数的调用，因为他们不允许使用该函数。在您自己的代码中，应该使用它。

- Jan-Henk

---

一旦您使用implode将它们放入数据库中，如何将它们分解回之前的原始数组？我目前正在处理同样的问题。 - Abdullah Rasheed

未知字段 'Ian' 在字段列表中。很抱歉，您的代码无法正常工作。 - Ashish Pathak

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());

这应该生成一个类似于查询字符串的东西：

INSERT INTO yourtable (name, age, gender) VALUES ('Ian', 24, 'male'), ('Janice', 21, 'female'), etc....

- Marc B

---

一个问题可能是变量 $name $age $gender 每次都会不同，它们的数量也会不同，所以我能否从第一个数组中获取变量名呢？比如计算第一个数组中的元素数量，然后递增它。因此，变量将类似于 - $data[0][$x]？ - chentudou

@MarcB 我想他在谈论的是不同字段数量，而不是行数。 - Aurelio De Rosa

@Aurelio：啊，我明白了。既然这样，不，这段代码只处理3个字段。 - Marc B

是的，抱歉，我可能没有表达清楚。因为我不总能知道字段的数量和标题，所以我认为奥里利奥的代码可以解决这个问题。谢谢 - chentudou

@chentudou：只要小心字符串值。其中任何一个引号都会破坏生成的查询。这就是为什么我硬编码了3个值并使用了mysql_real_escape_string。你确实可能会对自己进行SQL注入攻击。 - Marc B

Answer 3

假设数组中的值是“可信赖的”和“安全的”。

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

- Aurelio De Rosa

---

引用字段名会将它们转换为字符串，它们不再被视为字段名。除此之外，大部分内容看起来都很好。 - Marc B

你知道为什么我在使用implode()函数时会收到“无效参数传递”警告吗？ - chentudou

@AurelioDeRosa：你颠倒了implode的参数。已编辑。 - hakre

@chentudou 你可以在这里检查代码是否有效（你也可以复制源代码）：http://codepad.org/CuRp4pzC - Aurelio De Rosa

是的，正确的，这有历史原因，但最好把分隔符放在第一位，参见 http://php.net/manual/en/function.implode.php ;) - hakre

Answer 4

对于这个数组，你可以做一些简单的事情，比如这样：

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

你应该对输入进行清洗，而我在示例中没有这样做。如果你的数组结构不能保证相同，那么你就需要做一些其他的处理。

- Aaron Bruce

Answer 5

你可以这样做：

$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) . ')';

由于mysql(extension)在插入时会对您的值进行“类型转换”，因此您不需要关注列类型：如果数据库中定义了列为整数，则无论您是否引用该值（例如：年龄），它都将被插入为整数。

请注意我在源代码中标记的TODO：在未转义值的情况下插入值非常不安全（SQL注入）。

- aurora

Answer 6

我的解决方案有两种方法。

1. 将数组值保存为简单数据库表中数据的序列化表示。

2. 将数组值保存在单独的表字段中。

工作示例：

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

- Hans Kerkhof

Answer 7

将多维数组插入SQL的最直接方式可能是：

$queryIn = "INSERT INTO table (resultsArray) values (" . print_r($multiArray, true) . "')";

那个可以。

- Rafa

Answer 8

数组

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

- Ashish Pathak

Answer 9

做这种事情的最佳方式是使用PDO库的预处理语句和事务。在PHP中，PDO是任何数据库交互的最简单选择。

预处理语句快速、简单，并且提供参数绑定，这将作为副作用保护您免受SQL注入攻击。

数据库事务确保所有数据都被插入或全部不插入。它们还可以显著提高此类任务的性能。

以下是一个示例代码：

// 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();

关于标题行怎么处理？

如上面的代码所示，我正在丢弃标题行。由于SQL表是固定的，我需要事先知道列的名称。如果您想将CSV中的列与模式中的列动态匹配，则需要添加一些逻辑，该逻辑将查看硬编码的列列表（您还可以从information_schema中获取此信息），并将其与来自CSV的列的名称进行匹配。

可以调整代码如下。迭代CSV中的列名，并在您的代码中未定义任何列时抛出异常。如果所有列都经过验证，则implode()列名并将其注入到SQL字符串中。

$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();

- Dharman