What is the best practice for saving specific data in one table while saving other data in another table in PHP?

When you need to save specific data in one table while saving other data in another table in PHP, the best practice is to use transactions. This allows you to group multiple SQL queries together and ensure that either all of them succeed or none of them do, maintaining data integrity. You can begin a transaction, execute the queries for each table, and then commit the transaction if all queries are successful or rollback the transaction if any query fails. 

<?php

// Establish database connection
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

// Begin transaction
$pdo->beginTransaction();

// Query to insert data into first table
$stmt1 = $pdo->prepare("INSERT INTO table1 (column1, column2) VALUES (:value1, :value2)");
$stmt1->execute(array(':value1' => 'data1', ':value2' => 'data2'));

// Query to insert data into second table
$stmt2 = $pdo->prepare("INSERT INTO table2 (column3, column4) VALUES (:value3, :value4)");
$stmt2->execute(array(':value3' => 'data3', ':value4' => 'data4'));

// Commit transaction if both queries are successful
$pdo->commit();

// Close connection
$pdo = null;

?>

Keywords

PHP database SQL data separation normalization

Related Questions