INSERT INTO two different tables, but have the same ID?

Question

I have a database of Users and another table for Teachers. Teachers have all the properties as a user but also an e-mail address. When inserting into the DB how can I insert the info, ensuring that the ID is the same for both?

the ID currently is on automatic incrament.

this is what I have at the moment:

$sqlQuery="INSERT INTO user(firstName,lastName,DOB,title,password,classRoomID) 
    VALUES('$myFirstName','$myLastName','$myDOB','$myTitle','$newPassword','$myClassRoom')";
    $result=mysql_query($sqlQuery);

$sqlQuery = "INSERT INTO teacher(email) VALUES ('$myEmail')";
$result=mysql_query($sqlQuery);

thank you!

Answer 1

use MYSQL function LAST_INSERT_ID() OR php mysql http://ro1.php.net/manual/en/function.mysql-insert-id.php

Answer 2

why to use separate table for teachers. instead, you can have email field with in user table and additional field with flag (T ( for teacher) and U (for user). Default can be a U. This have following Pros.

1. Will Not increase table size as email would be varchar
2. Remove extra overhead of maintaining two tables.
3. Same Id can be used

If you want to have that as separate table then answer you selected is good one but make sure last insert id is called in same connection call.

Answer 3

Try this:

$sqlQuery="INSERT INTO user(firstName,lastName,DOB,title,password,classRoomID) 
    VALUES('$myFirstName','$myLastName','$myDOB','$myTitle','$newPassword','$myClassRoom')";
    $result=mysql_query($sqlQuery);

    $id = mysql_insert_id();

$sqlQuery = "INSERT INTO teacher(id, email) VALUES (' $id ','$myEmail')";
$result=mysql_query($sqlQuery);

Answer 4

After the first insert, fetch the last inserted id:

$last_id = mysqli_insert_id(); // or mysql_insert_id() if you're using old code

Or you could expand your second query and use mysql's integrated LAST_INSERT_ID() function:

$sqlQuery = "INSERT INTO teacher(id, email) VALUES ((SELECT LAST_INSERT_ID()), '$myEmail')";