0

I am wondering if there is any way to generate the random five digit number and insert into database using mysql. I know i can do it using PHP but wanted to know if i can get rid of the php and do it using database. Also, the generated number should be different than the numbers already stored in the database.

Following is example as how it should look like:

I have four letters of pattern common in random_no field which is org1 and want to append other 5 random letters as shown in following example:

+-------+-----------+-----------+--------------------------------------------+
|   id  |   title   |   phone        |         ABN       | Random No        |
+-------+-----------+----------+---------------------------------------------
|   1   |   title1    | 4765 5678   |   214-444-1234    |  org123456        |
|   2   |   title2    | 4444 4444   |   555-111-1234    |  org109876        |
|   3   |   title3    | 3333 3333   |   214-222-1234    |  org187654        |
|   4   |   title4    | 1111 1111   |   817-333-1234    |  org156432        |
|   5   |   title5    | 2222 2222   |   214-555-1234    |  org177654        |
+-------+-----------+-----------+--------------------------------------------

Any help will be appreciated.

RK.
  • 973
  • 5
  • 19
  • 45
  • The answer to this post might be relevant: http://stackoverflow.com/q/4382185/1144203 – ivan.sim Sep 10 '14 at 00:19
  • Can you provide an example? For instance, how large is the table? – Gordon Linoff Sep 10 '14 at 00:19
  • 1
    first thing look at [THIS POST](http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) I wrote for how to ask a question appropriately – John Ruddell Sep 10 '14 at 00:19
  • your five random numbers won't cut it if your rows gets large – Kevin Sep 10 '14 at 00:22
  • Table has 5 fields including this field where i want to store random generated number. I have certain pattern of 4 letters which will be appended to random generated number as i can differentiate between certain fields. for instance: I have 4 letters common such as org1 and if i would generate other 5 numbers between (00001-99999) then it should look like: org122222. – RK. Sep 10 '14 at 00:22
  • @RK. the issue with generating a random number is that there is no guarentee you wont come across the same number and get a duplicate... so do you want it to be a random number? or an incrementing number? btw thanks for editing with some more detail :) – John Ruddell Sep 10 '14 at 00:36
  • @john: random would be great. incremental is bit insecure because this generated field will be given to no of users for different purposes so if i go for incremental than there is large possibility that one user try of other user link simple by changing one number. – RK. Sep 10 '14 at 00:41
  • ah... ok well then let me see if I can work up a solution. btw are you able to use user defined varaibles? – John Ruddell Sep 10 '14 at 00:42
  • @JohnRuddell: Thank alot. waiting on it. also working myself if i can figure out in any way. – RK. Sep 10 '14 at 00:43

1 Answers1

0

Now there is no guarentee that there are not going to be duplicates... but this is getting two random numbers and multiplying them by different numbers so its not all that likely that they will be getting random numbers

UPDATE table t,
(   SELECT id, LPAD(FLOOR(7 + (RAND() * 50) * (RAND() * 333)), 5, 0) as join_num
    FROM table
)t1
SET t.random_no = CONCAT(t.random_no, t1.join_num)
WHERE t.id = t1.id;

From here I recommend you do this.. after updating your table go back through and run this query

SELECT id FROM table
GROUP BY random_no
HAVING COUNT(*) > 1;

if there are any results returned then the id's there will need a different random number and you can just change it at any duplicate spots once you know if there are any dupes

Breakdown of the update query....

update the table alias t.
select from table the id and then the random number alias t1.
concat the number and the column by row..
where the id's are equal... getting a different number for each row.

LPAD is a zero fill so that way if the number is smaller than 5 spaces it'll fill it in with 0's and then you have to use FLOOR() with RAND() for the random number.
Hope thats helpful!

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
  • Thanks for your solution but this does not look feasible to me because i would use this scheme in 5 different tables and all of them will be updated frequently. Therefore, it is impossible for me to check every time the record inserted in the table and delete if any duplicates occur. I look for permanent solution. Alternatively, if i did not find any solution solely using mysql then i would go for php that would be quite easy but with more requests.This is the only reason why i want to go only for mysql to avoid frequent server requests. – RK. Sep 10 '14 at 00:55
  • @RK. if you are doing this for 5 tables... and need to execute it multiple times then you should look into setting up triggers for when the tables are updated and then execute something like this query. [SEE HERE](http://dev.mysql.com/doc/refman/5.0/en/triggers.html) – John Ruddell Sep 10 '14 at 00:57