Insert Rows in Relational Tables Using PHP (PDO)

Question

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.

score 0 · Answer 1 · edited Aug 31 '23 at 11:27

0

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]);

edited Aug 31 '23 at 11:27

Your Common Sense

156,878
40
214
345

answered Jan 24 '18 at 07:23

HZN

28
1
5

How could i use LAST_INSERT_ID in second insert query : INSERT INTO `Table2` (`idfk`, `paycodeidfk`, `amount`) VALUES (0, 22, 99); – Salman Khalid Jan 24 '18 at 07:27
Dear please focus my question, i want to insert these two rows at a same time..... with single function call. – Salman Khalid Jan 24 '18 at 07:38
Like : sqlqry = ' INSERT INTO `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. – Salman Khalid Jan 24 '18 at 07:41

score -1 · Answer 2 · answered Aug 31 '23 at 10:38

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).

Insert Rows in Relational Tables Using PHP (PDO)

2 Answers2