CODEWITHSUNDEEP
phpmysqlrandomnumbers
Haradzieniec
Haradzieniec

Reputation: 9338

php: quickest way to generate a 5-digit number not already in MySQL db column (with a unique attribute)

Suppose Table1 contains column orderid (not a key, although it's NOT NULL and unique). It contains 5-digit numbers. What's the best way to generate a php var $unique_var that is not in that column. What could be important, from 10% to 30% of 5-digit numbers are in the table (so generating a number and checking while (mysql_num_rows() ==0) {} is not a best way to find one, are there any better solution for performance?).

Thank you.

Upvotes: 2

Views: 1231

Answers (3)

Ted Hopp
Ted Hopp

Reputation: 234847

Here are a couple of suggestions. Each has its drawbacks.

  1. Pre-populate your table and add a column to indicate that a number is unused. Select an unused number using LIMIT = 1 and mark it used. This uses a lot of space.
  2. Keep a separate table containing previously used numbers. If that table is empty, generate numbers sequentially from the last used number (or from 00001 if Table1 is empty). This requires some extra bookkeeping.

Upvotes: 3

Sergio
Sergio

Reputation: 303

I would suggest finding the biggest number (with a MAX() clause) and start from there.

Upvotes: 3

zerkms
zerkms

Reputation: 255005

If there is just 10-30% of numbers already taken - then it means that only 10-30% of queries will be performed at least twice. Which is not a big performance issue at all.

Otherwise - just create all-5-digit-numbers-list table (just 100k rows) and remove all that exist. When you need another random number - just pick one and delete.

Upvotes: 7

Related Questions