How to generate autoincrement primary key in varchar

Question

I want my UserId field to be like this AB001.

Where AB is the name of anything and is constant, and 001 is the auto-increment value.

How can I do that in PHP and MySql.

You can find your Answer here: http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-suffix — Amith Raj Shetty, Jan 22 '14 at 11:03
i think you need to go for having a composite primary key, in which multiple fields together constitute a unique value. Then you can keep auto increment field separate from name and both fields will work together as primary key. check http://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql — Pranay Bhardwaj, Jan 22 '14 at 11:07
@AmithRajShetty How can I implement that if I am using PHP and MySql?? — user3220816, Jan 22 '14 at 11:15
if `AB` is constant and never changing you could just have a `view` on your table that exposes a column `ID`, but in the `select` of that view you would concatenate that constant value with a `normal` autoincrement like this `SELECT 'AB' + CAST(ID AS VARCHAR(10)) AS ID FROM yourTable` — DrCopyPaste, Jan 22 '14 at 12:06

mic · Answer 1 · 2014-01-22T11:29:58.070

0

In PHP you can increment the var like this:

$var = "AB001";

for($i = 0; $i < 10; $i++) {
    echo $var . "\n";
    $var++;
}

Then you just need to insert it into mysql...

EDIT ----

However you may encounter ID collisions if two processes create the same ID at the same time!

Another Edit ----

You could make a function like this and pass in the DB ID and the base format like this:

$base = "AB000";

echo createId($base, 1456);

function createId($base, $dbId)
{
    for ($i = 0; $i < $dbId; $i++) {
        $base++;
    }
    return $base;
}

I hope this is what your looking for :)

edited Jan 22 '14 at 11:29

answered Jan 22 '14 at 11:06

mic

1,251
2
15
33

is this really valid php? you declare `$i` in the loop but never use it and then you `increment $var` which has been assigned to a string... that works? – DrCopyPaste Jan 22 '14 at 11:11
`$i` is used in the loop to check that it is lower than 10 after each iteration – mic Jan 22 '14 at 11:12
I am giving a user this UserId when he registers. The attribute in table is A_I. – user3220816 Jan 22 '14 at 11:13
@micb yea i take that back didn't work with php for a long time, seems it works (http://phpfiddle.org/main/code/ifq-1jd) but still i would really not recommend to do this id retrieving thing in php, but rather in the database itself since it can take care of uniqueness and stuff way better (not to mention it indeed CAN take care of those things whilst php cannot :D) – DrCopyPaste Jan 22 '14 at 11:16
DrCopyPaste I agree... @user3220816 ok i think i get what your after, I will update my response in a min – mic Jan 22 '14 at 11:16
@micb Isn't there any other way to do that in database itself?? – user3220816 Jan 22 '14 at 11:34
@user3220816 with a composite key you could, as mentioned earlier in the comments: http://stackoverflow.com/a/5836070/2186023 – DrCopyPaste Jan 22 '14 at 11:37
you could maybe create a stored procedure in mysql, but im not sure how to do those. As 'Daniel Bottner' said you can do it at run time, the method above will allow you to do that. You would be decorating the ID on every call, or perhaps you could create a cache of them so that you don't have to recreate them each time, although im not sure they will take that long to create until you get to a very high ID – mic Jan 22 '14 at 11:38

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

Please read following questions:

You could also try to use triggers if you really depend on having this field in the database.

Otherwise build the AB part at run time to show it to the user.

edited May 23 '17 at 12:22

Community

1
1

answered Jan 22 '14 at 11:07

Daniel Böttner

112
12

score 0 · Answer 3 · answered Jan 23 '14 at 05:55

0

Please look at this Fiddle. Let me know if you need any more clarifications.

answered Jan 23 '14 at 05:55

Amith Raj Shetty

68
14

this short of a text would have better been a comment. Don't just link stuff in your answer, please include the relevant explanations and code in your answer; so it is not reliant on external resources ;) – DrCopyPaste Jan 23 '14 at 07:57
I cannot comment as I donot have enough reputations(min 50) to comment on a question. – Amith Raj Shetty Jan 23 '14 at 09:19
then expand your answer ;) It should not be necessary to call links to understand an answer; using fiddle is fine but include your code here so one does not rely on that – DrCopyPaste Jan 23 '14 at 09:24
from next time.. :) I thought providing a working example where people can edit the code would be more helpful to understand. :) – Amith Raj Shetty Jan 24 '14 at 07:39
I would always encourage to do so, but I also would always encourage to include explanation about that, too (obviously a working code example WITH explanations is more helpful than one without ;)) – DrCopyPaste Jan 24 '14 at 08:34

How to generate autoincrement primary key in varchar

3 Answers3