Mysql auto increment issue

Question

 function randomUnique(){    
        return $randomString =rand(0, 9999); //generate random key    
    }

    function insert($uid,$name,$email){
            $link = mysqli_connect("localhost", "root", "", "dummy");        
             $query = "insert into `usertbl`(`uid`,`name`,`email`) 
                       values('".$uid."','".$name."','".$email."');";
            if(mysqli_query($link, $query)){        
                return $rval = 1;
            }else if(mysqli_errno($link) == 1062){          
                insert(randomUnique(),$name,$email);
            }else if(mysqli_errno($link != 1062)){          
               return $rval = 2;// unsuccessful query           
            }
        }


        $uid = randomUnique();
        $name = "sam";
        $email = "sam@domain.com";
        $msg_code = insert ($uid,$name,$email);
        echo $msg_code;

I have 4 columns in the table : id(PK AI),uid(varchar unique),name(varchar),email(varchar). When I want to create a new user entry.A random key is generated using the function 'randomUnique()'.And I have the column 'id' set to AI so it tries to input the details, but if the key repeats that error number 1062 is returned back from mysql.Everything runs well except for id column which is set to AI. the column value is skipped once if one key is a duplicate. The above code is a recursive function so the number of values skipped in column 'id' is directly proportional to the number of times the function is called.

Example:

id | uid  |   name |   email
1  |  438 |   dan  |  dan@domail.com
2  | 3688 |  nick  | nick@domain.com
4  | 410  |  sid   | sid@domain.com

Here, we can see number 3 has skipped bcoz either random number function gave us a number 438 or 3688 which tends to throw back an error and our recursive function repeats once skipping the number 3 and entering 4 next time on successful execution.

I need to fix the auto increment so it enters the value into proper sequence . I cannot change the structure of the table.

note: why do you care if an id is skipped? looks a code smell to me. — Karoly Horvath, Mar 25 '17 at 11:33
I'd be willing to bet that you have an insert which is failing a key constraint (e.g. a not unique return from `uniqid`), which will still increment the auto increment counter in both mariadb and mysql. — Rogue, Mar 25 '17 at 11:40
If your database is innodb refer to http://stackoverflow.com/questions/17668369/why-does-mysql-skip-some-auto-increment-ids https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable — Bob, Mar 25 '17 at 11:41

score 1 · Answer 1 · answered Mar 25 '17 at 11:30

You can check whether an entry already exists with that uid before performing the INSERT operation, e.g.

SELECT COUNT(*) FROM table WHERE uid = '$uid';

This will return you the count of records that have the newly generated uid. You can check this count and perform the INSERT only if count is 0. If not, you can call the function again to generate anoter random value.

score 1 · Answer 2 · answered Mar 25 '17 at 11:41

1

In each function calling you are creating new db link, may be for this situation php provided mysqli_close($link);

Either you close connection

  if(mysqli_query($link, $query)){        
            return $rval = 1;
        }else if(mysqli_errno($link) == 1062){
            mysqli_close($link);          
            insert(randomUnique(),$name,$email);
        }else if(mysqli_errno($link != 1062)){          
           return $rval = 2;// unsuccessful query           
        }

OR simply put DB connection out of function

$link = mysqli_connect("localhost", "root", "", "dummy");   
function insert($uid,$name,$email){

answered Mar 25 '17 at 11:41

GRESPL Nagpur

2,048
3
20
40

Thanks, this worked for me. I declared the $link outside the function. – Francis Mar 27 '17 at 05:07
@Francis : you can consider to accept as this answer worked for you, by clicking green right tick mark. – GRESPL Nagpur Mar 27 '17 at 05:46

user2182349 · Answer 3 · 2017-03-25T11:41:38.370

0

Use PHP's uniqid function, it generates a proper unique is.

http://php.net/manual/en/function.uniqid.php

What is this is being used for? You may be able to use the id column which will perform much faster and is already guaranteed to be unique.

edited Mar 25 '17 at 11:41

answered Mar 25 '17 at 11:36

user2182349

9,569
3
29
41

Mysql auto increment issue

3 Answers3