database input copy

Question

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".

You can use an "on insert" trigger, but perhaps there is a better database design that lets you avoid putting the same data in two places? Exactly what is it that you are trying to accomplish? — Thomas Padron-McCarthy
– Thomas Padron-McCarthy, Commented Feb 11, 2013 at 8:11

peterm · Accepted Answer · 2013-02-11 12:27:06Z

2

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);

edited Feb 11, 2013 at 12:27

answered Feb 11, 2013 at 8:19

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcus Over a year ago

Its MySQL i´m using. I will take a look at "LAST_INSERT_ID()" and see if i can get it to work, thanks.

Marcus Over a year ago

Hmm i can´t get it to work acutally, have searched all over google soon and found nothing that i understand how to use. I will post a link with all my mysql querys and hope u can give me som direction when you know how it all structured in my code. Heres my code: pastebin.com/nqmcSJ74

peterm Over a year ago

See updated answer. Don't use mysql_ extension as it has been deprecated. Use prepared statements with either PDO or MySQLi. Your code is vulnerable to sql-injections. Sanitize all user input and use prepared statements to avoid it.

Marcus Over a year ago

Hello again! Have tried your last code for hours now, and the only thing i get after i press my submit button i only got a white screen and nothing in my db. I have tried to use your code and mine together, but what you said about "mysqli", when i use that it change colour to black (not working code), dont know why, also tried to remove the i, and only running it with "mysql" but that didnt work either. Here you can see my code i expirimented with now: pastebin.com/KAU6ti4U. Would really estimate if you could take a lock to try to help me again!

Collectives™ on Stack Overflow

database input copy

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related