473,893 Members | 1,630 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

reading auto increment number before it is written?

Ok, this is a stupid problem, I admit. I have a scrip that adds records to
a table. The records are for photos. So, after adding a record the scrip
also uploads a picture from users computer and renames it to
{$recordID}.jpg .

The problem is, my recordID field is auto incrementing in mySQL. So
currently I just take the last added record and assume that it the one I
just added, and rename the file to the recordID of that record. It works,
but clearly isn't the best way to do this as 2 users can add records at the
same time and then I would have a problem.

So what I am wondering is if there is a way to write a record to mySQL and
then have mySQL return recordID of the record just written?

- Bogdan
Jul 14 '06 #1
2 3069
>From the docs:

LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column. See section 8.1.3.130 mysql_insert_id ().
mysqlSELECT LAST_INSERT_ID( );
-195

The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will
not even be changed if you update another AUTO_INCREMENT column with a
non-magic value (that is, a value that is not NULL and not 0). If you
insert many rows at the same time with an insert statement,
LAST_INSERT_ID( ) returns the value for the first inserted row. The
reason for this is to make it possible to easily reproduce the same
INSERT statement against some other server. If expr is given as an
argument to LAST_INSERT_ID( ), then the value of the argument is
returned by the function, and is set as the next value to be returned
by LAST_INSERT_ID( ). This can be used to simulate sequences: First
create the table:
mysqlCREATE TABLE sequence (id INT NOT NULL);
mysqlINSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:
mysqlUPDATE sequence SET id=LAST_INSERT_ ID(id+1);

You can generate sequences without calling LAST_INSERT_ID( ), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated value
(multi-user safe). You can retrieve the new ID as you would read any
normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID( )
(without an argument) will return the new ID. The C API function
mysql_insert_id () can also be used to get the value. Note that as
mysql_insert_id () is only updated after INSERT and UPDATE statements,
so you can't use the C API function to retrieve the value for
LAST_INSERT_ID( expr) after executing other SQL statements like SELECT
or SET.

x0054 wrote:
Ok, this is a stupid problem, I admit. I have a scrip that adds records to
a table. The records are for photos. So, after adding a record the scrip
also uploads a picture from users computer and renames it to
{$recordID}.jpg .

The problem is, my recordID field is auto incrementing in mySQL. So
currently I just take the last added record and assume that it the one I
just added, and rename the file to the recordID of that record. It works,
but clearly isn't the best way to do this as 2 users can add records at the
same time and then I would have a problem.

So what I am wondering is if there is a way to write a record to mySQL and
then have mySQL return recordID of the record just written?

- Bogdan
Jul 14 '06 #2
"ImOk" <jo**********@g mail.comwrote in
news:11******** **************@ m79g2000cwm.goo glegroups.com:
>>From the docs:

LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column. See section 8.1.3.130 mysql_insert_id ().
mysqlSELECT LAST_INSERT_ID( );
-195

The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It
will not even be changed if you update another AUTO_INCREMENT column
with a non-magic value (that is, a value that is not NULL and not 0).
If you insert many rows at the same time with an insert statement,
LAST_INSERT_ID( ) returns the value for the first inserted row. The
reason for this is to make it possible to easily reproduce the same
INSERT statement against some other server. If expr is given as an
argument to LAST_INSERT_ID( ), then the value of the argument is
returned by the function, and is set as the next value to be returned
by LAST_INSERT_ID( ). This can be used to simulate sequences: First
create the table:
mysqlCREATE TABLE sequence (id INT NOT NULL);
mysqlINSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:
mysqlUPDATE sequence SET id=LAST_INSERT_ ID(id+1);

You can generate sequences without calling LAST_INSERT_ID( ), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated value
(multi-user safe). You can retrieve the new ID as you would read any
normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID( )
(without an argument) will return the new ID. The C API function
mysql_insert_id () can also be used to get the value. Note that as
mysql_insert_id () is only updated after INSERT and UPDATE statements,
so you can't use the C API function to retrieve the value for
LAST_INSERT_ID( expr) after executing other SQL statements like SELECT
or SET.

x0054 wrote:
>Ok, this is a stupid problem, I admit. I have a scrip that adds
records to a table. The records are for photos. So, after adding a
record the scrip also uploads a picture from users computer and
renames it to {$recordID}.jpg .

The problem is, my recordID field is auto incrementing in mySQL. So
currently I just take the last added record and assume that it the
one I just added, and rename the file to the recordID of that record.
It works, but clearly isn't the best way to do this as 2 users can
add records at the same time and then I would have a problem.

So what I am wondering is if there is a way to write a record to
mySQL and then have mySQL return recordID of the record just written?

- Bogdan

Thank you very much, that's exactly what I was looking for.

- Bogdan
Jul 17 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2062
by: opt_inf_env | last post by:
Hello, I would like to solve the following problem. On the server side I have a file with a sequence of natural numbers (1, 2, 3, 4, 5, ...., n). Each user, after some action, adds new number (n+1, where n is the last number in the file) to the end of file. To do this user has to open the file for reading, read the last number, perform corresponding manipulations with the extracted last number and add result to the end of the file...
2
12724
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error telling me that "Number of query values and destination fields are not the same." If I add a value for the auto increment field to the SQL String the data is entered into the table with no problems but obviously the auto increment field now...
6
5078
by: Alpha | last post by:
I retrieve a table with only 2 columns. One is a auto-generated primary key column and the 2nd is a string. When I add a new row to the dataset to be updated back to the database. What should I do with the 1st column ? (Below I have a "1" in place for now). Also, Does the datase.AcceptChanges(); updates the changes to the database? Which command do I use to update the changes in dataset back to the Access database table? Thanks, Alpha...
1
1551
by: John | last post by:
Hi I am using Application.ProductVersion.ToString to get the app build number but the problem is that it does not auto increment. I have even replaced the content of AssemblyInfo.vb file from a new project in case it was corrupt or something but no luck. What elements effect the auto-increment of ProductVersion so I can check those? Thanks
5
5124
by: vul | last post by:
In VB6 there is Auto Increment check box in Project Properties, which allow you have a new version every time you compile the project. Is there any easy way to have this feature in VB 2005? Some of my blocks of code check and compare versions of EXE and DLLs located on the local workstation and the server. So it's very important for me to have an incremented number in case the project was rebuilt. It can be done manually, of course, but I...
13
4728
by: S.Dickson | last post by:
I had an access database that i use as an ordering system. I have a form for entering customer details. When i add a new customer on the form the customer number is an auto number that appears when i type in the details. I have just moved over to mysql server with access as the front end. I have setup the sql tables with the customer number as autonumber. When i go into the form and add a new customer it does not generate the
1
3131
by: rhepsi | last post by:
hii all, i have created a database with a table with number of fields....in msaccess ive set primary key as SNo: and its datatype as auto number... How to set the auto increment ???? For ex:
3
2186
by: Wayne L | last post by:
Ok now everyone has mentioned not to use auto number if it means anything to the user. My application uses the auto number for exporting only. I append the mastertbl column with my starting number of 0001(4 digits) (prior to populating the table). When information is imported and appended via a query to the mastertbl it will start with 0002 and increment. All tables used are cleared with a delete query after being exported, this allows the...
5
8112
by: David Wright | last post by:
Hello Everyone I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the incremented number would help to reduce errors and make data entry simpler. The next record in the Test_SrNo field should not have any entry in it until the text insertion point enters the Test_SrNo field. Example: If the last test serial number...
0
9985
marktang
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...
0
9832
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,...
0
10839
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10927
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9645
agi2029
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...
1
8022
isladogs
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...
0
6066
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4684
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
3
3289
bsmnconsultancy
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...

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.