MySQL: Insert data in different table but have same id

Question

I have some question : "insert data in different table but have same id (auto_increment)"
This is my twice table

student

----------------------
id | name | class|
----------------------

score

---------------------------------------------
id | math | english | physical | year |
---------------------------------------------

I have some data like:
John , A , 90 ,80 ,70 ,2015
John , A , 70 ,90 ,50 ,2016

I want result Like:

student

----------------------
id | name | class|
----------------------
1 | John | A |
----------------------

score

---------------------------------------------
id | math | english | physical | year |
---------------------------------------------

1 | 90 | 80 | 70 |2015
1 | 70 | 90 | 50 |2016

Because table of student's "id" use Auto_increment

If I want twice table have same id when I insert data

What SQL can I use ??
(I think mysql_insert_id() is Solution but not sure...)

Thank help !!

Possible duplicate of [Why shouldn't I use mysql\_\* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Vidya Sagar, Jan 02 '16 at 14:16
I see a lot of problems with your schema - what if a student can be in more than one class, or changes classes from one year to the next? What id more scores are needed to be saved? Your `score` table is denormalized and I see no need for that. — D'Arcy Rittich, Jan 02 '16 at 14:28

wolfsgang · Accepted Answer · 2016-01-02T14:52:05.323

I hope you are looking for the query to do so.First of all use PDO or mysqli for interacting with the database.Here I use PDO.

function my_select($query)
{
    global $dbserver,$dbuser,$dbpwd,$dbname;
    $dbh= new PDO('mysql:host='.$dbserver.';dbname='.$dbname.'',$dbuser,$dbpwd);
    $rs=$dbh->prepare($query); //prepared statements have numerous advantages over executing sql statements directly
    $rs->execute(); 
    return $db->lastInsertId(); //returns last inserted id of current db connection
}

And the query can be,

$l_id=my_select(query1)   // query to insert into student
$query2="INSERT INTO score (id,...) VALUES ('".$l_id."',...)" ;
my_select(query2);

score 0 · Answer 2 · answered Jan 02 '16 at 14:24

If you are using Auto Increament id in both tables than you need to add a column in score table for reference of studentid. For example:

Insert into student (name) values ('test');

Than use:

mysql_insert_id();

In last insert data into score table:

Insert into score (yourcolumns, studentid) values (yourcolumnvalue, last_inserted_id);

Side note: instead of mysql_* functions use mysqli_* or PDO becuase mysql_* is deprecated and not available in PHP 7.

MySQL: Insert data in different table but have same id

2 Answers2