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.
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.
try this query
INSERT INTO `DATA`(`job_id`, `job_name`) VALUES (ROUND((RAND() * (999999-100000))+100000),'php developer')
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
UUID is in format 6ccd780c-baba-1026-9564-0040f4311e29 which is not 6 digits random integer
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: dev.mysql.com/doc/refman/5.7/en/…
job_idprimary key and doAUTO_INCREMENTto start from 100000.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');