YumYumYum YumYumYum - 3 months ago 10
MySQL Question

PHP - how to make an insert until there is no duplicate field value exist?

I have a Mysql table where pincode field cant be duplicate daily (Sequential increment id), also i cant apply the unique key on that field using Mysql indexing for some reason.

Using PHP i am trying as below, but my code will become endless if i have to keep increasing by checking them as below.

Is there any better way without Mysql indexing to do it from PHP (zend framework)?

$sql = "SELECT count(*) as total
FROM `sh_av_spform`
WHERE DATE(`createdate`) = CURDATE( )";
$result = $db->fetchAll($sql);

if(count($result)>0) {
$tmp_id = $result[0]['total'] +1;
$new_id = sprintf('%03d',$tmp_id); // 009

try{

$sql1 = "SELECT id,pincode
FROM `sh_av_spform`
WHERE DATE(`createdate`) = CURDATE() and pincode='$new_id' limit 1";
$result1 = $db->fetchAll($sql1);
if(count($result1)>0) {
// 009 already exist make it 010?
$tmp_id = $result[0]['total'] +2;
$new_id = sprintf('%03d', $tmp_id); // 010
}

// Ooopsssss! 010 also exist. now what?
// keep wrting code forever? or there is better way?
$db->insert('sh_av_spform', array('pincode'=>$new_id) );// Pincode cant be duplicated

Answer

You can do this entirely in database, using a counter table.

Example:

CREATE TABLE daily_pin (day DATE PRIMARY KEY, pin INT UNSIGNED);

START TRANSACTION;
INSERT INTO daily_pin VALUES (CURDATE(),1) ON DUPLICATE KEY UPDATE pin=LAST_INSERT_ID(pin+1);
INSERT INTO table_requiring_pin (pin) VALUES (LPAD(LAST_INSERT_ID(),3,'0'));
COMMIT;

Notes:

  • The counter table holds a given day's highest as yet used PIN.
  • The INSERT .. ON DUPLICATE KEY gets a new pin, either a "1" if it's the first entry for a given day, or the current value plus 1.
  • LAST_INSERT_ID, when given an argument, returns the argument and remembers it for the next time LAST_INSERT_ID is called without an argument.
  • Finally, left pad it with LPAD to get the "000" format you're wanting.

As a side benefit of this approach, you get easy metrics on pin usage. Like, "what day of the week consumes the most pin?"

Comments