PHP-MySQL: Returning ID when new record is made?

Let's say I create a new record in a table like this:
mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);

....that had an auto-incrementing, unique identifying column named "ID"
that would be populated with a unique number upon insertion...

How can I get the unique number it assigned to that record returned for
futher use?


--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@12jabber.com <--jabber IM ]

re: PHP-MySQL: Returning ID when new record is made?

On Wed, 26 Nov 2003 18:47:46 -0000, Sugapablo <russREMOVE@sugapablo.com> wrote:
[color=blue]
>Let's say I create a new record in a table like this:
>mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
>...that had an auto-incrementing, unique identifying column named "ID"
>that would be populated with a unique number upon insertion...
>
>How can I get the unique number it assigned to that record returned for
>futher use?[/color]

http://uk2.php.net/manual/en/ref.mysql.php

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

re: PHP-MySQL: Returning ID when new record is made?

Sugapablo wrote:
[color=blue]
> Let's say I create a new record in a table like this:
> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
> ...that had an auto-incrementing, unique identifying column named "ID"
> that would be populated with a unique number upon insertion...
>
> How can I get the unique number it assigned to that record returned for
> futher use?[/color]

Check out your php manual ( http://www.php.net/ ) for this function:

int mysql_insert_id ( [resource link_identifier])

re: PHP-MySQL: Returning ID when new record is made?

select last_insert_id()

after the insert..

But you can read the manual which is very available and it will tell you
this.

Thanks

Aaron

"Sugapablo" <russREMOVE@sugapablo.com> wrote in message
news:slrnbs9taa.nru.russREMOVE@dell.sugapablo.net. ..[color=blue]
> Let's say I create a new record in a table like this:
> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
> ...that had an auto-incrementing, unique identifying column named "ID"
> that would be populated with a unique number upon insertion...
>
> How can I get the unique number it assigned to that record returned for
> futher use?
>
>
> --
> [ Sugapablo ]
> [ http://www.sugapablo.com <--music ]
> [ http://www.sugapablo.net <--personal ]
> [ sugapablo@12jabber.com <--jabber IM ][/color]

re: PHP-MySQL: Returning ID when new record is made?

Read here:

http://www.mysql.com/doc/en/Miscella...functions.html

Search for "LAST_INSERT_ID"

-John



Sugapablo wrote:
[color=blue]
> Let's say I create a new record in a table like this:
> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
> ...that had an auto-incrementing, unique identifying column named "ID"
> that would be populated with a unique number upon insertion...
>
> How can I get the unique number it assigned to that record returned for
> futher use?
>
>[/color]

re: PHP-MySQL: Returning ID when new record is made?

Sugapablo wrote:[color=blue]
> Let's say I create a new record in a table like this:
> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
> ...that had an auto-incrementing, unique identifying column named "ID"
> that would be populated with a unique number upon insertion...
>
> How can I get the unique number it assigned to that record returned for
> futher use?
>
>[/color]

mysql_insert_id()

http://us2.php.net/manual/en/functio...-insert-id.php

re: PHP-MySQL: Returning ID when new record is made?

There is function in PHP mysql_insert_id(), which return what you need.

"Sugapablo" <russREMOVE@sugapablo.com> wrote in message
news:slrnbs9taa.nru.russREMOVE@dell.sugapablo.net. ..[color=blue]
> Let's say I create a new record in a table like this:
> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>
> ...that had an auto-incrementing, unique identifying column named "ID"
> that would be populated with a unique number upon insertion...
>
> How can I get the unique number it assigned to that record returned for
> futher use?
>
>
> --
> [ Sugapablo ]
> [ http://www.sugapablo.com <--music ]
> [ http://www.sugapablo.net <--personal ]
> [ sugapablo@12jabber.com <--jabber IM ][/color]

re: PHP-MySQL: Returning ID when new record is made?

Bruce Wolk wrote:[color=blue]
> Sugapablo wrote:
>[color=green]
>> Let's say I create a new record in a table like this:
>> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
>>
>> ...that had an auto-incrementing, unique identifying column named "ID"
>> that would be populated with a unique number upon insertion...
>>
>> How can I get the unique number it assigned to that record returned for
>> futher use?
>>
>>[/color]
>
> mysql_insert_id()
>
> http://us2.php.net/manual/en/functio...-insert-id.php
>[/color]

Does mysql_insert_id() return the last record inserted by ANYONE, or the
last record inserted by the current instance?

In other words, suppose there's a site with heavy traffic that
manipulates a database. Two users get on and insert info into the db at
about the same time.

user1 inserts data
user2 inserts data
user1 mysql_insert_id()
user2 mysql_insert_id()

will user1 get the id of the data he *just* inserted, or will he get
user2's id?

--
Jim Thomas Principal Applications Engineer Bittware, Inc
jthomas@bittware.com http://www.bittware.com (703) 779-7770
When you have a new hammer, the whole world looks like a nail.

re: PHP-MySQL: Returning ID when new record is made?

On 5-Dec-2003, Jim Thomas <jthomas@bittware.com> wrote:
[color=blue]
> Bruce Wolk wrote:[color=green]
> > Sugapablo wrote:
> >[color=darkred]
> >> Let's say I create a new record in a table like this:
> >> mysql_query("INSERT INTO table (col1) VALUES ('example')",$conn);
> >>
> >> ...that had an auto-incrementing, unique identifying column named "ID"
> >> that would be populated with a unique number upon insertion...
> >>
> >> How can I get the unique number it assigned to that record returned for
> >> futher use?
> >>
> >>[/color]
> >
> > mysql_insert_id()
> >
> > http://us2.php.net/manual/en/functio...-insert-id.php
> >[/color]
>
> Does mysql_insert_id() return the last record inserted by ANYONE, or the
> last record inserted by the current instance?[/color]

It returns the insert id of the last insert for a particular link_id ($conn
in the above example). It would be pretty useless otherwise.

The documentation is pretty clear on this.

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

re: PHP-MySQL: Returning ID when new record is made?

> > Bruce Wolk wrote:[color=blue][color=green][color=darkred]
> > > Sugapablo wrote:
> > >
> > >> Let's say I create a new record in a table like this:
> > >> mysql_query("INSERT INTO table (col1) VALUES[/color][/color][/color]
('example')",$conn);[color=blue][color=green][color=darkred]
> > >>
> > >> ...that had an auto-incrementing, unique identifying column[/color][/color][/color]
named "ID"[color=blue][color=green][color=darkred]
> > >> that would be populated with a unique number upon insertion...
> > >>
> > >> How can I get the unique number it assigned to that record[/color][/color][/color]
returned for[color=blue][color=green][color=darkred]
> > >> futher use?
> > >>
> > >>
> > >
> > > mysql_insert_id()
> > >
> > > http://us2.php.net/manual/en/functio...-insert-id.php
> > >[/color]
> >
> > Does mysql_insert_id() return the last record inserted by ANYONE,[/color][/color]
or the[color=blue][color=green]
> > last record inserted by the current instance?>[/color][/color]

Jim Thomas & All,

The function that has been suggested does not seem to be what you want.
Everyone with their manuals buzz off. This requires some logic.

First, instead of relying on a one stop shop function to suit your
needs you need to think beyond this. Imagine coming to a page for the
first time that requires a customer ID. You could just rely on the dB
to auto increment but then the user does know what the value is/was.

Lastly, this is how I handle finding the last record auto incremented,
increment it, and post it for a future query (You do not always need to
rely on the system):

$query = "select cust_id from customer_data order by cust_id DESC";
$data_pointer = mysql_query($query);

if ($getID = mysql_fetch_assoc($data_pointer))
{
$lastID = $getID[cust_id];
$lastID++;
print $lastID;
}

You may need to do some formatting with the value at he end of this but
this code will lead you in the right direction.

re: PHP-MySQL: Returning ID when new record is made?

On 15 Dec 2004 19:02:08 -0800, "Ig" <blackgold00@yahoo.com> wrote:
[color=blue][color=green][color=darkred]
>> > Bruce Wolk wrote:
>> > > Sugapablo wrote:
>> > >
>> > >> Let's say I create a new record in a table like this:
>> > >> mysql_query("INSERT INTO table (col1) VALUES[/color][/color]
>('example')",$conn);[color=green][color=darkred]
>> > >>
>> > >> ...that had an auto-incrementing, unique identifying column[/color][/color]
>named "ID"[color=green][color=darkred]
>> > >> that would be populated with a unique number upon insertion...
>> > >>
>> > >> How can I get the unique number it assigned to that record[/color][/color]
>returned for[color=green][color=darkred]
>> > >> futher use?
>> > >>
>> > >>
>> > >
>> > > mysql_insert_id()
>> > >
>> > > http://us2.php.net/manual/en/functio...-insert-id.php
>> > >
>> >
>> > Does mysql_insert_id() return the last record inserted by ANYONE,[/color][/color]
>or the[color=green][color=darkred]
>> > last record inserted by the current instance?>[/color][/color]
>
>Jim Thomas & All,
>
>The function that has been suggested does not seem to be what you want.
>Everyone with their manuals buzz off. This requires some logic.
>
>First, instead of relying on a one stop shop function to suit your
>needs you need to think beyond this. Imagine coming to a page for the
>first time that requires a customer ID. You could just rely on the dB
>to auto increment but then the user does know what the value is/was.
>
>Lastly, this is how I handle finding the last record auto incremented,
>increment it, and post it for a future query (You do not always need to
>rely on the system):
>
>$query = "select cust_id from customer_data order by cust_id DESC";
>$data_pointer = mysql_query($query);
>
>if ($getID = mysql_fetch_assoc($data_pointer))
>{
>$lastID = $getID[cust_id];
>$lastID++;
>print $lastID;
>}
>
>You may need to do some formatting with the value at he end of this but
>this code will lead you in the right direction.[/color]

This approach leads to a classic race condition. What's to stop another
session inserting this ID in the period between selecting and the time an
insert is done in this session? You'd have to lock the table to make this safe,
which limits your scalability.

And another point; why select ordered by a field descending, then select only
one row, when you could select max(field) instead, which is considerably more
efficient given the MySQL's client method of transferring the entire result set
to the client before allowing the first fetch (unless you use unbuffered
queries, which you haven't in the example).

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool