Page 1 of 1

Performing database transaction

Posted: Sun Apr 29, 2007 8:23 pm
by jlising
Hi!

I created a booking registration form the users fills up. Each information entered must be inserted in the database with a unique id. I formulated an id like BK00001-07.

WHERE:
BK is the prefix
00001 is a series that is stored in the database
07 is the current year

Before inserting the record, I get the next counter in the table named ba_booknoprefix. But since the script is for multiple users,I believe duplicate ids are possible to generate. How can I avoid this scenario? Can I use database transactions? Please give me example scripts.

Thank you!

Posted: Sun Apr 29, 2007 8:47 pm
by Christopher
I assume you are using MySQL (by your signature), but you should post which database you are using. You might want to use and auto_increment field for the unique key and then get the last insert ID to get the number from which to build your booking registration ID.

Posted: Sun Apr 29, 2007 8:58 pm
by jlising
Thank you arborint!

Yes I am using MySQL. As my client required, I am not allowed to use auto increment instead, use the format with BK prefix, series and 2 digit current year. As now, I have a separate table for the counter which I update every record is inserted. Is there another efficient way to solve this?

Thanks again!

Posted: Mon Apr 30, 2007 3:29 pm
by Ambush Commander
'SELECT FOR UPDATE' should do the trick. The key table is actually a pretty good solution if you're not able to use the built-in database counter, although keeping it encapsulated in a class would probably be a good idea to make sure that you can swap algorithms if necessary.