CODEWITHSUNDEEP
phpmysqldatabasesql-insert
Marchelina Manullang
Marchelina Manullang

Reputation: 11

how to insert data into 2 different tables (php mysql)

2 Tables:

1. BOOKS_IN

2. BOOKS_IN_DETAIL

BOOKS_in_ID is a primary key and i need BOOKS_in_ID is automatically insert into BOOKS_IN_DETAIL. Here, is it possible to insert records into 2 table using single query?

thankyou for your advise.

Upvotes: 0

Views: 3347

Answers (4)

Matheno
Matheno

Reputation: 4172

You still need two INSERT statements, but it sounds like you want to get the IDENTITY from the first insert and use it in the second, in which case, you might want to look into OUTPUT or OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx

Src and possible duplicate of: SQL Server: Is it possible to insert into two tables at the same time?

You can also use LastInsertId() for PDO.

A small example:

$sql = "INSERT INTO city (`city`) VALUES ('Paris') ON DUPLICATE KEY UPDATE `city` = 'Paris"; 
$dbh->query($sql); 
echo $dbh->lastInsertId();

Src: http://php.net/manual/en/pdo.lastinsertid.php

Or get the last insert ID in mysqli:

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

Src: http://php.net/manual/en/mysqli.insert-id.php

Upvotes: 1

Asenar
Asenar

Reputation: 7030

You need to call the appropriate method to get the last inserted id.

Assuming you use PDO, you need to call the method lastInsertId. $books_in_id = $pdo->lastInsertId();

If you use mysqli_* extension that's $books_in_id = $mysqli->insert_id;

EDIT: if you use the mysql_* version ( which is deprecated), upgrade first to mysqli_* , or check in the documentation

Upvotes: 1

user2778823
user2778823

Reputation:

No,Its not possible with only one INSERT query.

You can follow these steps

  1. Write two different queries and execute them
  2. Create Stored Procedure that execute two INSERT queried

For point One you can useLAST_INSERT_ID() function to add foreigh key

Upvotes: 0

McBurgerKong
McBurgerKong

Reputation: 151

Do you have to stick to mysql? Because if you can use mysqli you can use multi_query(), which lets you execute multiple queries at once.
Link : http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

Upvotes: 0

Related Questions