This question already has an answer here:
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.
</div>
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!