0

I've a table with the purpose of linking two other tables with foreign keys (table 3).

table1 = id, name
table2 = id, sport
table3 = fk_table1_id, dk_table2_id

I do server side code with php and i need a way to automatic insert a row into table 3 when a row is inserted into table2.
But i dont know how to do a php query that will get the generated id of the new row in table2?
(The id of table one i do have stored in a php variable)
Im a looking at a smart stored procedure?

jmail
  • 5,944
  • 3
  • 21
  • 35
Mac Luc
  • 971
  • 5
  • 14
  • 31
  • Use sql triggers.You can trigger an insert/delete query in sql when your php inserts into a table – Tyranicangel Apr 21 '14 at 13:49
  • but i can only get the values of the one table? – Mac Luc Apr 21 '14 at 13:51
  • Depends on the PHP-side of your code. Mysql stores a @last_insert_id, which could be retrieved in PHP with a dedicated method (mysql_insert_id is deprecated). No need for Triggers, that would be overkill. – flaschenpost Apr 21 '14 at 14:00
  • possible duplicate of [How do I get the last inserted ID of a MySQL table in PHP?](http://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php) – Marcus Adams Apr 21 '14 at 14:16

2 Answers2

1

Steps to follow:

  • Insert a row into table2.
  • Find the generated key from table2 and assign it to a PHP variable. 
    -- read this into a php variable, say, $last_key  
    SELECT LAST_INSERT_ID();
  • Use the $last_key in insert statement for table3.
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
0

You can Follow below Steps:

1) Make ID to AUTO_INCREMENT IN Table2

2) Create Store Procedure that work as below:

    A) INSERT Value to your Table2 :

    B) Increase ID value :  SELECT LAST_INSERT_ID() INTO VARIABLE; 

    C) Insert Value to your Table3   : use VARIABLE to get ID of Table2
Ilesh Patel
  • 2,053
  • 16
  • 27