PHP - Autoincrement alphanumeric from 0

[XRS]

Hello,

 

I'm in trouble with alphanumeric autoincrement. I have a database wich I need an ID ( the autoincremented one ). It will have many rows, so I'd like to have the smallest ID possible.

 

For example, normally, using only numbers from 0 to 10000000 this ID will be very long. If the ID could have letters, it could be something like 3AcB2 .

 

For example what I'd like to:

 

1

2

3

4

....

99

1a

1A

1b

1B

....

999

9aa

9AA

9aA

9Aa

 

and so on...

Could someone help me generate me such ID's being last in dabase + 1 ?

Thanks in advance.

[davidannis]

I find it difficult to believe that it makes sense to have the overhead of converting to base 62 numbers just to shorten he ID. A 10 character number gives you a 10 billion possible records, a 13 character number give you 10 trillion. However, if you need to do it, the way to do it is documented here. A mysql bigint (unsigned) column would allow you 18,446,744,073,709,551,615 records. That's 18 quintillion if I remember number names that big correctly.

Edited September 8, 2013 by davidannis

[vinny42]

Provided that you have a very good reason to do this, you ca do it in the database itself (which is the only place where this should be done anyway)

 

Create a regular INT PK AUTO_INCREMENT column. That will generate the actual ID, leave that in place, it's all you have to guarantee unique values.

Use an AFTER INSERT trigger to use the generated ID to create a HEX value, and store that in a separate column. Make your application use that hex value.

 

You'll probably soon find that the HEX values are very difficult to use, but time will tell.

[XRS]

Hi,

Basically I would like to do it because I want to develop a simple link shortener.

It could easier if a have a link mysite.com/3Xc than mysite.com/13410394808 ( for eg. ) .

 

I'll try to get into it. Thanks

[davidannis]

vinny42,

 Hex values are base 16, OP wants to use base 62.

[kicken]

Basically I would like to do it because I want to develop a simple link shortener.

It could easier if a have a link mysite.com/3Xc than mysite.com/13410394808 ( for eg. ) .

There's no need to not use an normal auto-increment ID number in the database for such a task. Just do the conversion in PHP when you select the link data. For example, when adding a URL:

$sql = 'INSERT ...';
$db->exec($sql);
$id = $db->lastInsertId();

echo 'Your url is http://example.com/'.encodeId($id);

;

 

When getting a page:

$id = decodeId($_GET['id']);
$sql = 'SELECT ... WHERE id=?';
$stmt = $db->prepare($sql);
$stmt->execute([$id]);
var_dump($stmt->fetch());

Just create two functions, one encodeId which will change an integer in to your short string, and decodeId which will change the short string back into your integer. Edited September 8, 2013 by kicken

[vinny42]

 

 Hex values are base 16, OP wants to use base 62.

 

Then use a base62 method in the trigger :-)