Insert and update into multiple tables, MySQL
I have two tables. I want to be able to use parentcreate.php as an action when submitting a form and with that file insert a couple of variables into the first table and at the same time update another table with one of the variables. I searched the web, but the closest I found was inserting and updating one table, not two different ones.
Thanks in advance.
Update:
Here is the code I got so far as you can see it by simply pasting 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 of 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'";
source to share
You should be using PDO and read this thread in the PHP manual: Link to PDO :: beginTransaction and this and this
You have to start a new transaction, then execute your requests, check for an error, and then commit!
Once you use PDO :: commit, the queries are maintained in auto-commit mode, then you need to use PDO :: beginTransaction again to set up auto-commit and execute multiple queries in one transaction.
How to execute multiple queries 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
source to share