...

View Full Version : Catch the auto-increment ID



pavmoxo
07-18-2006, 03:38 PM
Hello!!

How can I catch an auto-increment ID in a table with a SQL querie?

For example:

...ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

and I wan´t select the 20. How can I do this??

guelphdad
07-18-2006, 05:13 PM
You don't select it before entering your data you select it afterwards with last_insert_id() and use that before entering data into the next table.

So if table A has the auto increment, perhaps a customerid column and you are then entering information about the newly created customer in a warehouse table you would do something like:



insert
into companytable
(company_name, address)
values
('G.F. Smith and Sons','London')


and now you want to retrieve the companyid to insert into the warehouse table do this:



insert into warehouse
(companyid, warehouselocation)
values
(last_insert_id(),'Ilford')

eak
07-18-2006, 06:41 PM
if you have mysql 5 available, you can find out the auto_increment value this way.


select auto_increment from information_schema.tables where table_name='your_tbl_here'

guelphdad
07-19-2006, 01:02 AM
There shouldn't be a reason to rely on the value of what the auto increment number is, you should be using it as in the example above.

eak
07-19-2006, 01:26 AM
normally, i would agree with you, but I just came across a situation where I needed to find out what the auto_increment value.
When an insert fails (duplicate key for example), mysql_insert_id returns 0. I needed to get the next number so I could still insert data with out duplicate key errors.

guelphdad
07-19-2006, 03:43 AM
I'm not sure how you mean. If the insert failed then obviously you wouldn't be adding values to a second table based on the failure of the data in the first table now would you?

raf
07-19-2006, 08:23 AM
normally, i would agree with you, but I just came across a situation where I needed to find out what the auto_increment value.
When an insert fails (duplicate key for example), mysql_insert_id returns 0. I needed to get the next number so I could still insert data with out duplicate key errors.
i also don't understand that situation. when you have an auto_increment column, you will not be specifying a value for that column when you do an insert, so i don't quite see how you can get duplicates.
+ getting the value doesn't mean that this value wount be used by another instance by the time you make the insert

the only good reason i can think of (to get the next value of an autoincrement) is when you have an rdbm that is able to pre-allocate auto_increment PK values. If the server goes down, then your only option to know what the PK of the next record will be is to request the next auto_increment value.

eak
07-19-2006, 04:27 PM
I'm not sure how you mean. If the insert failed then obviously you wouldn't be adding values to a second table based on the failure of the data in the first table now would you?

I dont think I explained my example well enough.
In my situation, I have one table that holds data about screens. This screens table has a screen_id (auto_increment key).
I needed the option for the user to change the screen_id manually after the screen was created. for example, I create a screen and its id is 1. Then I change its id to 3. I insert another screen (number 2) ok. When I add another screen, which would be 3 due to the auto_increment, it fails due to duplicate keys. In my case, I lookup the auto_increment value after the insert failed and I increase it then try the insert again.

guelphdad
07-19-2006, 06:14 PM
then your table design is incorrect. you should not allow an auto incremented column to be changed at all. the only reason you should be using an auto incremented primary key is where there is no suitable candidate for a primary key in the data you already have. your application should not rely on this primary key other than to provide uniqueness for that row. the user should not even know about or need to use this column.

eak
07-19-2006, 06:26 PM
then your table design is incorrect. you should not allow an auto incremented column to be changed at all. the only reason you should be using an auto incremented primary key is where there is no suitable candidate for a primary key in the data you already have. your application should not rely on this primary key other than to provide uniqueness for that row. the user should not even know about or need to use this column.


I understand that. I originally was going to have a screen_id (auto_increment key) and screen_number (int) so the user could still change the screen_number.

The only reason I did it the way I did was to see if it could be done with MySQL using 1 column instead of 2. It was more of an experiment than anything else.

raf
07-20-2006, 07:17 AM
I needed the option for the user to change the screen_id manually after the screen was created. for example, I create a screen and its id is 1. Then I change its id to 3. I insert another screen (number 2) ok. When I add another screen, which would be 3 due to the auto_increment, it fails due to duplicate keys. In my case, I lookup the auto_increment value after the insert failed and I increase it then try the insert again.


I understand that. I originally was going to have a screen_id (auto_increment key) and screen_number (int) so the user could still change the screen_number.

The only reason I did it the way I did was to see if it could be done with MySQL using 1 column instead of 2. It was more of an experiment than anything else.
so basically, you are just wasting our time here?

you want an auto_increment that shouldn't automatically increment and which values you want to update after the records are created, and where you fill up the gaps to keep a continuous serie?
What's the experiment here: these can all be done with a few simple querys and recordset processing? and what has this actually got to do with auto_increments or even getting the next value of the auto_increment? Why not simply get the highest value like
select max(colname) from tablename
which you then increment for your insert?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum