CODEWITHSUNDEEP
phpmysql
Fourty-Two
Fourty-Two

Reputation: 77

[PHP][MySQL] How to insert data in 2 tables at same time?

I have a little problem to insert data in 2 tables and need some help with it.

For example:

Table 1:

CREATE TABLE tb1 (
    tb1_id int(5) not null AUTO_INCREMENT PRIMARY KEY,
    tb1_title varchar(50),
    tb1_cat varchar(50)
);

Table 2:

CREATE TABLE tb2 (
    tb2_id int(5) not null AUTO_INCREMENT PRIMARY KEY,
    tb2_title varchar(50),
    tb2_doc varchar(200),
    id_tb1 int(5) not null REFERENCES tb1(tb1_id)
);

One entry of tb1 can have many information(rows) of tb2, but how to insert the id of tb1 in some rows of tb2?

formular.php:

$sqla = "INSERT INTO tb_1 (tb1_title, tb1_cat) VALUES ('$tb1_title', '$tb1_cat')";
$sqlb = "INSERT INTO tb_2 (tb2_title, tb2_doc, <b>[? ? ?]</b>) VALUES ('$tb2_title', '$tb2_doc', <b>[? ? ?]</b>)";

mysqli_query($db, $sqla);
mysqli_query($db, $sqlb);

What do I have to change here?

Upvotes: 1

Views: 250

Answers (3)

user10200759
user10200759

Reputation: 11

$sqla = "INSERT INTO tableA (col1, col2) VALUES (val1, val2)";
mysqli_query($db,$sqla);
$id = mysqli_insert_id($db); //add it here.

$sqlb = "INSERT INTO tableB (col1,col2) VALUES (val1, val2, ...)";
//then you can pass the id into your second query
//...

Upvotes: 0

Nick
Nick

Reputation: 147146

You can get the value of tb1_id using mysqli_insert_id(), and then insert that into tb2:

$sqla = "INSERT INTO tb1 (tb1_title, tb1_cat) VALUES ('$tb1_title', '$tb1_cat')";
if (mysqli_query($db, $sqla)) {
    $tb1_id = mysqli_insert_id($db);
    $sqlb = "INSERT INTO tb2 (tb2_title, tb2_doc, id_tb1) VALUES ('$tb2_title', '$tb2_doc', $tb1_id)";
    mysqli_query($db, $sqlb);
}

Upvotes: 1

Avi
Avi

Reputation: 1594

Yes you can.. try this..

BEGIN;
    INSERT INTO tb_1 (tb1_title, tb1_cat) VALUES ('$tb1_title', '$tb1_cat');
    INSERT INTO tb_2 (tb2_title, tb2_doc, <b>[? ? ?]</b>,id_tb1) VALUES ('$tb2_title', '$tb2_doc', <b>[? ? ?]</b>,LAST_INSERT_ID());
    COMMIT;

Upvotes: 0

Related Questions