CODEWITHSUNDEEP
phpmysqlmd5unique
Kyle
Kyle

Reputation: 410

Unique Value in non-primary field

I'm generating a random 10 character string with php, and inserting it into a column in my DB. My issue is that I want this string to be unique(in the database). I've thought of many ways of doing this, but wondering which way is the most efficient.

My PHP looks like this(random string can only be 10 chars long):

 //generates an almost unique(not quite) ticket number
 $pretrimmedtask = md5(uniqid(mt_rand(),true));
 $tasknum =  substr($pretrimmedtask ,0,10);

I then take this "unique" value and insert it. But because of the trim of the string, this value is by no means unique. I'm wondering what is the best way of making sure this value could never be duplicated, while still being efficient.

(I understand that querying the db to look for this value in there is possible... but I would rather do it in a more elegant fashion)

Upvotes: 0

Views: 267

Answers (4)

Xfile
Xfile

Reputation: 684

Had this issue by myself.

I use (insert) time() and insert/row id (+ special letters array) md5-ed all together as one string and hashed - for some cookie purposes lets say. So, that key is exposed.

Insert (or row) id cannot be duplicated, and merged with unix timestamp (10 digits)+random letters and md5-ed all together creates surely unique "second key" somewhat harder to break what is available via cookies. In this case is impossible to break it.

But it is a hash.

If 10 chars is essential - as I can't find reason to be - you may create function for creating keys like (99999999999999999999-primary key)+substr 10 with letters included also, but that depends on a level of exposure of that key.

However, substr is not an option, and primary key role is simply - essential.

Upvotes: 0

Zefiryn
Zefiryn

Reputation: 2265

Does it has to be 10 character. With crypt() you can generate 13 character long hashes.

Here http://www.php.net/manual/en/function.hash.php you can check the length of different hashing methods. None of them unfortunatelly produces exactly 10 character long string. But you can generate 8 character long string and add two characters.

Another possible solution that I came up with is using current date of generating the string. Unixtimestamp is only numbers and to long but we can convert date into 10 char string in the following manner

Create two arrays, first with keys from 1 til 31 and assign one character for each key (26 letters plus 10 numbers will do the trick), the second array need to have keys from 0 til 99 and have values of two charater long string.

Now take the day, month, year (2 digits), hour, minute and seconds of the current time and replace the value with the value from the array, where day and month take from the first array and the rest from the second. Combine that and you have 10 character long unique string.

Upvotes: 0

Filip Roséen
Filip Roséen

Reputation: 63807

You should update your table and make the relevant column be a UNIQUE KEY, than try to insert the generated string, if no rows where inserted, generate another key and try again.

ALTER TABLE table_name ADD UNIQUE KEY (column_name);

The code below will try to INSERT a new row into table1, if unable it will try again with a different random generated $key.

IE. the query will not succeed if col2 has a unique key constraint and the value of $key already exists in the column.

function generate_random_string () {
  $charset = array_merge (
    range ('a', 'z'), range ('A','Z'), range ('0','0')
  );

  shuffle ($charset);

  return join ('', array_slice ($charset, 0, 9));
}

/* ....................................................... */

do {
  $key = generate_random_string ();
} while (
  !mysql_query ("INSERT INTO table1 (col1,col2) VALUES (123, '$key')")
);

You can of course use your own algorithm for generating random strings.

NOTE: Make sure that the query can potentially succeed so that you don't get caught in an endless loop.

Upvotes: 1

zerkms
zerkms

Reputation: 254944

Create unique mysql index that covers only that field and insert the value in a loop until success.

Like:

while (true) {
    $random = generate it;
    try to insert;
    if (inserted without errors) break;
}

Upvotes: 1

Related Questions