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
Sagar
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
Devart
source
to share
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
Electronick
source
to share