CODEWITHSUNDEEP
mysqlsqldatabase
Marcus
Marcus

Reputation: 55

database input copy

I´m gonna start of by typing what i want.

So, i have one table, where each row gets a new id automatically, and for each new row created there, i want that id to also be put into another table into a specific column.

Database-structure: event

(This is the table i want to copy the id from)

EventID | lots of more columns

-

Database-structure: jointable (This is the table i want the "EventID" be copied to)

EventID | more columns.

So, i want my EventID also be put in my jointable automatically when an event get put in my table "event".

Upvotes: 1

Views: 85

Answers (1)

peterm
peterm

Reputation: 92785

Use the function that returns last inserted auto-generated ID.

In MySql it's LAST_INSERT_ID()
In SQL Server it's SCOPE_IDENTITY, IDENT_CURRENT, @@IDENTITY

For example in MySql you can do something like this

INSERT INTO event (column1,column2...) VALUES (...); 
SET @last_event_id = LAST_INSERT_ID();
INSERT INTO jointable (EventID, column1, column2...) VALUES (@last_event_id, ...);

UPDATE: To do that from the client (php+mysqli) use mysqli_insert_id()

Your code in that case will look like

$db = mysqli_connect(...);
...
$query = "INSERT INTO event (column1, column2...) VALUES (...)";
mysql_query($query);

$last_event_id = mysqli_insert_id($db);

$query = "INSERT INTO jointable (EventID, column1, column2...) VALUES ($last_event_id, ...)";
mysql_query($query);

Upvotes: 2

Related Questions