Autoincremented unique string id

Question

I have read many answers on stackoverflow, but I haven't find anything related my issue.

This is my table:

`id` char(11) NOT NULL,
`element` varchar(32) NOT NULL,

I need to use an autoincremented unique string id of 11 chars ( case sensitive if possible ) and numbers as youtube does:

youtube.com/watch?v=j5syKhDd64s

youtube.com/watch?v=YVkUvTmDf3Y

youtube.com/watch?v=8BcDeoKLsaY

...

How could I do this with mysql/php ?

@D4V1D The same way you could increment numbers. Heck, run-of-the-mill hexadecimal numbers are "incrementable strings": `FE + 1 = FF`. — deceze, Sep 01 '15 at 09:28
@D4V1D aaaaaaaaaaa, aaaaaaaaaab, aaaaaaaaaac, aaaaaaaaaad, ... — xRobot, Sep 01 '15 at 09:29
This might help http://stackoverflow.com/questions/3567180/increment-letters-like-numbers — Alfabravo, Sep 01 '15 at 09:30
@waka Numbers are chains of arbitrary symbols in a defined order. I'm saying if hexadecimal numbers can contain A-F and can be incremented, then how hard is it to image "numbers" using A-Z which can be incremented? — deceze, Sep 01 '15 at 09:31
@deceze: Look at what OP posted. How do you increment `8BcDeoKLsaY`? Ok, you could say "increment Y to Z", but after that? Is it "ba" at the end or "bA" or maybe it's "a1"? — waka, Sep 01 '15 at 09:33
Is the Youtube reference random, or is there a pattern to it? Is it just a hash of an integer for example? — Kickstart, Sep 01 '15 at 09:35
@waka That's merely a problem of defining the desired behaviour, and it's pretty trivial. You know how to increment numbers when using the symbols 0-9, right? Just extend that alphabet used to `0-9a-zA-Z`. The rules about incrementation don't need to change at all. After 9 comes a, after z comes A, after Z you add a position to the "number" and wrap to 0. — deceze, Sep 01 '15 at 09:36
@waka BTW, PHP already *can* increment strings: `$l = "Z"; var_dump(++$l); // AA` — deceze, Sep 01 '15 at 09:43
@waka My definition of my base 62 numbers. If you want to define your base 62 number alphabet differently or create an entirely different base, be my guest. — deceze, Sep 01 '15 at 10:12
Something like `$id = rtrim(base64_encode(hash("crc32b", $num)),"=");` where `$num` is any integer, would generate 11 characters of upper, lower and digits: [Test here](https://eval.in/426071). Dunno if there's any collisions. — Jonny 5, Sep 01 '15 at 11:15
@deceze: Exactly. YOUR definition. But as far as I know there's no "official" definition that after "9" comes "a" (last time I checked, there was a 10 after a 9...) and after "z" comes "A". Therefore, "incrementing a string" is something that's really a matter of opinion, not a matter of facts. — waka, Sep 02 '15 at 08:29
@waka The *logic* of how a string can be incremented, i.e. how a value in a [positional numeral system](https://en.wikipedia.org/wiki/Radix) can be incremented, is very well defined and fixed. The only thing you need to do is to specify your alphabet. You seem to be questioning how a string could possibly be incremented at all, which is really a solved problem. — deceze, Sep 02 '15 at 08:35
@waka And no, 10 doesn't come after 9 as far as a positional numeral notation is concerned. When incrementing 9, it wraps around to 0 and you increment the next position in your number. — deceze, Sep 02 '15 at 08:37

score 2 · Answer 1 · answered Sep 01 '15 at 09:59

2

You probably want something like hashids. Their page also links to alternative solutions.

If that doesn't fit the bill, please describe your problem in more detail.

answered Sep 01 '15 at 09:59

user5287961

21
1

it seems great but where can I put the last id to create the next row in mysql ? – xRobot Sep 01 '15 at 10:09
1

You just use an integer auto incremented field for the id, and hash it for external use. – Kickstart Sep 01 '15 at 10:16
@user5287961 sorry I just noticed that it's not auto-incremented. – xRobot Sep 01 '15 at 10:18
@Kickstart and for internal use ? Is there a reverse function to get the string from the id ? – xRobot Sep 01 '15 at 10:40
The page given by user5287961 does talk about allowing decoding (and I would assume you will use a salt value to encode / decode them). But personally I would be tempted to store the encoded version on you database table as well. – Kickstart Sep 01 '15 at 10:46

Bender · Answer 2 · 2015-09-01T09:40:03.180

0

one way to do it is to use this function

    function generateRandomString($length = 11) {
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
    return $randomString;
}

and then pass $randomString result to your query to add in DB.

EDIT : if OP is looking for the string like he mentioned (aaaaaaaaaaa, aaaaaaaaaab, aaaaaaaaaac, aaaaaaaaaad) i recommend you to check out Theory of Computation .

edited Sep 01 '15 at 09:40

answered Sep 01 '15 at 09:33

Bender

705
11
25

That's a terrible random string generator. `rand` is just about the worst random number generator you can choose. Use `/dev/random` to read a bunch of random bytes and simply convert them to a narrower alphabet. Base64 would be a decent choice. – deceze Sep 01 '15 at 09:38

Micaela · Answer 3 · 2015-09-01T09:45:42.430

This is my code which I Autoincremented my unique id string field.

case "Add":
    $itemno = $_POST['itemno'];
    $qty = $_POST['qty'];
    $unitprc = $_POST['unitprc'];
    $amt = $_POST['amt'];
    $coopmemid = $_SESSION['kiosk']['is_coopmemID_kiosk'];
    $totamt = 0;
    $totitm = count($itemno);
    $a_empgroid = array();
    for($x=0; $x<$totitm; $x++) {
        $Addquery = "INSERT INTO tb_empgrocery (coopmemID , date_ordered, item_no, qty_ordered, unit_price, amount) 
                     VALUES ('$coopmemid',(NOW()),'$itemno[$x]','$qty[$x]','$unitprc[$x]','$amt[$x]')";
        $atecCoop->query($Addquery);
        $totamt+=$amt[$x];
        $inserted_id = $atecCoop->insert_id;
        array_push($a_empgroid,$inserted_id);
    }
    $Savequery = "INSERT INTO tb_empgroc_master (order_status, date_ordered, total_items, total_amount) VALUES ('Pending', (NOW()), '$totitm', '$totamt')";
    $atecCoop->query($Savequery);
    $empgrocmstid = $atecCoop->insert_id;
    $orderno = date('y-m-').str_pad($empgrocmstid, 10, "0", STR_PAD_LEFT);
    $sql = "UPDATE tb_empgroc_master SET order_no='$orderno' WHERE empgrocmstID='$empgrocmstid'";
    $atecCoop->query($sql);
    foreach($a_empgroid as $empgrocid) {
        $sql = "UPDATE tb_empgrocery SET order_no='$orderno' WHERE empgrocID='$empgrocid'";
        $atecCoop->query($sql);
    }
break;

As you can see the field oder_no is a unique id (varchar 25)

Hope this helps :)

Autoincremented unique string id

3 Answers3