0

I'd like to know the right and proper practice to handle auto_increment in this table structure problem. Let's say I have these table:

StudentMaster

ID        auto_increment
name      varchar(100)
Status    int(11)

StudentDetail

ID         varchar(10)
examID     varchar(20)
examDate   date

When I insert a data to StudentMaster and StudentDetail in one action, how to make the ID in StudentDetail has the same value with StudentMaster? Because in the new form, there is no textbox containing ID to be inputted.

Data example:

ID     Name     Status
1      Roy          1
2      Nicole       2

ID    examID       examDate
1         A1     2017-05-15
1         A2     2017-05-15
1         A5     2017-05-17
2         A2     2017-05-15
1         A3     2017-05-16

P.S: I am in the database structure phase. The PHP form is not created yet.

ashura91
  • 441
  • 4
  • 17
  • First off, the ID in StudentDetail should also be INT. Usually you insert into the StudentMaster first, get the ID value and use that to insert all the other data in StudentDetail. – Sourcery May 18 '17 at 08:33
  • How? in the form there is no textbox for ID. Because it's auto_increment. The data that user need to input for StudentMaster is just name and status. – ashura91 May 18 '17 at 08:50
  • Yes, but in StudentDetail you have varchar(10) as ID, make this an INT instead. The two inserts has to be made in the application code. There is also a good answer on how to get the latest added id here: http://stackoverflow.com/questions/7501464/how-to-get-the-id-of-inserted-row-in-mysql – Sourcery May 18 '17 at 09:10
  • `SELECT LAST_INSERT_ID();` returns 0 ?? – ashura91 May 18 '17 at 09:19
  • Did you insert a row in StudentMaster before running last_insert_id()? – Sourcery May 18 '17 at 09:26
  • yes. I insert the data directly – ashura91 May 18 '17 at 09:55
  • Look at this post. Dont know what interface you use though. But you seem to need to do the last_insert_id in the same connection as your insert statement. http://stackoverflow.com/questions/13880267/mysql-last-insert-id-returns-0 – Sourcery May 18 '17 at 10:12

0 Answers0