Generate unique 3 letter/number code and compare to existing ones in PHP/MySQL?

I will go with number 2, it is simple and 40000 is not a big number.

I will go with number 2, it is simple and 40000 is not a big number. To make it more efficient, you can store a number representing each 3-letter code. The conversion should be trivial because you have a total of 34 (A-Z, 2-9) letters.

I'm not sure I understand how/what you convert 34 letters/numbers into numbers. But I guess the 2nd option is a one time only operation and if you say 40000 is small then ok :) – Solenoid Sep 20 '09 at 18:11 Löwis gave an explanation at second part of his solution. – phsiao Sep 21 '09 at 13:42.

I would for option 1 (i.e. Do a sequential search), adding a table that gives the last assigned code per country (i.e. Such that AAA..code are all assigned already).

When assigning a new code through sequential scan, that table gets updated; for user-assigned codes, it remains unmodified. If you don't want to issue repeated queries, you can also write this scan as a stored routine. To simplify iteration, it might be better to treat the three-letter codes as numbers (as Shawn Hsiao suggests), i.e.

Give a meaning to A-Z = 0..25, and 2..9 = 26..33. Then, XYZ is the number X*34^2+Y*34+Z == 23*1156+24*34+25 == 27429. This should be doable using standard MySQL functions, in particular using CONV.

I went with the 2nd option. I was also able to make a script that will try to match as close as possible the country name, for example for Tartu it will try to match T** then TA* and if possible TAR, if not it will try TAT as T is the next letter after R in Tartu. The code is quite extensive, I'll just post the part that takes the first possible code: $allowed = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ23456789'; $length = strlen($allowed); $codes = array(); // store all possibilities in a huge array for($i=0;$iSubstr($allowed, $j, 1).

Substr($allowed, $k, 1); $used = array(); $query = mysql_query("SELECT code FROM location WHERE country = '$country'"); while ($result = mysql_fetch_array($query)) $used = $result'code'; $remaining = array_diff($codes, $used); $code = $remaining0; Thanks for your opinion, this will be the key to transport codes all over the world :).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.