1

ive found a question related to my problem and im using it for some parts of my website. but right now i can't use that

How to make MySQL table primary key auto increment with some prefix

in my website i need to create a tracking number. the tracking number must be seen by the user from the html input. so if i use the same idea with the link above it will not work. because it will only trigger the autonumber when an insert is executed i want to create something like this string + numbers(00001) + year str-00001-15

Community
  • 1
  • 1
knowmeifyou
  • 217
  • 7
  • 17
  • 2
    Consider storing the prefix and the integer separately - or if the prefix is always the same, not storing that part at all. – Strawberry Dec 03 '15 at 07:53
  • 1
    Altough you can do this, it's not recommended. Use a normal integer ID colum and then for order number create an unique column using a string generation. Reference always your table rows with the numeric ID. But this will allow to filter and display a "order" number. Unique column costraint will prevent duplicates and you can manage issues in code. – Yuri Blanc Dec 03 '15 at 07:54
  • 1
    I disagree with Yuri to some degree. I don't think an 'id' column should ever be part of a public key. – Strawberry Dec 03 '15 at 07:55
  • 2
    people blow days on doing this segment stuff. Keep it simple with an int. 99% of the time it comes down to OCD issues. The other 1% I don't know – Drew Dec 03 '15 at 07:58
  • 1
    can you show me example? thanks. – knowmeifyou Dec 03 '15 at 07:59
  • Here is your id: `s-n1-YYYY-n2-n3`. Now, the fun begins. When the year changes, you want n2 to go back to 1. Who knows what n3 is, or n1 for that matter. The whole thing need concurrency (locking) control because a 1000 inserts could happen at once. It turns into a train wreck. It can be wrapped nice if the gateway to all action is a stored procedure. We have all written them. And as we do, we wonder what is wrong with a simple int. All gets back to OCD – Drew Dec 03 '15 at 08:15

1 Answers1

0

This might help to generate the string as hint - 

$number = 0;
$keys= array();
for($i = 0; $i < 5; $i++) {
   $number++;
   $keys[] = 'str-' . str_pad($number, 5, "0", STR_PAD_LEFT) . '-' . date('y');
}

Output

array(5) {
  [0]=>
  string(12) "str-00001-15"
  [1]=>
  string(12) "str-00002-15"
  [2]=>
  string(12) "str-00003-15"
  [3]=>
  string(12) "str-00004-15"
  [4]=>
  string(12) "str-00005-15"
}

You can implement it as you need.

Sougata Bose
  • 31,517
  • 8
  • 49
  • 87