0

2 Tables:

1. BOOKS_IN

  • BOOKS_in_ID,
  • DATE,
  • USERID

2. BOOKS_IN_DETAIL

  • BOOKS_in_ID,
  • BOOK_ID,
  • STOCK

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.

SonalPM
  • 1,317
  • 8
  • 17
Marchelina Manullang
  • 11
  • 1
  • 2

4 Answers4

1

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

Community
  • 1
  • 1
Matheno
  • 4,112
  • 6
  • 36
  • 53
1

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

Asenar
  • 6,732
  • 3
  • 36
  • 49
0

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

McBurgerKong
  • 153
  • 13
  • This is still multiple queries. It is just a way to send them in one command to reduce the number of DB requests that you need to make. This wouldn't really provide any value to the OP. – Patrick Q Nov 17 '14 at 13:37
0

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