一次将记录插入两个表 [英] Insert records into two table at once

问题描述

在我的网页中，有一个 CSV 导入部分.用户可以导入数千条记录.我需要将 csv 详细信息插入到 2 个表中.

In my web page, there is a CSV import section. Users may import several thousands of records. I need to insert the csv details into the 2 tables.

第一个表包含基本信息.第二个包含其他附加信息.所以我需要将第一个表的插入 ID 保存到第二个表中.

The first table contains the basic information. And the second one contains the other additional information. So that i need to save the first table's Inserted ID into the second table.

对于上面的需求，我写了2个mysql语句.但是导入需要更多时间.这里，是否可以使用单个查询将记录插入到 2 个表中?

For the above requirement, i wrote the 2 mysql statement. But it took more time to import. Here, is it possible to insert records into 2 table using single query?

请指教.

推荐答案

你不能同时插入两个表(因为你需要第一个的 id)但是你可以一个一个地插入第一个并且第二个的一个查询如下:

You cannot insert in two tables at once (because you need the id from the first one) but you can insert one-by-one for the first one and one single query for the second one as below:

$main_data = array('data1', 'data2', 'data3');
  $new_data = array();

    foreach($main_data as $data) {

      mysql_query("insert into table1 (data) values ('$data')");

      $new_id = mysql_insert_id();

      // Save the new id into an array + the data
      $new_data[$new_id] = $main;

    }

    $insert_into = array();

    // Create a new insert statement
    foreach($new_data as $new_key => $data) {
        $insert_into[] . "($new_key, '$data')"
    }

    $imploded_data = implode(',', $insert_into);

    if (count($insert_into) > 0) {

        // The result will be something like Insert into `table2` (id, value) values (1, 'data1'), (2, 'data2'),(3, 'data3');
        mysql_query("insert into `table2` (id, value) values $imploded_data");
    }

这篇关于一次将记录插入两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！