By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 ]
[ su*******@12jabber.com <--jabber IM ]
Jul 17 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On Wed, 26 Nov 2003 18:47:46 -0000, Sugapablo <ru********@sugapablo.com> wrote:
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?


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

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 17 '05 #2

P: n/a
Sugapablo wrote:
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?


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

int mysql_insert_id ( [resource link_identifier])

Jul 17 '05 #3

P: n/a
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" <ru********@sugapablo.com> wrote in message
news:sl***********************@dell.sugapablo.net. ..
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 ]
[ su*******@12jabber.com <--jabber IM ]

Jul 17 '05 #4

P: n/a

Read here:

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

Search for "LAST_INSERT_ID"

-John

Sugapablo wrote:
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?

Jul 17 '05 #5

P: n/a
Sugapablo wrote:
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?


mysql_insert_id()

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

Jul 17 '05 #6

P: n/a
There is function in PHP mysql_insert_id(), which return what you need.

"Sugapablo" <ru********@sugapablo.com> wrote in message
news:sl***********************@dell.sugapablo.net. ..
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 ]
[ su*******@12jabber.com <--jabber IM ]

Jul 17 '05 #7

P: n/a
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 ('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?


mysql_insert_id()

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


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
jt*****@bittware.com http://www.bittware.com (703) 779-7770
When you have a new hammer, the whole world looks like a nail.

Jul 17 '05 #8

P: n/a

On 5-Dec-2003, Jim Thomas <jt*****@bittware.com> wrote:
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 ('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?


mysql_insert_id()

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


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


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 ja*********@willglen.net (it's reserved for spammers)
Jul 17 '05 #9

P: n/a
Ig
> > 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 ('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?
>
>

mysql_insert_id()

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


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


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.

Jul 17 '05 #10

P: n/a
On 15 Dec 2004 19:02:08 -0800, "Ig" <bl*********@yahoo.com> wrote:
> 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('example')",$conn); > >>
> >> ...that had an auto-incrementing, unique identifying columnnamed "ID" > >> that would be populated with a unique number upon insertion...
> >>
> >> How can I get the unique number it assigned to that recordreturned for > >> 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,or the > last record inserted by the current instance?>


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.


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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.