dousikuai5417
2016-10-02 15:14
浏览 187
已采纳

用PHP将数组插入MySQL数据库?

Here's my current code:

// connect to database  
$con = mysqli_connect("localhost","username","password","database");
if (!$con) {
    die('Could not connect: ' . mysqli_error());
}

// get the json file
$jsondata = file_get_contents('http://www.example.com');

// convert json to php array
$data = json_decode($jsondata, true);

// assign each of the variables
$id = $data['0']['id'];
$name = $data['0']['name'];
$status = $data['0']['status'];

$insertstatement = mysqli_query($con,"INSERT INTO `table` (`id`, `name`, `status`) VALUES ('".$id."', '".$name."', '".$status."');");

Technically this code is working, but it's only adding the first item.

For example, here's the original json that's being imported:

[
   {
      "id":"19839",
      "status":"active",
      "name":"First Name",
   },
   {
      "id":"19840",
      "status":"active",
      "name":"Second Name",
   },
   {
      "id":"19841",
      "status":"active",
      "name":"Another Name",
   },
   {
      "id":"19842",
      "status":"active",
      "name":"Last Name",
   }
]

My code would only be inserting this into the database:

{
 "id":"19839",
 "status":"active",
 "name":"First Name",
}

How do I make it loop through all of them and insert all of the rows? Also is there, a way to insert them in reverse order (starting from the last one, ending at the first one)?

Any help would be greatly appreciated :)

图片转代码服务由CSDN问答提供 功能建议

这是我当前的代码:

  //连接数据库 
 $ con = mysqli_connect(“localhost”,“username”,“password”,“database”); 
if(!$ con){
 die('无法连接:'。mysqli_error()); 
  } 
 
 //获取json文件
 $ jsondata = file_get_contents('http://www.example.com'); 
 
 //将json转换为php数组
 $ data = json_decode($  jsondata,true); 
 
 //分配每个变量
 $ id = $ data ['0'] ['id']; 
 $ name = $ data ['0'] ['name'  ]; 
 $ status = $ data ['0'] ['status']; 
 
 $ insertstatement = mysqli_query($ con,“INSERT INTO`table`(`id`,`name`,`status`  )VALUES('“。$ id。”','“。$ name。”','“。$ status。”');“); 
   
 
 

从技术上讲,这段代码正在运行,但它只是添加了第一项。

例如,这里是导入的原始json:

  [
 {
“id”:“19839”,
“status”:“active”,
“name”:“First Name”,
},
 {
“id”:“  19840“,
”status“:”active“,
”na  me“:”Second Name“,
},
 {
”id“:”19841“,
”status“:”active“,
”name“:”Another Name“,
},  
 {
“id”:“19842”,
“status”:“active”,
“name”:“姓氏”,
} 
] 
    
 
 

我的代码只会将其插入数据库:

  {
“id”:“19839”,
“status”:“  active“,
”name“:”First Name“,
} 
   
 
 

如何让它循环遍历所有行并插入所有行 ? 还有,一种方法以相反的顺序插入它们(从最后一个开始,到第一个结束)?

任何帮助将不胜感激:) \ n

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • doubi12138 2016-10-02 15:39
    已采纳
    • To iterate over array you have to use foreach operator.
    • To perform multiple inserts you have to use prepared statements

    So despite what in all other hastily written answers said, the code should be

    $stmt = $con->prepare("INSERT INTO `table` (`id`, `name`, `status`) VALUES (?,?,?)");
    $stmt->bind_param("sss", $id, $name, $status);
    foreach ($data as $row)
    {
        $id = $row['id'];
        $name = $row['name'];
        $status = $row['status'];
        $stmt->execute();
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dtiu94034 2016-10-02 15:23

    With a foreach loop:

    foreach($data as $index => $row){
        $insertstatement = mysqli_query($con,"INSERT INTO `table` (`id`, `name`, `status`) VALUES ('". $row['id'] ."', '". $row['name'] ."', '". $row['status'] ."');");
    }
    

    However please note that you should be careful when inserting any data from untrusted sources, since it leaves you vulnerable to SQL injections. Learn read more about SQL injection here: How can I prevent SQL injection in PHP?.

    评论
    解决 无用
    打赏 举报
  • dpn517111 2016-10-02 15:30

    You should use the MySql multiple row inserts.

    INSERT INTO tbl_name (col1,col2) VALUES(1,2), (3,4);
    

    MySql insert reference: http://dev.mysql.com/doc/refman/5.7/en/insert.html

    In php, you would do something like (modified your code)

    // connect to database  
    $con = mysqli_connect("localhost","username","password","database");
    if (!$con) {
        die('Could not connect: ' . mysqli_error());
    }
    
    // get the json file
    $jsondata = file_get_contents('http://www.example.com');
    
    // convert json to php array
    $data = json_decode($jsondata, true);
    
    $query = "INSERT INTO `table` (`id`, `name`, `status`) VALUES ";
    
    foreach($data as $d) {
    $query .= "('".$d['id']."', '".$d['name']."', '".$d['status']."'),";
    }
    // Query. Note the trim to remove the last ,
    $insertstatement = mysqli_query($con, trim($query,',');
    

    You can also continue reading from ON DUPLICATE KEY... & other useful additions to the above. You should also make sure your data is correctly escaped / from a 100% safe source. If status contains something like "'; TRUNCATE TABLE..." unescaped, you're potentially in trouble.

    EDIT: The two other solutions also work and insert all rows. My version however inserts all rows in one query. Depending on how large your data is, it might not be feasible to run - say - 1000 queries instead of one.

    评论
    解决 无用
    打赏 举报
  • douzuizhuo0587 2016-10-02 17:44

    In order to insert multiple rows of data or array of the data you have to iterate upon the array variable using the foreach() so that it will loop you by single row so that you can perform the needed operation that you want to perform.

    You can insert the data using mysqli.* orPDO or by using prepared statements. And it is up to you who can decide of under which scenario you are going to insert the data.

    MYSQLI:

    <?php
    $data = json_decode($jsondata, true);
    foreach($data as $single_data)
    {
    $insertstatement = mysqli_query($con,"INSERT INTO `table` (`id`, `name`, `status`) VALUES ('".$single_data['id']."', '".$single_data['name']."', '".$single_data['status']."');");
    }
    ?>
    

    Prepared Statements:

    <?php
    $query = $con->prepare("INSERT INTO `table` (`id`, `name`, `status`) VALUES (?,?,?)");
    $query->bind_param("sss", $id, $name, $status);
    foreach ($data as $row)
    {
        $id = $row['id'];
        $name = $row['name'];
        $status = $row['status'];
        $result->execute();// This will execute the statement. 
    }
    ?>
    

    PDO with Prepared Statements:

    <?php
    $link = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbusername,$dbpassword);
    $statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
        VALUES(:id, :name, :status)");
    foreach($data as $row)
    {
    $statement->execute(array(
        "id" => $row['id'],
        "name" => $row['name'],
        "status" => $row['status']
    ));
    }
    ?>
    

    Hope so my explanations would be clear for better understanding of all the three type of statements.

    评论
    解决 无用
    打赏 举报