0

Hello I'd like to have an incremental alphanumeric key (primary key) in one of my tables. I'd like it to be BR10000, BR10001, BR10002, and so on. Any ideas? I've made some researches on google, but I find it very hard to understand for a beginner like me. So any suggestion or anything that could help get started is truly appreciated. thanks.

StasGrin
  • 1,800
  • 2
  • 14
  • 30
  • Why don`t you use 2 colomns? – ka_lin Nov 12 '12 at 07:52
  • 1
    When you say tables, are you talking about a database? Why do you specifically need to store the "BR" prefix? Can't you just define that as a constant for your application, and then use an auto_increment for your primary key column (usually called `id`) and just join them when you need to display it? – Stephen Nov 12 '12 at 07:53
  • save only numbers where ever you want to check add with prefix as BR – Jobin Nov 12 '12 at 07:54
  • @Stephen yup I mean DB, I like your idea but what I want is to reference the PK with BR prefix. When I would search for the booking reference number let's say BR10012 how is it going to look for that if numbers are only stored in the DB? Thanks –  Nov 12 '12 at 08:08
  • Any specific database system or are you looking for a general purpose approach (i.e. most likely: a leaky-abstraction)? – VolkerK Nov 12 '12 at 08:17
  • @VolkerK I FYI I use MySQL, I like the idea of having a integer PK then another field where it contains alphanumerics like VARCHARS, but I can't seem to find a solution on how to do that. –  Nov 12 '12 at 08:27
  • Try just to add column `id_prefix`(varchar) and auto-incremental `id`(int). After that select like this: `SELECT CONCAT(id_prefix,id) as id ...` – StasGrin Nov 12 '12 at 09:32
  • Thanks @StasGrin! Just what I'm looking for! Anyways do you have some working examples there? Or point me to a similar tutorials? Thanks again. –  Nov 12 '12 at 10:11
  • @bEtTyBarnes No, i just *suggest* how to do that on mysql-side. Tutorials or examples? Just read about simple function `CONCAT`. p.s. I can post it as answer for closing this question, if u want to. – StasGrin Nov 12 '12 at 10:14
  • If the prefix never changes, why store it in the database for every record? You still aren't going to be using it in lookups. – Stephen Nov 12 '12 at 21:20
  • @StasGrin okay let me see your answer. thanks –  Nov 13 '12 at 06:45
  • 1
    @Stephen coz he want to process it on database-side. see comments above. – StasGrin Nov 13 '12 at 07:26

1 Answers1

0

Store the record in your database and the primary key of that record (usually the ID column) becomes the number part. When you display it to users, you simply format it as required, e.g.:

$a = sprintf('BR%06d', 135);

var_dump($a); //string(8) "BR000135"

When you want to lookup a record from user input you simply strip off the "BR" before doing your query, e.g.:

sscanf($a, 'BR%06d', $id);

var_dump($id); //int(135)
Stephen
  • 18,597
  • 4
  • 32
  • 33