What are some best practices for creating a submit button that adds entries to multiple MySQL tables in PHP?

When creating a submit button that adds entries to multiple MySQL tables in PHP, it is important to use transactions to ensure data integrity. This involves starting a transaction, executing the SQL queries to insert data into the tables, and then committing the transaction if all queries are successful. If any query fails, the transaction should be rolled back to maintain consistency in the database. 

<?php

// Establish connection to MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

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

// SQL queries to insert data into multiple tables
$sql1 = "INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2')";
$sql2 = "INSERT INTO table2 (column3, column4) VALUES ('value3', 'value4')";

// Execute SQL queries
if ($conn->query($sql1) === TRUE && $conn->query($sql2) === TRUE) {
    // Commit transaction if all queries are successful
    $conn->commit();
    echo "New entries added to multiple tables successfully";
} else {
    // Rollback transaction if any query fails
    $conn->rollback();
    echo "Error: " . $conn->error;
}

// Close connection
$conn->close();

?>

Keywords

PHP MySQL submit button multiple tables entries

Related Questions