Clever Geek Handbook

Insert a record in a field that must be between 10000 - 99999 and no longer exists in unique field records

I have a field name 'unique_no' in a table that already contains some records. I want to select records in the "unique_no" field, which should be
 - between 10000 - 99999 and
 - no longer exists in the unique_no files.

So please suggest me a method for this. Thanks in advance.

+3


source to share


2 answers


  • Enter the field unique_no

    as AUTO_INCREMENT.
  • Set table option AUTO_INCREMENT = 10000;
  • Insert new records, specify NULL for the field unique_no

    .


+1


source


It should be optimized, but basically you want code like (pseudocode):

// lock table to prevent duplicates
mysql_lock_table('table');
do {
    $random = rand(10000, 99999);
} while ( mysql_count_rows("SELECT count(*) FROM table WHERE unique_no = %value", $random) > 0 );
$row['unique_no'] = $random;
mysql_insert($row);
// unlock table
mysql_unlock_table('table');


If the table has a UNIQUE key in the field, you can use INSERT IGNORE

with the specified row values

// lock table to prevent duplicates
do {
    $random = rand(10000, 99999);
    $row['random'] = $random;
    $result = mysql_insert_ignore($row);
} while ( mysql_affected_rows($result) == 0 );
0


source






More articles:


All Articles