SQL INSERT INTO多个表 [英] SQL INSERT INTO multiple tables

问题描述

如何查询这两个查询?我将数据插入两个表中.

How can you make one query of this two?? I will insert data into two tables.

 $query = "
              INSERT INTO dc_mail_users (
                  i_id_pk, c_user, c_passwd_md5, i_user_active_id_fk, i_user_type_id_fk
              ) VALUES (
                  %1%, %2%, %3%, %4%, %5%
              )";

 $query2 = "
              INSERT INTO dc_mail_user_data (
                   i_id_ut, c_user_sex, c_user_name, c_user_surname, c_user_url
              ) VALUES (
                  %1%, %2%, %3%, %4%, %5%
              )";

推荐答案

这是什么目的?您是否要从一种HTML表单将数据插入到两个不同的表中?我不知道存储过程，但是我在类似的情况下使用事务:

What's the purpose of this? Are you trying to insert data into two different tables from one HTML form? I don't know about stored procedures but I use a transaction in similar case like this:

$d = dbSingle::dbLink();
//set autocommit to false
mysqli_autocommit($d->getDbc(), FALSE);

$query = " INSERT INTO dc_mail_users (
              i_id_pk, c_user, c_passwd_md5, i_user_active_id_fk, i_user_type_id_fk
          ) VALUES (
              %1%, %2%, %3%, %4%, %5%
          )";

$r = $d->sqlQ($query);

//get the last inserted id for the second query
$last_insert_id = $d->getInsertId();                    

$query2 = "
          INSERT INTO dc_mail_user_data (
               i_id_ut, c_user_sex, c_user_name, c_user_surname, c_user_url
          ) VALUES (
              %{$last_insert_id}%, %2%, %3%, %4%, %5% //not sure about the syntax, sorry
          )";


$r2 = $d->sqlQ($query2);

//rollback if either one of the queries failed
if (!$r || (isset($r2) && !$r2)) {
  mysqli_rollback($d->getDbc());
}
  else  {
    //commit if everything worked
    mysqli_commit($d->getDbc());

  //autocommit on
  mysqli_autocommit($d->getDbc(), TRUE);
  }

这假定表dc_mail_user_data中的i_id_ut是FK，而i_id_pk是自动递增字段.我有一个名为dbSingle的类，其中包含查询功能和数据库连接.希望它足够清晰，可以与常规mysqli函数一起使用.

This assumes i_id_ut in the table dc_mail_user_data is the FK and the i_id_pk is an auto increment field. I have a class called dbSingle that contains the query functions and database connection. Hope it's clear enough to be used with regular mysqli functions.

这篇关于SQL INSERT INTO多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！