can this be achieved by a single mysql instruction?

[newbie]

I am writing a DB like this:
---------------------------------------------------------------------
| id (auto_incremented filed) | foo | bar |
---------------------------------------------------------------------

$sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
//how can I get id for the above inserted entry? is that possible I
get it with a single mysql instruction?
since each time I write a record into mysql, 'id' is incremented,
how can I get the id that an insertion corresponds to (can I get it
with a single mysql instruction)?

Thanks,

[Rik]

On Fri, 20 Jul 2007 09:44:24 +0200, newbie <mi******@yahoo.comwrote:

I am writing a DB like this:
---------------------------------------------------------------------
| id (auto_incremented filed) | foo | bar |
---------------------------------------------------------------------

$sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
//how can I get id for the above inserted entry? is that possible I
get it with a single mysql instruction?
since each time I write a record into mysql, 'id' is incremented,
how can I get the id that an insertion corresponds to (can I get it
with a single mysql instruction)?

PHP:
mysql_insert_id();
MySQL:
mysql_query('SELECT LAST_INSERT_ID()');
--
Rik Wasmus

[newbie]

On Jul 20, 12:56 am, Rik <[e-mail]:

On Fri, 20 Jul 2007 09:44:24 +0200, newbie <[e-mail]:

I am writing a DB like this:
---------------------------------------------------------------------
| id (auto_incremented filed) | foo | bar |
---------------------------------------------------------------------

$sql_query = "INSERT INTO `table` (`foo`, `bar`) VALUES ('$string_1',
'$string_2')";
mysql_query($sql_query, $db_handle);
//how can I get id for the above inserted entry? is that possible I
get it with a single mysql instruction?

since each time I write a record into mysql, 'id' is incremented,
how can I get the id that an insertion corresponds to (can I get it
with a single mysql instruction)?

PHP:
mysql_insert_id();

Thanks for the answer. But I undersatnd mysql_insert_id() is to
'Retrieves the ID generated for an AUTO_INCREMENT column by the
previous INSERT query.'. How to deal with the situation that multi-
users insert rows concurrently?

Say, I have a user_insert.php page, and each user accessing this page
will insert a foo bar(say, both are user specified strings) a row, and
he/she get an ID back---and he/she need to know exactly which ID his/
her string corresponds to.

It seems that by using the following code can't guarantee atomicity.

mysql_query("INSERT INTO `table` (`foo`, `bar`) values ('example foo',
'example bar')");
mysql_insert_id();
Thanks

MySQL:
mysql_query('SELECT LAST_INSERT_ID()');

--
Rik Wasmus

[Sanders Kaufman]

newbie wrote:

Thanks for the answer. But I undersatnd mysql_insert_id() is to
'Retrieves the ID generated for an AUTO_INCREMENT column by the
previous INSERT query.'. How to deal with the situation that multi-
users insert rows concurrently?

That's only a problem if you use the SELECT method.

If you use mysql_insert_id or (whatever that command is) it gets the
last insert *on that connection*. So as long as two different users are
using two different connections, you don't have to worry about getting
back the wrong ID.

[newbie]

On Jul 20, 1:39 pm, Sanders Kaufman <[e-mail]:

newbie wrote:

Thanks for the answer. But I undersatnd mysql_insert_id() is to
'Retrieves the ID generated for an AUTO_INCREMENT column by the
previous INSERT query.'. How to deal with the situation that multi-
users insert rows concurrently?

That's only a problem if you use the SELECT method.

If you use mysql_insert_id or (whatever that command is) it gets the
last insert *on that connection*. So as long as two different users are
using two different connections, you don't have to worry about getting
back the wrong ID.

ah, thanks!

[Michael Fesser]

..oO(Sanders Kaufman)

>newbie wrote:

>Thanks for the answer. But I undersatnd mysql_insert_id() is to
'Retrieves the ID generated for an AUTO_INCREMENT column by the
previous INSERT query.'. How to deal with the situation that multi-
users insert rows concurrently?

That's only a problem if you use the SELECT method.

Nope.

>If you use mysql_insert_id or (whatever that command is) it gets the
last insert *on that connection*.

So does LAST_INSERT_ID(). The last auto-generated ID is maintained by
the server on a per-connection basis. It doesn't matter if you use a
SELECT statement or an API call to retrieve it.

Micha

[Sanders Kaufman]

Michael Fesser wrote:

.oO(Sanders Kaufman)

>>If you use mysql_insert_id or (whatever that command is) it gets the
last insert *on that connection*.

So does LAST_INSERT_ID(). The last auto-generated ID is maintained by
the server on a per-connection basis. It doesn't matter if you use a
SELECT statement or an API call to retrieve it.

Wow.
I coulda saved myself a lot of trouble a long time ago if I hadn't
learned that wrong... and taught it wrong.

[Gordon Burditt]

since each time I write a record into mysql, 'id' is incremented,

how can I get the id that an insertion corresponds to (can I get it
with a single mysql instruction)?

PHP:
mysql_insert_id();

Thanks for the answer. But I undersatnd mysql_insert_id() is to
'Retrieves the ID generated for an AUTO_INCREMENT column by the
previous INSERT query.'.

.... by the privious INSERT query *ON THIS CONNECTION*.

>How to deal with the situation that multi-
users insert rows concurrently?

Don't deal with it: other users can (maliciously or not) insert
on other connections until they are blue in the face and not change
the result you get from mysql_insert_id().

>Say, I have a user_insert.php page, and each user accessing this page
will insert a foo bar(say, both are user specified strings) a row, and
he/she get an ID back---and he/she need to know exactly which ID his/
her string corresponds to.

If you *want* interference between users, you'll need to do something
like "SELECT max(id) FROM ... "

>It seems that by using the following code can't guarantee atomicity.

As long as no other queries are made between the two statements
listed *ON THIS CONNECTION*, you're OK.

>mysql_query("INSERT INTO `table` (`foo`, `bar`) values ('example foo',
'example bar')");
mysql_insert_id();

[Sanders Kaufman]

Gordon Burditt wrote:

>>Say, I have a user_insert.php page, and each user accessing this page
will insert a foo bar(say, both are user specified strings) a row, and
he/she get an ID back---and he/she need to know exactly which ID his/
her string corresponds to.

If you *want* interference between users, you'll need to do something
like "SELECT max(id) FROM ... "

Actually, I find myself having to build stuff in MySQL that *might* end
up on other DBMS's, so relying on auto-unique stuff in MySQL isn't feasible.

To keep my apps from breaking when they migrate like that, I've been
using a UID function. There are a lot of them out there if you query a
search on "UUID +PHP".

By generating a UUID to use as your primary key, instead of the
auto-increment thingy, YOU create the PK value as a "Universally Unique
ID". (Now - I haven't travelled the universe, but my *faith* tells me
it's a safe bet that I won't ever get a duplicate with one of these UUID
functions.)

This has the *added* benefit of avoiding having to do TWO conversations
with the DBMS - one to insert, and one to get the insert ID. Instead,
you just insert it with your own UUID as the PK. One shot, and the
programmer is in control, not the DBMS.

This becomes even *more* efficient if your DBMS and your HTTPd are not
on the same server.

function fnUUID(){
//Returns format: [12345678-1234-1234-123456789012]
$sRetVal = "";
$sRetVal .= sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),
mt_rand( 0, 0xffff ),
mt_rand( 0, 0x0fff ) | 0x4000,
mt_rand( 0, 0x3fff ) | 0x8000,
mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),
mt_rand( 0, 0xffff ) );
return $sRetVal;
}

[Michael Fesser]

..oO(Sanders Kaufman)

>Actually, I find myself having to build stuff in MySQL that *might* end
up on other DBMS's so relying on auto-unique stuff in MySQL isn't feasible.

Of course there are other - more portable - ways to create unique
numbers, i.e. sequences (some DBMS support them natively, in MySQL they
can be emulated).

>To keep my apps from breaking when they migrate like that, I've been
using a UID function.

Not the best idea.

>By generating a UUID to use as your primary key, instead of the
auto-increment thingy, YOU create the PK value as a "Universally Unique
ID". (Now - I haven't travelled the universe, but my *faith* tells me
it's a safe bet that I won't ever get a duplicate with one of these UUID
functions.)

A UUID is not unique. It can be _considered_ unique, because a collision
is highly unlikely, but an AUTO_INCREMENT or a sequence _is_ unique.

>This has the *added* benefit of avoiding having to do TWO conversations
with the DBMS - one to insert, and one to get the insert ID.

True, but on the other hand you're wasting a lot of space. It's even
more waste if the UUID PK is referenced from other tables. There might
also be performance issues with the InnoDB storage engine (from what
I've read).

>Instead,
you just insert it with your own UUID as the PK. One shot, and the
programmer is in control, not the DBMS.

This becomes even *more* efficient if your DBMS and your HTTPd are not
on the same server.

I don't consider that efficient. An AUTO_INCREMENT is efficient and
cheap. If it has to be portable, then using sequences can be the way.
Still more efficient than UUIDs.

Micha