473,224 Members | 1,575 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 ]
[ su*******@12jabber.com <--jabber IM ]
Jul 17 '05 #1
10 41908
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
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
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

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

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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: lawrence | last post by:
I haven't been able to reach www.php.net for days. Most of the rest of the web is working for me, though I've bad trouble reaching any English sites. Anyone else having trouble?
6
by: the wonderer | last post by:
This is an elementary question, but I've not been able to find the answer, so here goes: I am developing a site using php. I have the html header information in a file that I include in all the...
14
by: Gregory | last post by:
Hello, I'm trying to do the above in order to process an image and return the result to an html image control. It fails and my key suspects are either the variable that I'm passing in -...
1
by: Danny Anderson | last post by:
Hola, PHP folk! I have a php page that contains a self-processing form. The form holds search results. The search terms originally came from the previous page, but the user can repeatedly...
0
by: 356523600 | last post by:
HTML To PHP Converter 4.2.1.8 Free download Get more for less ... HTML To PHP Converter 4.2.1.8 Get more for less! With a new version of HTML To PHP Converter 4, we've dropped the price and given...
1
by: ansc1 | last post by:
Hello, I'm new to using php coding. I need help with the following: 1. There is a submit button on the form and is saves information to my database. After clicking on "Save Measurement" it...
1
by: sandeepifw | last post by:
plz help I have a php variable $content on page menu.php now i wnt to use its value on page menu_items.js hear menu_items.js create a menubar.its contan both static and dynamic menu my...
10
by: sickboy | last post by:
Hey everyone, I am working on a new site, ForceFedTV.com and I have gotten reports that the site runs great on mac, but once loaded on a pc, after clicking a few links then going back to the home...
0
by: Benjamin Grieshaber | last post by:
Hi, I´m on SuSE 9.3 with xmlrpc-c and xmlrpc-c-devel installed (ver. 0.9.10) I tried to compile php with xmlrpc support and got the following errors: ...
25
by: Mark | last post by:
so, i'm making a website. let's say i have header.php, footer.php and content.php. now in index.php I simply want to include the 3 pages. easy enough to do. but let's say the user navigates to...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.