Creating alphanumeric id for a table

I want to create user generated id like this :
AAA0001
AAA0002
....
AAA0009
AAA0010

where the id consists of 3 alphanumeric characters and 4 numerical digits in
the beginning (for numerical digit, it can grow like this AAA10001). I try
to use php script to generate id like this, where I use the following
script.

function generate_id($num) {
$start_dig = 4;
$num_dig = strlen($num);

$id = $num;
if($num_dig <= $start_dig) {
$num_zero = $start_dig - $num_dig;

for($i=0;$i< $num_zero; $i++) {
$id = '0' . $id;
}
}
$id = 'AAA' . $id;
return $id;
}

$app_id = generate_id(1);

?>

I assume that I can get increment value/sequence from db (I used harcoded
increment value in the code above (generate_id(1))),
but I don't know how I can get this incremental value from db.I use mysql
5.0.
Or has anyone had another solution to create this alphanumeric id ?

Any help would be much appreciated
Thanks
--
View this message in context: http://www.nabble.com/Creating-alphanumeric-id-for-a-table-t p25391939p25391939.html
Sent from the PHP - General mailing list archive at Nabble.com.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

> I assume that I can get increment value/sequence from db =A0(I used harco=
ded
> increment value =A0in the code above (generate_id(1))),
> but I don't know how I can get this incremental value from db.I use mysql
> 5.0.

If you're thinking of retrieving the newest value of an AUTO_INCREMENT
column, immediately after inserting a row, there are different ways to
do this depending on how you're connecting to MySQL.

PDO, for example, has a method called lastInsertId():
http://us2.php.net/manual/en/pdo.lastinsertid.php

And the mysql_* family of functions has mysql_insert_id(), etc.

Ben

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

Note that this approach has risks around race conditions. Anytime you
have a construct for the id you run the risk of having it create
duplicate ids. You will need to handle that.

Bastien

Sent from my iPod

On Sep 10, 2009, at 6:49 PM, Ben Dunlap
wrote:

>> I assume that I can get increment value/sequence from db (I used
>> harcoded
>> increment value in the code above (generate_id(1))),
>> but I don't know how I can get this incremental value from db.I use
>> mysql
>> 5.0.
>
> If you're thinking of retrieving the newest value of an AUTO_INCREMENT
> column, immediately after inserting a row, there are different ways to
> do this depending on how you're connecting to MySQL.
>
> PDO, for example, has a method called lastInsertId():
> http://us2.php.net/manual/en/pdo.lastinsertid.php
>
> And the mysql_* family of functions has mysql_insert_id(), etc.
>
> Ben
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

On Fri, 2009-09-11 at 00:19 -0400, Phpster wrote:
> Note that this approach has risks around race conditions. Anytime you
> have a construct for the id you run the risk of having it create
> duplicate ids. You will need to handle that.
>
> Bastien
>
> Sent from my iPod
>
> On Sep 10, 2009, at 6:49 PM, Ben Dunlap
> wrote:
>
> >> I assume that I can get increment value/sequence from db (I used
> >> harcoded
> >> increment value in the code above (generate_id(1))),
> >> but I don't know how I can get this incremental value from db.I use
> >> mysql
> >> 5.0.
> >
> > If you're thinking of retrieving the newest value of an AUTO_INCREMENT
> > column, immediately after inserting a row, there are different ways to
> > do this depending on how you're connecting to MySQL.
> >
> > PDO, for example, has a method called lastInsertId():
> > http://us2.php.net/manual/en/pdo.lastinsertid.php
> >
> > And the mysql_* family of functions has mysql_insert_id(), etc.
> >
> > Ben
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
Not if you use the functions built specifically for that, i.e. the ones
that Ben mentioned. In no circumstances should you ever do a "SELECT
MAX(id) FROM table". I've seen this done so many times by people who
should know better, and they even saw no problems when they ran tests
(that was, they tested the systems themselves, and only themselves) but
then it all fell down as soon as multiple users tried to use it.

Thanks,
Ash
http://www.ashleysheridan.co.uk




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

aveev wrote:
> > function generate_id($num) {
> $start_dig = 4;
> $num_dig = strlen($num);
>
> $id = $num;
> if($num_dig <= $start_dig) {
> $num_zero = $start_dig - $num_dig;
>
> for($i=0;$i< $num_zero; $i++) {
> $id = '0' . $id;
> }
> }
> $id = 'AAA' . $id;
> return $id;
> }
>
> $app_id = generate_id(1);
>
> ?>

Your function can be reduced to a one-liner:

function generate_id($num) {
return 'AAA' . str_pad(strval($num), 4, '0', STR_PAD_LEFT);
}

If the prefix is always 'AAA' you should consider to use a numeric ID
for your database and let your database generate the autoincrement id
and use this function just for display.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

On Sep 11, 2009, at 3:50 AM, Ashley Sheridan
wrote:

> On Fri, 2009-09-11 at 00:19 -0400, Phpster wrote:
>> Note that this approach has risks around race conditions. Anytime you
>> have a construct for the id you run the risk of having it create
>> duplicate ids. You will need to handle that.
>>
>> Bastien
>>
>> Sent from my iPod
>>
>> On Sep 10, 2009, at 6:49 PM, Ben Dunlap
>> wrote:
>>
>>>> I assume that I can get increment value/sequence from db (I used
>>>> harcoded
>>>> increment value in the code above (generate_id(1))),
>>>> but I don't know how I can get this incremental value from db.I use
>>>> mysql
>>>> 5.0.
>>>
>>> If you're thinking of retrieving the newest value of an
>>> AUTO_INCREMENT
>>> column, immediately after inserting a row, there are different
>>> ways to
>>> do this depending on how you're connecting to MySQL.
>>>
>>> PDO, for example, has a method called lastInsertId():
>>> http://us2.php.net/manual/en/pdo.lastinsertid.php
>>>
>>> And the mysql_* family of functions has mysql_insert_id(), etc.
>>>
>>> Ben
>>>
>>> --
>>> PHP General Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>
> Not if you use the functions built specifically for that, i.e. the
> ones
> that Ben mentioned. In no circumstances should you ever do a "SELECT
> MAX(id) FROM table". I've seen this done so many times by people who
> should know better, and they even saw no problems when they ran tests
> (that was, they tested the systems themselves, and only themselves)
> but
> then it all fell down as soon as multiple users tried to use it.
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>


I totally agree. Ufortunately, my boss would not agree and the one app
I have to support is full of race conditions. I told them from the
beginning that it would not work and under any load ( btw the max app
load is about 40 people on a server, don't ask, it would make me sad)
we get errors in the app. I get to spend a few hours a week fixing
this crap!

I did just want to point out that there is risk in not using auto
numbers or other unique id mechanisms if you don't pay attention.
>


Bastien

Sent from my iPod

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

At 3:17 PM -0700 9/10/09, aveev wrote:
>I want to create user generated id like this :
>AAA0001
>AAA0002
>...
>AAA0009
>AAA0010
>
>where the id consists of 3 alphanumeric characters and 4 numerical digits in
>the beginning (for numerical digit, it can grow like this AAA10001). I try
>to use php script to generate id like this, where I use the following
>script.
>
> > function generate_id($num) {
> $start_dig = 4;
> $num_dig = strlen($num);
>
> $id = $num;
> if($num_dig <= $start_dig) {
> $num_zero = $start_dig - $num_dig;
>
> for($i=0;$i< $num_zero; $i++) {
> $id = '0' . $id;
> }
> }
> $id = 'AAA' . $id;
> return $id;
> }
>
> $app_id = generate_id(1);
>
>?>
>
>I assume that I can get increment value/sequence from db (I used harcoded
>increment value in the code above (generate_id(1))),
>but I don't know how I can get this incremental value from db.I use mysql
>5.0.
>Or has anyone had another solution to create this alphanumeric id ?
>
>Any help would be much appreciated
>Thanks

aveev:

Why get an incremental value from the database? What information does
that give you? Why do you want it? And what are you going to do with
it?

When those questions are answered, then we can help with more informed advice.

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

I agree that this question could be just "how to create an unique ID with
leading letters like 'AAA'.

At that point I want to mention that a timestamp does garanty a unique
number at almost 100% formated with the given samples in the other posts
will do the job.

"tedd" wrote in message
news:p06240805c6cff752b9f7@[192.168.1.102]...
> At 3:17 PM -0700 9/10/09, aveev wrote:
> >I want to create user generated id like this :
> >AAA0001
> >AAA0002
> >...
> >AAA0009
> >AAA0010
> >
> >where the id consists of 3 alphanumeric characters and 4 numerical digits
in
> >the beginning (for numerical digit, it can grow like this AAA10001). I
try
> >to use php script to generate id like this, where I use the following
> >script.
> >
> > > > function generate_id($num) {
> > $start_dig = 4;
> > $num_dig = strlen($num);
> >
> > $id = $num;
> > if($num_dig <= $start_dig) {
> > $num_zero = $start_dig - $num_dig;
> >
> > for($i=0;$i< $num_zero; $i++) {
> > $id = '0' . $id;
> > }
> > }
> > $id = 'AAA' . $id;
> > return $id;
> > }
> >
> > $app_id = generate_id(1);
> >
> >?>
> >
> >I assume that I can get increment value/sequence from db (I used
harcoded
> >increment value in the code above (generate_id(1))),
> >but I don't know how I can get this incremental value from db.I use mysql
> >5.0.
> >Or has anyone had another solution to create this alphanumeric id ?
> >
> >Any help would be much appreciated
> >Thanks
>
> aveev:
>
> Why get an incremental value from the database? What information does
> that give you? Why do you want it? And what are you going to do with
> it?
>
> When those questions are answered, then we can help with more informed
advice.
>
> Cheers,
>
> tedd
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

You can use mysql's autoincrement ,but you must do some php for
special display,follow is my method.

1.create an int or smallint mysql col.
2.set it autoincrement.
3.insert some dates to database.
4.select dates and display with letter prefix.
for example: 12->AAA0012,AAA1234,
you can make an php functions to change num to prefix letter .
If you want letter autoincrement like int vars create an 26 based
calculate function for mapping number to letters.
ex:
10001->AAB0001
20001->AAC0001

2009/9/11 Phpster :
>
>
>
>
> On Sep 11, 2009, at 3:50 AM, Ashley Sheridan
> wrote:
>
>> On Fri, 2009-09-11 at 00:19 -0400, Phpster wrote:
>>>
>>> Note that this approach has risks around race conditions. Anytime you
>>> have a construct for the id you run the risk of having it create
>>> duplicate ids. You will need to handle that.
>>>
>>> Bastien
>>>
>>> Sent from my iPod
>>>
>>> On Sep 10, 2009, at 6:49 PM, Ben Dunlap
>>> wrote:
>>>
>>>>> I assume that I can get increment value/sequence from db =A0(I used
>>>>> harcoded
>>>>> increment value =A0in the code above (generate_id(1))),
>>>>> but I don't know how I can get this incremental value from db.I use
>>>>> mysql
>>>>> 5.0.
>>>>
>>>> If you're thinking of retrieving the newest value of an AUTO_INCREMENT
>>>> column, immediately after inserting a row, there are different ways to
>>>> do this depending on how you're connecting to MySQL.
>>>>
>>>> PDO, for example, has a method called lastInsertId():
>>>> http://us2.php.net/manual/en/pdo.lastinsertid.php
>>>>
>>>> And the mysql_* family of functions has mysql_insert_id(), etc.
>>>>
>>>> Ben
>>>>
>>>> --
>>>> PHP General Mailing List (http://www.php.net/)
>>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>>
>>>
>> Not if you use the functions built specifically for that, i.e. the ones
>> that Ben mentioned. In no circumstances should you ever do a "SELECT
>> MAX(id) FROM table". I've seen this done so many times by people who
>> should know better, and they even saw no problems when they ran tests
>> (that was, they tested the systems themselves, and only themselves) but
>> then it all fell down as soon as multiple users tried to use it.
>>
>> Thanks,
>> Ash
>> http://www.ashleysheridan.co.uk
>>
>>
>
>
> I totally agree. Ufortunately, my boss would not agree and the one app I
> have to support is full of race conditions. I told them from the beginnin=
g
> that it would not work and under any load ( btw the max app load is about=
40
> people on a server, don't ask, it would make me sad) we get errors in the
> app. I get to spend a few hours a week fixing this crap!
>
> I did just want to point out that there is risk in not using auto numbers=
or
> other unique id mechanisms if you don't pay attention.
>>
>
>
> Bastien
>
> Sent from my iPod
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

I don´t know if it´d be the right way, but what about using trigger in this
case?


"aveev" escreveu na mensagem
news:25391939.post@talk.nabble.com...
>
> I want to create user generated id like this :
> AAA0001
> AAA0002
> ...
> AAA0009
> AAA0010
>
> where the id consists of 3 alphanumeric characters and 4 numerical digits
> in
> the beginning (for numerical digit, it can grow like this AAA10001). I try
> to use php script to generate id like this, where I use the following
> script.
>
> > function generate_id($num) {
> $start_dig = 4;
> $num_dig = strlen($num);
>
> $id = $num;
> if($num_dig <= $start_dig) {
> $num_zero = $start_dig - $num_dig;
>
> for($i=0;$i< $num_zero; $i++) {
> $id = '0' . $id;
> }
> }
> $id = 'AAA' . $id;
> return $id;
> }
>
> $app_id = generate_id(1);
>
> ?>
>
> I assume that I can get increment value/sequence from db (I used harcoded
> increment value in the code above (generate_id(1))),
> but I don't know how I can get this incremental value from db.I use mysql
> 5.0.
> Or has anyone had another solution to create this alphanumeric id ?
>
> Any help would be much appreciated
> Thanks
> --
> View this message in context:
> http://www.nabble.com/Creating-alphanumeric-id-for-a-table-t p25391939p25391939.html
> Sent from the PHP - General mailing list archive at Nabble.com.
>
>



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

aveev wrote:
> where the id consists of 3 alphanumeric characters and 4 numerical digits in
> the beginning (for numerical digit, it can grow like this AAA10001). I try
> to use php script to generate id like this, where I use the following
> script.
>
> > function generate_id($num) {
> $start_dig = 4;
> $num_dig = strlen($num);
>
> $id = $num;
> if($num_dig <= $start_dig) {
> $num_zero = $start_dig - $num_dig;
>
> for($i=0;$i< $num_zero; $i++) {
> $id = '0' . $id;
> }
> }
> $id = 'AAA' . $id;
> return $id;
> }

You can create a stored procedure or a trigger on inserting that
implement your id logic.

--
Mickaël Wolff aka Lupus Michaelis
http://lupusmic.org

Seeking for a position

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

The reason why I used incremental value from the db is that I need a sequence
number generator that can be used as an argument to my function. If I can
generate sequence number myself, I don't need this incremental value from
db.
What I want is that the sequential alphanumeric id that increments it's
numeric value by one everytime a new record is inserted while the alphabet
part remains the same..(like I've shown before):
AAA0001
AAA0002
....
AAA0009
AAA0010
This id will be used as the application number that will be printed as a
barcode on the user's application form. The requirement says that the app
number is prefixed with the letters (actually it's not 'AAA' but 'FIN' which
stands for File Identification Number)....



tedd-2 wrote:
>
>
> aveev:
>
> Why get an incremental value from the database? What information does
> that give you? Why do you want it? And what are you going to do with
> it?
>
> When those questions are answered, then we can help with more informed
> advice.
>
> Cheers,
>
> tedd
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

--
View this message in context: http://www.nabble.com/Creating-alphanumeric-id-for-a-table-t p25391939p25411503.html
Sent from the PHP - General mailing list archive at Nabble.com.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

At 9:19 PM -0700 9/11/09, aveev wrote:
>The reason why I used incremental value from the db is that I need a sequence
>number generator that can be used as an argument to my function. If I can
>generate sequence number myself, I don't need this incremental value from
>db.
>What I want is that the sequential alphanumeric id that increments it's
>numeric value by one everytime a new record is inserted while the alphabet
>part remains the same..(like I've shown before):
>AAA0001
>AAA0002
>...
>AAA0009
>AAA0010
>This id will be used as the application number that will be printed as a
>barcode on the user's application form. The requirement says that the app
>number is prefixed with the letters (actually it's not 'AAA' but 'FIN' which
>stands for File Identification Number)....


Things to consider.

First, if all your application-numbers start with FIN, then my advice
would be to do this after you pull the ID from the database. Why
store an additional three characters for each ID? And why make your
code more complicated than it should be?

Second, considering that you want sequential numbering, are you
prepared for what what happens when you delete an item? You either
live with gaps in your sequence (recommended) or you renumber.

Third, your example limits the number of applications to 9999 (i.e.,
AAA9999 (seven characters)), is that correct OR is the next number
AAA1000 (eight characters)? If the next number is eight characters,
then why not use "AAA1" as the start of the sequence? Why the extra
zeros?

Fourth, if you are going to use the index of the table for creating
the application-number and require the addition of the alpha prefix,
then I would create another field in the table and store the
application-numbers there.

For example, I would use (not tested):

// code to create a record (i.e., INSERT INTO your_table (whatever)
VALUES ('$whatever') )

$id = LAST_INSERT_ID();

The above statement finds the last record added to the database (the
index of the record) and then I would create my application-number
and store it in that record -- like so:

// code to create the application-number (i.e., $application-number =
'AAA' . $id; )

$query = "UPDATE your_table SET application_number =
'$application_number' WHERE id = '$id' ";
$result = mysql_query($query) or die('Error, query failed');

That way you should not have any duplications and you have
application-numbers (as you want )that are tied to the auto-numbering
of the table's index.

HTH's

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

If I follow you correctly, is the scenario below possible.??
Let's say there are 2 users accessing the app at the same time (user A and
B). Here's the sequnce of operation...
1. user A insert into table (get id = 1 from auto increment value)
2. user B insert into table (get id = 2 from auto increment value)
3. user A get value from $id = LAST_INSERT_ID() (id = 2)
4. user B get value from $id = LAST_INSERT_ID() (id =2)
5. user A update table where id = 2
6. user B update table where id = 2

in the scenario above, the row with id = 1 doesn't get his file number (fin)
while the row with id = 2 gets the value for his file number twice...

How can we make sure that those 3 processes are atomic operation (insert
table -> get id from LAST_INSERT_ID() -> update table) ??

Thanks..


tedd-2 wrote:
>
> For example, I would use (not tested):
>
> // code to create a record (i.e., INSERT INTO your_table (whatever)
> VALUES ('$whatever') )
>
> $id = LAST_INSERT_ID();
>
> The above statement finds the last record added to the database (the
> index of the record) and then I would create my application-number
> and store it in that record -- like so:
>
> // code to create the application-number (i.e., $application-number =
> 'AAA' . $id; )
>
> $query = "UPDATE your_table SET application_number =
> '$application_number' WHERE id = '$id' ";
> $result = mysql_query($query) or die('Error, query failed');
>
> That way you should not have any duplications and you have
> application-numbers (as you want )that are tied to the auto-numbering
> of the table's index.
>
> HTH's
>
> tedd
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

--
View this message in context: http://www.nabble.com/Creating-alphanumeric-id-for-a-table-t p25391939p25447945.html
Sent from the PHP - General mailing list archive at Nabble.com.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating alphanumeric id for a table

> 1. user A insert into table (get id = 1 from auto increment value)
> 2. user B insert into table (get id = 2 from auto increment value)
> 3. user A get value from $id = LAST_INSERT_ID() (id = 2)
> 4. user B get value from $id = LAST_INSERT_ID() (id =2)
[8<]
> How can we make sure that those 3 processes are atomic operation (insert
> table -> get id from LAST_INSERT_ID() -> update table) ??

From the MySQL 5.0 manual (20.9.10.3):

For LAST_INSERT_ID(), the most recently generated ID is maintained in
the server on a per-connection basis. It is not changed by another
client. ... Using LAST_INSERT_ID() and AUTO_INCREMENT columns
simultaneously from multiple clients is perfectly valid. Each client
will receive the last inserted ID for the last statement /that/ client
executed.

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.htm l

Ben

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php