1

I have these tables in a MYSQL database:

tBook (idBook,BookName)
tAuthor (idAuthor,AuthorName)
tBookAuthor (FK_Book,FK_Author)

I want to insert in one stored procedure a new book. In the same procedure I want to insert into the table tBookAuthor.

INSERT INTO `tBook`
   (`idBook`, `BookName`)
VALUES
   (23, 'myBookName')

INSERT INTO `tBookAuthor`
   (`FK_Book`, `FK_Author`)
VALUES
   (LAST_INSERT_ID(), 526)
Enamul Hassan
  • 5,266
  • 23
  • 39
  • 56
Lingo
  • 580
  • 2
  • 7
  • 26
  • What exactly is the issue you are facing? The 2 insert statements as you described them should work in a stored procedure. You probably do not need to insert any values in the `idBook` field, since that's likely to be an auto increment field. – Shadow Jul 22 '16 at 08:52
  • Possible duplicate of [Using Mysql to do multiple INSERT on linked tables](http://stackoverflow.com/questions/10075279/using-mysql-to-do-multiple-insert-on-linked-tables) – Shadow Jul 22 '16 at 08:53
  • Both insert should work. what issue are you facing? – Hafiz Arslan Jul 22 '16 at 09:06
  • My Problem I am facing is that php myadmin doesn't allow me to do this query: `DELIMITER // START TRANSACTION; INSERT INTO `tMedia` (`Title`, `Description`, `ISBN`, `InsertedIntoBibliothek`, `CoverLink`, `Note`, `FK_Author`, `FK_MediaType`) VALUES (`myTitle`, `myDescription`, `myISBN`, `myInsertDate`, `myCoverLink`, `myNote`, `myAuthor`, `myMediaType`) INSERT INTO `tMediaAuthor` (`FK_Media`, `FK_Author`) VALUES (LAST_INSERT_ID(), `myAuthor`) COMMIT; //` – Lingo Jul 22 '16 at 09:43
  • Welcome to Stack Overflow! I edited your question as far as I could guess your problem. However, add explanation of code and description so that more people with knowledge of the subject will see it. Please edit in the specific error-message you're encountering in case that's necessary to identify the specific problem. Good Luck! – Enamul Hassan Jul 24 '16 at 10:46

2 Answers2

0

The problem was that I tried to modify a storec procedure which already exists. I did this in PHPmyAdmin.

If you want to create a new stored procedure you do it like this:

DELIMITER //
CREATE PROCEDURE new_person(
  first CHAR(35), last CHAR(35), email CHAR(255), tool_id INT)
BEGIN
START TRANSACTION;
INSERT INTO `tBook`
(`idBook`,
`BookName`)
VALUES
(23
'myBookName')

INSERT INTO `tBookAuthor`
(`FK_Book`,
`FK_Author`)
VALUES
(LAST_INSERT_ID(),
526)
COMMIT;
END//
DELIMITER;

If you want to ALTER a stored procedure in phpmyadmin you do it like this:

BEGIN
START TRANSACTION;
INSERT INTO `tBook`
(`idBook`,
`BookName`)
VALUES
(23
'myBookName')

INSERT INTO `tBookAuthor`
(`FK_Book`,
`FK_Author`)
VALUES
(LAST_INSERT_ID(),
526)
COMMIT;
END
Lingo
  • 580
  • 2
  • 7
  • 26
-1

Instead of a stored procedure, since you are using PHP, I suggest doing 2 inserts, since the second insert is foreign keys you'll have to select the author, get the last insert ID of the book and do a simple insert there. It's a lot less trouble.

I also have some trouble understanding your dB structure, can one book be written by more than one author? If so, my bad, otherwise, save yourself the trouble of the helper table and just put the author id on the book.

Florian Humblot
  • 1,121
  • 11
  • 29