I have two mysql tables
Table1 : Columns (id(int pk), empid(int), status( varchar(1) ) )
Table2 : Columns (idfk(int fk), paycodeidfk(int fk), amount(int) )
Relation : Table1->id is Primary Key and Table2->idfk is Foreign Key
How could i insert rows at same time in both relational tables using PDO.
for php code
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// First Query
$stmt = $conn->prepare("INSERT INTO `Table1` (`id`, `empid`, `status`) VALUES (?,?,?)";
$stmt->execute([0, 11, 0]);
// Get lastInsrtId
$last_id = $conn->lastInsertId();
// Second Query
$stmt = $conn->prepare("INSERT INTO `Table2` (`idfk`, `paycodeidfk`, `amount`) VALUES (?,?,?);";
$stmt->execute([$last_id, 22, 99]);
Table2 (idfk, paycodeidfk, amount) VALUES (0, 22, 99);Table1 (id, empid, status) VALUES ('', 11, 0); INSERT INTO Table2 (idfk, paycodeidfk, amount) VALUES (lastinserid(), 22, 99); ' I want to execute both insert quires at same time with one function call.If you want to save with on query you can do as follows:
INSERT INTO Table1 (empid, status) VALUES (1,2)
INSERT INTO Table2 (paycodeidfk, amount) VALUES(LAST_INSERT_ID(), 345)
In addition you can just use this query with with PhP PDO class(add paran key instead of value items).
