Hi,
Is there any way I could make my model ID (primary key) into custom unique 8 digits containing only numbers instead of the default auto increment?
A client requested this specific 8-digits-number-only feature, so I can’t argue much about the reasons.
I want to use the PHP uniqid but it’s 13 digits and contains alphabets as well.
Any idea?
Thanks.
First idea is just set auto_increment start value to 1000000 (or similar number).
Use an unsigned INT column and set auto increment to start at 10000000
CREATE TABLE `my_table` (
`my_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`another_column` TEXT NOT NULL,
PRIMARY KEY (`my_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=10000000
Edit: Due to misreading the question.
If you want it randomized (by your comment about uniqid) and run under Linux you may try:
$randValue = '';
$filePointer = @fopen('/dev/urandom','rb'); // Unix/Linux only
if ($filePointer !== false) // should always be true because /dev/urandom doesn't block but just in case
{
$randValue .= @fread($filePointer,4); // load the first 4 bytes only
@fclose($filePointer); // be nice and close the handle
}
$randValue = bin2hex($randValue);
You can play with the number of bytes retrieved or further process the result.
I forgot to tell that I need the ID randomly generated.
Just want to ask the mechanism on generating the ID and then saving the ID (also the attributes). Do I have to check the database first for a randomly generated ID and then save the attributes or what?
what I’m worrying is that ID will probably collide with another concurrent request. although the chance is very slim, I just want to make sure I’m doing it the right way.
My current code is below. It’s already working well. But is it sufficient? Do I miss something there?
public function beforeSave() {
if ($this->isNewRecord) {
//Generate ID
$this->generateID();
}
}
public function generateID() {
do {
mt_srand();
$id = mt_rand(10000000, 99999999);
$count = $this->dbConnection->createCommand()
->select('count(*)')
->from($this->tableName())
->where('id = :id', array(
':id' => $id,
))
->queryScalar();
} while ($count > 0);
$this->id = $id;
}
Thanks
Looks good to me and I can’t think of a more efficient way. UIDS are based on numbers that are large enough to ensure that a duplicate is highly improbable. 8 digits isn’t enough for this, so you will have to double check, as you are.
You should check for uniqueness against the database just in case. For that you have to generate a temporary ID inside beforeValidate() and check it in rules(), then save it in beforeSave():
...
public $tempId;
...
public function rules()
{
...
array('tempId', 'unique', 'attributeName'=>'id')),
...
}
public function beforeValidate()
{
if (is_null($this->id)) // new record
$this->tempId = $this->generateID();
}
public function beforeSave()
{
if ($this->isNewRecord)
{
$this->id = $this->tempId;
}
...
}
Or something like that…
What about ZEROFILL option in mysql? if "00001234" is a valid id then you choose that option.
That wouldn’t be random.
I think your solution might work for some time, but it isn’t very efficient (probably several selects for each insert) plus you still have the problem about concurrent requests.
I think a better way to go would be letting the DB create the ids using some self defined sequence generator. How to do it depends on your DBMS and I don’t know if all DBMSs provide such functionality. Oracle and postgres certainly do. I expect mssql to provide such functionality, but really don’t know about mysql and sqlite.
Agree with Ben,
The best safe solution is to use DB triggers. Latest versions of MySql have triggers. You may try something like:
MySql:
DELIMITER $$
CREATE TRIGGER set_unique_id BEFORE INSERT ON tbl_user FOR EACH ROW
BEGIN
DECLARE row_count INT DEFAULT 0;
SELECT COUNT(*) INTO row_count FROM tbl_user WHERE user_id=NEW.user_id;
WHILE row_count > 0 DO
SET NEW.user_id=ROUND(RAND()*10000000);//Eight digit random integer
SELECT COUNT(*) INTO row_count FROM tbl_user WHERE user_id=NEW.user_id;
END WHILE;
END $$
DELIMITER ;
PHP:
echo $model->user_id=mt_rand(10000000,99999999);//user_id=12345678 and exists in tbl_user...
$model->save();//MySql will regenerate user_id before INSERT
$model->refresh();
echo $model->user_id; //now user_id is unique but differs from what it was before save()