automatically insert random value in column

Question

I want to generate random number of 6 digit in mysql.

For example,

I have a table named as DATA and columns are job_name and job_id. So, when user insert value in job_name and the value of job_id which would be random and unique number which will be stored in database automatically.

I have been searched for this but can not get anything out of it. So, Please specify your answer in brief.

make `job_id` primary key and do `AUTO_INCREMENT` to start from 100000. — mitkosoft, Apr 15 '16 at 07:09
I agree with Jester, the solution yo have provided is not for random numbers — Dhara Vihol, Apr 15 '16 at 07:13
you need job_id to be a random number or another column in your db ,with a 6 digit random number? — Hanan Ashraf, Apr 15 '16 at 07:17
Create random number using php and put it in db. `function randomkey( $length ) { $chars = "0123456789"; return substr(str_shuffle($chars),0,$length); }` You can also make it more unique by adding current timestamp. like `$key = randomkey(10) . date('dmYHis');` — abhishek bagul, Apr 15 '16 at 08:25

score 1 · Answer 1 · edited May 23 '17 at 12:07

Give job_id the primary key and use Auto Increment in your database. This way you just have to insert job_name and it will auto_increment the name by itself. Auto Increment

P.S. this is not random, but there shouldn't really be a reason for it to be random? otherwise you'd have to make a script that keeps making random numbers and comparing them to the database until one doesn't exist yet.

If you really want it to be random check this post

I really suggest that you don't do this, there might also be a day where you run out of ids and it will get stuck in an endless loop if you limit it to 6 characters.

Sankar V · Answer 2 · 2016-04-15T11:32:34.680

0

Use 2 fields in your table. One is your AUTO_INCREMENT job_id and another new field: job_unique_id. You can insert unique random values in that column using mysql UUID function. From your tags it seems like you're using codeigniter. In codeigniter use the following code for generating unique tokens:

$data = array(
    'name' => 'full name'
);
$this->db->set('job_unique_id', 'UUID()', FALSE);
$this->db->insert('my_table',$data);

Check this link for more info: http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid

edited Apr 15 '16 at 11:32

answered Apr 15 '16 at 07:12

Sankar V

4,110
5
28
52

Yup, I got it. but an you describe where should i put this code $this->db->set('job_unique_id', 'UUID()', FALSE); – Dhara Vihol Apr 15 '16 at 07:17
`UUID` is in format `6ccd780c-baba-1026-9564-0040f4311e29` which is not 6 digits random integer – mitkosoft Apr 15 '16 at 07:29
@DharaVihol - you need to add the code before the insert function. – Sankar V Apr 15 '16 at 07:34
No, It is not adding the random key, every time it is adding only zero. – Dhara Vihol Apr 15 '16 at 11:26
The `job_unique_id` field needs to be of type `varchar(50)` or text. Also note the generated key looks like: '2787e8b8-1a60-11e5-be60-3085a9ad784a'. Check this link for more info: http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid – Sankar V Apr 15 '16 at 11:29

score 0 · Answer 3 · answered Apr 15 '16 at 07:19

0

try this query

INSERT INTO `DATA`(`job_id`, `job_name`) VALUES (ROUND((RAND() * (999999-100000))+100000),'php developer')

answered Apr 15 '16 at 07:19

Rakesh Sojitra

3,538
2
17
34

Can i see your table structure? As its working in my phpmyadmin when i query directly. – Rakesh Sojitra Apr 16 '16 at 06:44

automatically insert random value in column

3 Answers3