如何使用php将数据数组插入到mysql中?

Currently I have an Array that looks like the following when output thru print_r();

目前，我有一个数组，当输出到print_r()时，它看起来如下所示;

Array
(
    [0] => Array
        (
            [R_ID] => 32
            [email] => a@a.com
            [name] => Bob
        )

    [1] => Array
        (
            [R_ID] => 32
            [email] => b@b.com
            [name] => Dan
        )

    [2] => Array
        (
            [R_ID] => 32
            [email] => c@c.com
            [name] => Paul
        )

    [3] => Array
        (
            [R_ID] => 35
            [email] => d@d.com
            [name] => Mike
        )  
)

I would like to insert this data into one table with each element value belonging to its respective field.

我想把这些数据插入到一个表中，每个元素都属于各自的字段。

Currently my php code looks like the following

目前，我的php代码如下所示

if(is_array($EMailArr)){
    foreach($EMailArr as $R_ID => $email => $name){

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
    mysql_query($sql) or exit(mysql_error()); 
    }
}

*Note : R_ID is NOT the primary key in this table.*

*注:R_ID不是本表中的主键。*

Can someone help me understand how I should approach this situation? Thank you for reading and your help!

有人能帮我理解我应该如何处理这种情况吗?感谢您的阅读和帮助!

Regards.

的问候。

4 个解决方案

#1

I would avoid to do a query for each entry.

我将避免对每个条目执行查询。

if(is_array($EMailArr)){

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ";

    $valuesArr = array();
    foreach($EMailArr as $row){

        $R_ID = (int) $row['R_ID'];
        $email = mysql_real_escape_string( $row['email'] );
        $name = mysql_real_escape_string( $row['name'] );

        $valuesArr[] = "('$R_ID', '$email', '$name')";
    }

    $sql .= implode(',', $valuesArr);

    mysql_query($sql) or exit(mysql_error()); 
}

#2

First of all you should stop using mysql_*. MySQL supports multiple inserting like

首先，您应该停止使用mysql_*。MySQL支持多重插入

INSERT INTO example
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

You just have to build one string in your foreach loop which looks like that

你只需要在foreach循环中构建一个字符串就像这样

$values = "(100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1')";

and then insert it after the loop

然后在循环之后插入它。

$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES ".$values;

Another way would be Prepared Statements, which are even more suited for your situation.

另一种方法是准备好陈述，这更适合你的情况。

#3

if(is_array($EMailArr)){
    foreach($EMailArr as $key => $value){

    $R_ID = (int) $value['R_ID'];
    $email = mysql_real_escape_string( $value['email'] );
    $name = mysql_real_escape_string( $value['name'] );

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
    mysql_query($sql) or exit(mysql_error()); 
    }
}

A better example solution with PDO:

一个更好的PDO示例解决方案:

 $q = $sql->prepare("INSERT INTO `email_list` 
                     SET `R_ID` = ?, `EMAIL` = ?, `NAME` = ?");

 foreach($EMailArr as $value){
   $q ->execute( array( $value['R_ID'], $value['email'], $value['name'] ));
 }

#4

I've a PHP library which helps to insert array into MySQL Database. By using this you can create update and delete. Your array key value should be same as the table column value. Just using a single line code for the create operation

我有一个PHP库，可以帮助将数组插入到MySQL数据库中。通过使用它，您可以创建更新和删除。数组键值应该与表列值相同。只需要为create操作使用一行代码。

DB::create($db, 'YOUR_TABLE_NAME', $dataArray);

where $db is your Database connection.

$db是您的数据库连接。

Similarly, You can use this for update and delete. Select operation will be available soon. Github link to download : https://github.com/pairavanvvl/crud

类似地，您可以使用它进行更新和删除。选择操作将会很快出现。Github下载链接:https://github.com/pairavanvl/crud

#1