can this be achieved by a single mysql instruction?

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,

Re: can this be achieved by a single mysql instruction?

On Fri, 20 Jul 2007 09:44:24 +0200, newbie wrote:

> I am writing a DB like this:
> ------------------------------------------------------------ ---------
> | id (auto_incremented filed) | foo | bar |
> ------------------------------------------------------------ ---------
>
> $sql_query =3D "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

Re: can this be achieved by a single mysql instruction?

On Jul 20, 12:56 am, Rik wrote:
> On Fri, 20 Jul 2007 09:44:24 +0200, newbie wrote:
> > 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

Re: can this be achieved by a single mysql instruction?

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.

Re: can this be achieved by a single mysql instruction?

On Jul 20, 1:39 pm, Sanders Kaufman wrote:
> 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!

Re: can this be achieved by a single mysql instruction?

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

Re: can this be achieved by a single mysql instruction?

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.

Re: can this be achieved by 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.'.

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

Re: can this be achieved by a single mysql instruction?

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

Re: can this be achieved by a single mysql instruction?

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