Getting MySQL last insert id() for two inserts in one query

[csdude55]

I'm doing this in PHP, so I'm asking here instead of the Database forum...

Here's an example of what I'm doing:

$write = sprintf(<<<EOF
INSERT INTO table (colA, colB) VALUES
('%s', '%s'),
('%s', '%s')
EOF
,
 mysql_real_escape_string($one),
 mysql_real_escape_string($two),
 mysql_real_escape_string($three),
 mysql_real_escape_string($four));

mysql_query($write) or die(mysql_error());

If I just needed the AutoIncrement ID of one row, I would do this:

$new_id = mysql_insert_id();

But since I'm inserting 2 rows with one query, is there a way to get both of the IDs? Or do I have to do it in two separate queries?

[robzilla]

Either seperate the inserts or, if the inserted data is unique to those rows, use a subsequent SELECT query to fetch the IDs.

[Demaestro]

Another option is to create a function that returns a unique id from a sequence. You can do this with a mysql function or a php one. Get the ID before the insert and pass it in rather than using the auto increment.

$id1 = genId();
$id2 = genId();

$write = sprintf(<<<EOF
INSERT INTO table (id, colA, colB) VALUES
(%s, '%s', '%s'),
(%s, '%s', '%s')
EOF
,
$id1,
mysql_real_escape_string($one),
mysql_real_escape_string($two),
$id2,
mysql_real_escape_string($three),
mysql_real_escape_string($four));

[csdude55]

I'd read on another site where someone stated that $new_id = mysql_insert_id(); will return the ID of the first insert, so I could just increment it by 1 to get the second insert.

Eg,

$first_insert_id = mysql_insert_id();
$second_insert_id = $first_insert_id + 1;

I'm not 100% sure how I feel about that, though, because it's not impossible for two people to insert at the exact same time. So for my purposes, generating an ID like Demaestro mentioned might be better; maybe I could create an alphanumeric ID based on the user's username followed by a number so that there would be no chance of it overlapping. And if I'm going to do a SELECT statement, anyway, then I might as well make it a little more secure.

[robzilla]

Adding more complexity and size to your database just to avoid doing a few more simple writes seems rather counterproductive to me. Is there an actual need for reducing writes or are you just "optimizing" prematurely?

And yes, you can't ensure data integrity with a + 1.

[Demaestro]

Agree with Rob, you can not count on a+1 being the id, unless you put the 2 inserts into a transactional statement, which I would actually suggest doing anyways.

The reason being if the 2nd insert fails but the first one works you may come out of alignment or end up with orphaned records. Placing them in a transactional statement would roll back the first insert if the second fails.

[csdude55]

Is there an actual need for reducing writes or are you just "optimizing" prematurely?

A little of both, I guess. My current site is running my server in to the red on both CPU and RAM, so while I'm rebuilding I'm doing everything I can think of to save on resources. I use mysql_tuner to optimize MySQL regularly, but it's still getting tight. I really need to upgrade the server, but really can't afford it, so...

This section could realistically have hundreds of inserts per minute, so my theory is that, by doing one insert instead of two, I would cut the demand in half. But if I'm going to do a select query afterward then I'm not sure that saves anything, so I guess it doesn't really matter here :-(

[robzilla]

Well, not quite in half... the two separate queries are simpler than the combined query, but either way you'll still be inserting two new rows. You could benchmark both strategies, I really don't know which would be faster; a "multi-write" with a SELECT to get the IDs, or separate writes. I suppose the pre-generated unique ID could also work, providing it's unique enough, but possibly at the cost of simplicity and database size.

Reducing server resource usage really requires you to know where exactly those resources are being spent, and to pick the lowest-hanging fruits first.