I have 2 tables. In one table I add events, ID are auto-increment. The second table contains personal events.
So if I create event, it adds to both tables.
Here down below is a example of my code. So first of all I add a new event to general table. Then I want to add the same event to schedule table, but I need to get the ID of just added event. So I execute that second query.
Is there any optimized way how to do this or at least get the ID of just added event without extra query?
mysql_query("INSERT INTO events (title, description, user) VALUES ('$title', '$description', '".$user['id']."')");
$id = mysql_fetch_array(mysql_query("SELECT id FROM events WHERE user='".$user['id']."' ORDER BY date_added DESC LIMIT 1"));
mysql_query("INSERT INTO schedule (uid, event) VALUES ('".$user['id']."', '".$id['id']."')") or die(mysql_error());
mysql_insert_id() is what you are after, I think...
You could also use the MySQL native LAST_INSERT_ID() in your second INSERT query:
mysql_query("INSERT INTO schedule (uid, event) VALUES ('".$user['id']."', LAST_INSERT_ID())") or die(mysql_error());
...and skip retrieving the ID in PHP altogether.
there is no "optimized" way.
there is the right way of getting inserted id, called mysql_insert_id().
I guess you're looking for the last inserted id: LAST_INSERT_ID()
the native php function mysql_insert_id() will do it for sure
http://php.net/manual/en/function.mysql-insert-id.php
consider wrapping in a transaction? under high concurrency you could end up with the wrong id using this