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.

Improve this question
7
  • 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. Commented May 18, 2017 at 8: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. Commented May 18, 2017 at 8: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: stackoverflow.com/questions/7501464/… Commented May 18, 2017 at 9:10
  • SELECT LAST_INSERT_ID(); returns 0 ?? Commented May 18, 2017 at 9:19
  • Did you insert a row in StudentMaster before running last_insert_id()? Commented May 18, 2017 at 9:26

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.