From one set of variables post to multiple Php database tables

Question

I have variable from an HTML form that are currently being posted to one table in my database table.

I would like to post those same variables to other tables at the same time within the same function. Is this possible? Here is my current PHP function that is posting successfully to one table

<?php
    $var1 = $_POST['var1'];
    $var2 = $_POST['var2'];
    $var3 = $_POST['var3'];


    // Database connection
    $conn = new mysqli('localhost','user','password','database');
    if($conn->connect_error){
        echo "$conn->connect_error";
        die("Connection Failed : ". $conn->connect_error);
    } else {
        $stmt = $conn->prepare("insert into table1(var1, var2, var3) values(?, ?, ?)");
        $stmt->bind_param("sss", $var1, $var2, $var3);   
        $execval = $stmt->execute();
        echo $execval;
        $stmt->close();
        $conn->close();
    }
?>

And I would like the following the variables to post to more than one table in the same database, so was thinking the following but it does not work -

<?php
    $var1 = $_POST['var1'];
    $var2 = $_POST['var2'];
    $var3 = $_POST['var3'];


    // Database connection
    $conn = new mysqli('localhost','user','password','database');
    if($conn->connect_error){
        echo "$conn->connect_error";
        die("Connection Failed : ". $conn->connect_error);
    } else {
        $stmt = $conn->prepare("insert into table1(var1, var2, var3) values(?, ?, ?)");
        $stmt->bind_param("sss", $var1, $var2, $var3);
        $stmt = $conn->prepare("insert into table2(var1) values(?)");
        $stmt->bind_param("s", $var1);
        $stmt = $conn->prepare("insert into table3(var2, var3) values(?, ?)");
        $stmt->bind_param("ss", $var2, $var3); 
        $execval = $stmt->execute();
        echo $execval;
        $stmt->close();
        $conn->close();
    }
?>

It does not post to any of the tables when coded like this unfortunately, there is a connection error — KieranDL3, Jun 22 '20 at 19:15
try to call $stmt->execute(); after every calling of $stmt->bind_param — Mustafa Hamdi, Jun 22 '20 at 19:15
phpMyAdmin is a MySQL administration tool written in PHP, it is not a database itself. You are probably using MySQL or MariaDB as your DB. — Dharman, Jun 22 '20 at 19:43

score 0 · Answer 1 · answered Jun 22 '20 at 19:49

Yes, it is possible. You can do what you are doing now, but you need to call execute() method after preparing each query. Apart from this, it would also be a good idea to wrap this in a transaction. Transactions help you make you sure that either all or none operations are successful. If one of them fails the others are not executed.

Your fixed code should look something like this:

<?php
$var1 = $_POST['var1'];
$var2 = $_POST['var2'];
$var3 = $_POST['var3'];


// Database connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // switches error reporting on
$conn = new mysqli('localhost','user','password','database');
$conn->set_charset('utf8mb4'); // always set the charset

// Start transaction
$conn->begin_transaction();

$stmt = $conn->prepare("insert into table1(var1, var2, var3) values(?, ?, ?)");
$stmt->bind_param("sss", $var1, $var2, $var3);
$stmt->execute();

$stmt = $conn->prepare("insert into table2(var1) values(?)");
$stmt->bind_param("s", $var1);
$stmt->execute();

$stmt = $conn->prepare("insert into table3(var2, var3) values(?, ?)");
$stmt->bind_param("ss", $var2, $var3); 
$stmt->execute();

// End transaction
$conn->commit();

score -2 · Accepted Answer · answered Jun 22 '20 at 19:21

-2

Try to call $stmt->execute(); after every calling of $stmt->bind_param();

Take a look at this solved problem for executing multiple queries at the same call. PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

answered Jun 22 '20 at 19:21

Mustafa Hamdi

134
1
9

From one set of variables post to multiple Php database tables

2 Answers2