How to avoid duplicate entry in mysql with php

Question

Here i have made a function to produce random key,

function gen_link(){
$link = '';                             
$s = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
for ($i= 0 ; $i <= 4 ; $i++)
$link = $link.$s[rand(0,63)]; 
return $link;   
}

I dont want to repeat the key in mysql table, i have made it unique in mysql, but what i want to do is, when the key already exists i want to regenerate another random key and try to add it to table again, i tried this code below.

$con = mysqli_connect("localhost","shashi","asd123","redir");
$sql = " insert into 'links' ('link') values('$link') ";

do{
 $link = gen_link();
$result = mysqli_query($con,$sql);

}while(mysqli_errno($con)==1064);

 mysqli_close($con);

but it doesn't seem to work at all, it keeps looping. what can i do?

score 2 · Accepted Answer · edited May 23 '17 at 10:32

Instead of generating an actual error, use an INSERT IGNORE query like this:

$sql = "insert ignore into `links` (`link`) values ('$link')";

And check mysqli_affected_rows() to ensure something was actually inserted:

while (mysqli_affected_rows($con) == 0);

All together, that looks like this:

$con = mysqli_connect("localhost", "shashi", "asd123", "redir");

do {
    $link = gen_link();
    $sql = "insert ignore into `links` (`link`) values ('$link')";
    $result = mysqli_query($con, $sql);
} while (mysqli_affected_rows($con) == 0);

mysqli_close($con);

Also, a couple notes about your queries:

I changed your quotes around the table and column names to backticks, which is the correct way to quote them in sql.
Because you're including the $link variable directly in the query, you need to define your query after you give the $link variable a value - so I moved that line inside the loop. This is probably the source of your original problem where you kept looping.
It's not important in this instance because you have full control of the value you're inserting (generated in gen_link()), but it's a good idea to get in the habit of properly escaping the variables you insert into a query. Alternatively, read up a bit on prepared statements, and use them instead.

score 0 · Answer 2 · answered Jun 22 '13 at 05:30

Get the existing key values from the DB as array. Then search your current key with your existing keys using in_array() function. If it is true generate new key. If the condition is false , insert your new key.

http://php.net/manual/en/function.in-array.php

if(in_array($new_key,$existing))
{
//generate new key
}
else
{
//insert current key
}

score 0 · Answer 3 · answered Jun 22 '13 at 05:34

I'm working with Prepared Statement an using "ON DUPLICATE KEY", to change the duplicate Value with MYSQL:

$sql = "INSERT INTO ".$this->table." ".
        "(".implode(',',$fields).") VALUES
        (".implode(',',$values).")
        ON DUPLICATE KEY 
        UPDATE key_field = concat(substr(key_field,1,".($laenge_key-3)."),FORMAT(FLOOR(RAND()*999),0))

How to avoid duplicate entry in mysql with php

3 Answers3