插入并更新到多个表中，MySQL [英] Insert and update into multiple tables, MySQL

问题描述

我有两个桌子.我想在提交表单时使用parentcreate.php作为操作，并使用该文件在第一个表中插入几个变量，并同时使用其中一个变量更新另一个表.我已经在线搜索过，但发现最接近的是插入和更新单个表，而不是两个不同的表.

I've got two tables. I want to, when submitting a form, use parentcreate.php as action and with that file insert a couple of variables into the first table and at the same time update the other table with one of the variables. I've searched online but the closest thing I found was inserting and updating a single table, not two different.

谢谢.

更新:

这是我到目前为止获得的代码，如您所见，它只是插入到第一个表中.

Here's the code I've got so far, as you can see it's just inserting into the first table.

session_name('knine_settings_login');
    session_set_cookie_params(1*1*1*15*60);
    session_start();

    mysql_connect('xxxx', 'xxxx', 'xxxx') or die(mysql_error());
    mysql_select_db("xxxx") or die(mysql_error());

    $ClassIDOne = mysql_real_escape_string($_POST["cidone"]);
    $ClassIDTwo = mysql_real_escape_string($_POST["cidtwo"]);
    $ClassIDThree = mysql_real_escape_string($_POST["cidthree"]);
    $ClassIDFour = mysql_real_escape_string($_POST["cidfour"]);
    $ClassIDFive = mysql_real_escape_string($_POST["cidfive"]);
    $usr = $_SESSION["usr"];

    mysql_query("SET NAMES 'utf8'") or die(mysql_error()); 
    mysql_query("SET CHARACTER SET 'utf8'") or die(mysql_error()); 

    $query="INSERT INTO knine_parent_db
            SET usr = '$usr', ClassIDOne = '$ClassIDOne', ClassIDTwo = '$ClassIDTwo', ClassIDThree = '$ClassIDThree', ClassIDFour = '$ClassIDFour', ClassIDFive = '$ClassIDFive'";  

我基本上想同时执行这两个查询:

I basically want to execute both these queries at the same time:

$query="INSERT INTO knine_parent_db
            SET usr = '$usr', ClassIDOne = '$ClassIDOne', ClassIDTwo = '$ClassIDTwo', ClassIDThree = '$ClassIDThree', ClassIDFour = '$ClassIDFour', ClassIDFive = '$ClassIDFive'";

    $query="UPDATE knine_settings_login
            SET ClassID = '$usr' WHERE usr ='$usr'";  

推荐答案

您应该使用PDO，并阅读PHP手册中的以下主题:此一个和这一个也

You should use PDO, and read this topic in PHP manual : Link to PDO::beginTransaction and this one and this one too

您应该开始新的交易，然后进行请求，检查是否没有错误，然后提交！

You should begin a new transaction, then do your requests, check if there was no error, then commit !

使用PDO :: commit后，查询将返回到自动提交模式，然后必须再次使用PDO :: beginTransaction来设置自动提交功能，并在单个事务中进行多个查询.

Once you use PDO::commit, the queries are backed to auto-commit mode, then you have to use again PDO::beginTransaction to set auto-commit off and do multiple queries into a single transaction.

该如何在一个事务中处理多个请求.

That how to do multiple requests in one transaction.

对于您而言，有一些代码可以帮助您:

In your case, there is some code to help you :

$query1 = "INSERT INTO knine_parent_db
          SET usr = :usr,
              ClassIDOne = :ClassIDOne,
              ClassIDTwo = :ClassIDTwo,
              ClassIDThree = :ClassIDThree,
              ClassIDFour = :ClassIDFour,
              ClassIDFive = :ClassIDFive
          ";

$exec1 = [
    ':usr' => $_SESSION["usr"],
    ':ClassIDOne' => $_POST["cidone"],
    ':ClassIDTwo' => $_POST["cidtwo"],
    ':ClassIDThree' => $_POST["cidthree"],
    ':ClassIDFour' => $_POST["cidfour"],
    ':ClassIDFive' => $_POST["cidfive"],
];

$query2 = "UPDATE knine_settings_login
           SET ClassID = :usr
           WHERE usr = :usr
          ";

$exec2 = [':usr' => $_SESSION["usr"]];

$db_host = 'localhost';
$db_name = 'MyDatabase';
$db_user = 'user';
$db_pass = 'password';

$dsn = 'mysql:host='.$db_host.';dbname='.$db_name.';charset=utf8';

try
{
    $PDO = new PDO($dsn, $db_user, $db_pass);
}
catch(PDOException $e)
{
    // if dev mode
    echo 'Database connection error : ' . $e->getMessage();

    // if prod mode
    //header('Location: /404.html');
    //exit;
}

// begin of transaction
$PDO->beginTransaction();

$res1 = $PDO->prepare($query1);
$res2 = $PDO->prepare($query2);

if($res1->execute($exec1) && $res2->execute($exec2))
{
    $PDO->commit();
}
else
{
    $PDO->rollBack();
}

// end of transaction

这篇关于插入并更新到多个表中，MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！