mysql insert select 涉及到多个表,插入并更新到多个表中，MySQL

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.

Thanks in advance.

Update:

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'";

解决方案

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 !

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