Insert and update into multiple tables, MySQL

Question

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

Show some code effort what you tried? it's not a code giving platform? — Death-is-the-real-truth
– Death-is-the-real-truth, Commented May 3, 2015 at 18:40
If you have found something for inserting a single table, you can use it twice to insert into two — Jonny C
– Jonny C, Commented May 3, 2015 at 18:43
Please explain more what to you want to update in in second table and what is the problem you are facing. As I can understand from these detail is that may be you are inserting value and want to update last inserted row id of table one in table2 if it is that then you can use mysql_last_insert_id() function of php — Varun Naharia
– Varun Naharia, Commented May 3, 2015 at 18:55
I'm saving the user details in "knine_settings_login" and five different id's for each user in "knine_parent_db". But that's only for users with user level 4. Therefore I can't keep them in the same table, I've got 3 other user levels with other content in the user db apart from the ClassID. — Axel
– Axel, Commented May 3, 2015 at 18:59

Bobot · Accepted Answer · 2015-05-03 19:26:43Z

1

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

edited May 3, 2015 at 19:26

answered May 3, 2015 at 19:05

Bobot

1,1188 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Axel Over a year ago

Thanks! I'll try it as soon as possible!

Bobot Over a year ago

@9focuspoints I added some example code to help you some more, that's dirty, but enough to give you an idea ;) By the way, with PDO->prepare(), you don't have to secure your data with mysql_real_escape_string(), PDO does it for you :D (read the php manual about it )

Reza Saadati · Accepted Answer · 2019-04-25 04:24:51Z

Try and separate the statements this worked for me

//  insert query execution
$sql = "INSERT INTO vendors (vendorId ,vendor_name, address, email, phone, category) VALUES
('$vendorId', '$vendor_name', '$address', '$email',
 '$phone', '$category')
";

if ($mysqli - > query($sql) === true) {
  echo "<script>alert('You have successfully registered');</script>";
} else {
  echo "<script>alert('could not create shop something went wrong  ');  <
  /script>";  
  $mysqli - > error;
}

//  update query execution
$sql = "UPDATE users SET userType = 'vendor', vendor_name='$vendor_name'  
WHERE id = '".$_SESSION['
id ']."'
";

if ($mysqli - > query($sql) === true) {
  echo "<script>alert('update successfull ');</script>";
  header("location:my-account.php");
} else {
  echo "<script>alert('could not create shop something went wrong  ');  <
  /script>";  $mysqli->error;
}

Collectives™ on Stack Overflow

Insert and update into multiple tables, MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related