Hi,
I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin 9 1744
On Jul 26, 2:35*pm, KDawg44 <KDaw...@gmail. comwrote:
Hi,
I have PHP function that adds a record to the database. *The table has
an ID that is AUTO_INCREMENT. *Is there anyway to get that ID back
when I do any kind of insert? *That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin
I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html
INSERT INTO foo (auto,text)
VALUES(NULL,'te xt'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:
USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second
table records various values for that record.
Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo
Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I
did something like:
INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID( )
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
On Jul 26, 2:47*pm, KDawg44 <KDaw...@gmail. comwrote:
On Jul 26, 2:35*pm, KDawg44 <KDaw...@gmail. comwrote:
Hi,
I have PHP function that adds a record to the database. *The table has
an ID that is AUTO_INCREMENT. *Is there anyway to get that ID back
when I do any kind of insert? *That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin
I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
INSERT INTO foo (auto,text)
* * VALUES(NULL,'te xt'); * * * * # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
* * VALUES(LAST_INS ERT_ID(),'text' ); *# use ID in second table
but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:
USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second
table records various values for that record.
Table 1
ItemID *ItemName ItemDesc
1 * * * *Foo * * *Bar
2 * * * *Bar * * *Foo
Table 2
ID * * *ValueName * * Value * Table1ID
1 * * * * *A * * * * * *5 * * * * 1
2 * * * * *B * * * * * *5 * * * * 1
3 * * * * *A * * * * * *5 * * * * 2
4 * * * * *C * * * * * *5 * * * * 1
5 * * * * *B * * * * * *5 * * * * 2
So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). *It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I
did something like:
INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID( )
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
Well, I see there is a mysql_insert_id () PHP function that returns
what I need and I am assuming that since i doing this immediately
after the INSERT statement that chances of another insert occurring in
between (especially since this is the only function inserting into
these two tables) is pretty slim.
Anyone seeing please faults in my logic, please let me know.
Thanks.
Kevin
Message-ID:
<c2************ *************** *******@r66g200 0hsg.googlegrou ps.comfrom
KDawg44 contained the following:
>I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign key that I need to do an insert on afterwards.
If it's MySql there is this http://uk.php.net/mysql_insert_id
--
Geoff Berrow 011000100110110 0010000000110
001101101011011 001000110111101 100111001011
100110001101101 111001011100111 010101101011 http://slipperyhill.co.uk
KDawg44 wrote:
On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail. comwrote:
>Hi,
I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign key that I need to do an insert on afterwards.
Thanks.
Kevin
I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html
INSERT INTO foo (auto,text)
VALUES(NULL,'te xt'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:
USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second
table records various values for that record.
Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo
Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I
did something like:
INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID( )
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
If I need to do multiple inserts into multiple tables using a web
connection, I tend to incorporate all of the respective inserts for the
logical transaction into a stored procedure where I guarantee that the
connection being used for the subsequent inserts are the same as the
connection for the initial connection and therefore guarantee that
last_id is truly the last_id this connection executed. I do this to
ensure that the complete transaction is autonomous - and if the complete
transaction fails - it all gets rolled back.
There are many schools of thought on this, this is just how I prefer to
execute this type of transaction. YMMV.
KDawg44 wrote:
On Jul 26, 2:47 pm, KDawg44 <KDaw...@gmail. comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail. comwrote:
>>Hi,
I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign key that I need to do an insert on afterwards.
Thanks.
Kevin
I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
INSERT INTO foo (auto,text) VALUES(NULL,'te xt'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at almost the same time, it is possible (thought I do not know how likely) that it could go something like this:
USER 1 INSERT USER 2 INSERT USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second table records various values for that record.
Table 1 ItemID ItemName ItemDesc 1 Foo Bar 2 Bar Foo
Table 2 ID ValueName Value Table1ID 1 A 5 1 2 B 5 1 3 A 5 2 4 C 5 1 5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and the Values (array). It then performs an insert into Table 1, then loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I did something like:
INSERT INTO Table1 $insertID = SELECT LAST_INSERT_ID( ) Loop through array INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
Well, I see there is a mysql_insert_id () PHP function that returns
what I need and I am assuming that since i doing this immediately
after the INSERT statement that chances of another insert occurring in
between (especially since this is the only function inserting into
these two tables) is pretty slim.
Anyone seeing please faults in my logic, please let me know.
Thanks.
Kevin
Read the notes here: http://dev.mysql.com/doc/refman/5.0/...last-insert-id
"The ID that was generated is maintained in the server on a
/per-connection basis/. ....."
--
*************** **************
Chuck Anderson • Boulder, CO http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
*************** *************** *****
..oO(KDawg44)
>I found this documentation at http://dev.mysql.com/doc/refman/5.0/...unique-id.html
INSERT INTO foo (auto,text)
VALUES(NULL,'te xt'); # generate ID by inserting NULL INSERT INTO foo2 (id,text)
VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at almost the same time, it is possible (thought I do not know how likely) that it could go something like this:
USER 1 INSERT USER 2 INSERT USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second table records various values for that record.
LAST_INSERT_ID( ) is connection-specific. It will return the correct
value, even if multiple users insert records at the same time.
You might however get wrong results if you would do it like this:
INSERT INTO foo ... -- insert new record
SELECT MAX(id) FROM foo -- get highest ID
In this case you have a nice race condition and might run into trouble
with multiple parallel inserts. But if you do it the right way with
LAST_INSERT_ID( ) or its PHP counterpart you'll be safe.
Micha
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail. comwrote:
On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail. comwrote:
Hi,
I have PHP function that adds a record to the database. The table has
an ID that is AUTO_INCREMENT. Is there anyway to get that ID back
when I do any kind of insert? That ID is a foreign key that I need to
do an insert on afterwards.
Thanks.
Kevin
I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
INSERT INTO foo (auto,text)
VALUES(NULL,'te xt'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at
almost the same time, it is possible (thought I do not know how
likely) that it could go something like this:
USER 1 INSERT
USER 2 INSERT
USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second
table records various values for that record.
Table 1
ItemID ItemName ItemDesc
1 Foo Bar
2 Bar Foo
Table 2
ID ValueName Value Table1ID
1 A 5 1
2 B 5 1
3 A 5 2
4 C 5 1
5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and
the Values (array). It then performs an insert into Table 1, then
loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I
did something like:
INSERT INTO Table1
$insertID = SELECT LAST_INSERT_ID( )
Loop through array
INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.
Hello,
Gordon wrote:
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail. comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail. comwrote:
>>Hi, I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign key that I need to do an insert on afterwards. Thanks. Kevin
Skipping the other comments that were made I assume you only need the
PHP/MySQL function "mysql_insert_i d()". It retrieves the ID generated
for an AUTO_INCREMENT column by the previous INSERT query. Also check
out the notes manual page as they might have an answer to your race
condition problem.
>I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
INSERT INTO foo (auto,text) VALUES(NULL,'te xt'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at almost the same time, it is possible (thought I do not know how likely) that it could go something like this:
USER 1 INSERT USER 2 INSERT USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second table records various values for that record.
Table 1 ItemID ItemName ItemDesc 1 Foo Bar 2 Bar Foo
Table 2 ID ValueName Value Table1ID 1 A 5 1 2 B 5 1 3 A 5 2 4 C 5 1 5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and the Values (array). It then performs an insert into Table 1, then loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I did something like:
INSERT INTO Table1 $insertID = SELECT LAST_INSERT_ID( ) Loop through array INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.
- Jensen
Gordon wrote:
On Jul 26, 7:47 pm, KDawg44 <KDaw...@gmail. comwrote:
>On Jul 26, 2:35 pm, KDawg44 <KDaw...@gmail. comwrote:
>>Hi, I have PHP function that adds a record to the database. The table has an ID that is AUTO_INCREMENT. Is there anyway to get that ID back when I do any kind of insert? That ID is a foreign key that I need to do an insert on afterwards. Thanks. Kevin
I found this documentation athttp://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
INSERT INTO foo (auto,text) VALUES(NULL,'te xt'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INS ERT_ID(),'text' ); # use ID in second table
but my concern is that if two different people call this function at almost the same time, it is possible (thought I do not know how likely) that it could go something like this:
USER 1 INSERT USER 2 INSERT USER 1 RETRIEVE LAST_INSERT_ID( )
What happens is that the first table creates a record and the second table records various values for that record.
Table 1 ItemID ItemName ItemDesc 1 Foo Bar 2 Bar Foo
Table 2 ID ValueName Value Table1ID 1 A 5 1 2 B 5 1 3 A 5 2 4 C 5 1 5 B 5 2
So the PHP function performs gets passed the ItemName, Item Desc, and the Values (array). It then performs an insert into Table 1, then loops through the values array, inserting each into Table 2.
So what are the chances I can have the problem described above if I did something like:
INSERT INTO Table1 $insertID = SELECT LAST_INSERT_ID( ) Loop through array INSERT INTO Table2 $valueName, $value, $insertID
Thanks.
Kevin
This is a classic programming problem, known as a race condition. In
the database world, the mechanism for working around race conditions
is the ACID transaction. Unfortunately, the default MySQL table type
(myISAM) doesn't support them. If you are using InnoDB tables, then
you can use BEGIN TRANSACTION at the beginning of your operation and
COMMIT at the end to make absolutely sure that you get the correct
insert ID back.
Not at all. mysql_last_inse rt_id() or LAST_INSERT_ID( ) is connection
specific. It will never get the id of an insert from another
connection. Transactions are not necessary to get the correct insert id.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp. js*******@attgl obal.net
=============== === This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: das dsf |
last post by:
Hi there!
I have both PHP 4.0( RPM) installed and MySQL 4.0.20 installed with the
mysqld daemon up and running. But when I look at the output of phpinfo() ,
I do not see it there, which is worrisome and so I tried to recompile PHP
and do ./configure with the --with-mysql=path but I don't really know
where my mysql's header files should be , is there any way to find that
out by using any of the mysql commands?
I did try to do...
|
by: Jason Reljac |
last post by:
Howdy,
I'm looking for a little help.
I had a W2K server with Apache/PHP/mySQL installed and working. I had
a specific page that ran a mySQL SELECT INTO OUTFILE and dumped the
outfile into a web accessible directory. Now mySQL has been moved to
another server and my SELECT INTO OUTFILE no longer works.
mySQL help tells me SELECT INTO OUTFILE only can create a file on the
local system.
|
by: Jeff Harbin |
last post by:
I've spent the last 2 days trying to begin using mysql. I've purchased 3
books and read huge chunks of the documentation provided with the mysql zip
file.
I've downloaded 'mysql-4.0.18-win.zip', unzipped it, run the WinMySqlAdmin
program, and everything seems fine. I'm trying to use it on WindowsXP on a
new machine.
I'm trying to learn mysql from scratch using the command line before I play
with what I'm assuming are GUI for mysql. ...
|
by: Jason Reljac |
last post by:
Howdy,
I'm looking for a little help.
I had a W2K server with Apache/PHP/mySQL installed and working. I had
a specific page that ran a mySQL SELECT INTO OUTFILE and dumped the
outfile into a web accessible directory. Now mySQL has been moved to
another server and my SELECT INTO OUTFILE no longer works.
mySQL help tells me SELECT INTO OUTFILE only can create a file on the
local system.
|
by: Chefry |
last post by:
I'm trying to set up an off the shelf script and keep getting an error.
My host set up the mysql on my site and I changed the variables I had to in the
settings.php file but I keep getting the following errors
Warning: mysql_pconnect(): Access denied for user: 'ODBC@localhost' (Using password: NO)
in f:\wwwroot\professionalchef\Back\JobsOnline\mysql.php on line 121
Warning: mysql_pconnect(): Access denied for user: 'ODBC@localhost'...
| |
by: Erik H. |
last post by:
Trying to connect to MySQL db on localhost, and populate datagrid from a
dataset using code inline method.
Getting the following compile error:
Error Message: "CS0246: The type or namespace name 'CoreLab' could not be
found (are you missing a using directive or an assembly reference?)"
I downloaded the add on for connecting to MySQL using ADO.NET and installed
it, but am still getting this error. Anybody ever run into this before?
|
by: Greg Noss |
last post by:
I'm trying to install MySQL. During the security settings portion fo the
setup. I keep getting a Connection Error.
Error Nr. 1045
Access denied for user'root'@'localhost'(using password:YES)
........
I had Zonealarm running. Changed the trusted zone security setting to
low(disabled) then selected "retry" Failed again.
|
by: h4xPace |
last post by:
I am building a MySQL query application, and I have run into a small
snag. MySQL has released a set of classes that extend the .NET
framework base data classes (command, connection, etc), and I am using
them to interact with the MySQL server (on localhost). Everything
works great on that side of the aisle.
However, I have never worked with getting schema from a database
before, so I am fumbling around for a workable solution to doing...
|
by: ist |
last post by:
Hi,
I am trying to get (and transfer over ASP.NET) some encrypted data
from some MySQL fields. Since the data contains many unicode
characters, I tried to get the data as a series of ASCII values,
transfer those numeric values over ASP.NET. I had no problem doing
this on my local computer, by getting the field with "cast(field as
BINARY)" so that on ASP.NET I have a byte array.Then send every
field of array over ASP.Net.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |