Duplicate check before adding into database

Question

I have a code which kinda works, but not really i can't figure out why, what im trying to do is check inside the database if the URL is already there, if it is let the user know, if its not the go ahead and add it.

The code also makes sure that the field is not empty. However it seems like it checks to see if the url is already there, but if its not adding to the database anymore. Also the duplicate check seems like sometimes it works sometimes it doesn't so its kinda buggy. Any pointers would be great. Thank you.

    if(isset($_GET['site_url']) ){

    $url= $_GET['site_url'];


    $dupe = mysql_query("SELECT * FROM $tbl_name WHERE URL='$url'");
    $num_rows = mysql_num_rows($dupe);
    if ($num_rows) {
    echo 'Error! Already on our database!';
    }
    else {
    $insertSite_sql = "INSERT INTO $tbl_name (URL) VALUES('$url')";
    echo $url;
    echo ' added to the database!';

    }

}
else {
echo 'Error! Please fill all fileds!';
}

Answer 1

Instead of checking on the PHP side, you should make the field in MySQL UNIQUE. This way there is uniqueness checking on the database level (which will probably be much more efficient).

ALTER TABLE tbl ADD UNIQUE(URL);

Take note here that when a duplicate is INSERTed MySQL will complain. You should listen for errors returned by MySQL. With your current functions you should check if mysql_query() returns false and examine mysql_error(). However, you should really be using PDO. That way you can do:

try {
    $db = new PDO('mysql:host=localhost;db=dbname', $user, $pass);

    $stmt = $db->query('INSERT INTO tbl (URL) VALUES (:url)');
    $stmt->execute(array(':url' => $url));
} catch (PDOException $e) {
    if($e->getCode() == 1169) { //This is the code for a duplicate
        // Handle duplicate
        echo 'Error! Already in our database!';
    }
}

Also, it is very important that you have a PRIMARY KEY in your table. You should really add one. There are a lot of reasons for it. You could do that with:

ALTER TABLE tbl ADD Id INT;
ALTER TABLE tbl ADD PRIMARY KEY(Id);

Answer 2

You should take PhpMyCoder's advice on the UNIQUE field type.

Also, you're not printing any errors.

Make sure you have or die (mysql_error()); at the end of your mysql_* function(s) to print errors.

You also shouldn't even be using mysql_* functions. Take a look at PDO or MySQLi instead.

You're also not executing the insert query...

Try this code:

if(isset($_GET['site_url']) ){

$url= $_GET['site_url'];


$dupe = mysql_query("SELECT * FROM $tbl_name WHERE URL='$url'") or die (mysql_error());
$num_rows = mysql_num_rows($dupe);
if ($num_rows > 0) {
echo 'Error! Already on our database!';
}
else {
$insertSite_sql = "INSERT INTO $tbl_name (URL) VALUES('$url')";
mysql_query($insertSite_sql) or die (mysql_error());
echo $url;
echo ' added to the database!';

}

}
else {
echo 'Error! Please fill all fileds!';
}

Answer 3

As PhpMyCoder said, you should add a unique index to the table.

To add to his answer, here is how you can do what you want to do with only one query.

After you add the unique index, if you try to "INSERT INTO" and it result in a duplicate, MySQL will produce an error.

You can use mysql_errno() to find out if there was a duplicate entry and tell the user.

e.g.

$sql = "INSERT INTO $tbl_name (URL) VALUES('$url')";
$result = mysql_query($sql);

if($result === false) {
  if(mysql_errno() == $duplicate_key_error) {
    echo 'Error! Already in our database!';
  } else {
    echo 'An error has occurred. MySQL said: ' . mysql_error();
  }
}

mysql_error() will return the mysql error in plain english.

mysql_errno() returns just the numeric error code. So set $duplicate_key_error to whatever the code is (I don't know it off the top of my head) and you are all set.

Also note that you don't want to print any specific system errors to users in production. You don't want hackers to get all kinds of information about your server. You would only be printing MySQL errors in testing or in non-public programs.

ALSO! Important, the mysql functions are deprecated. If you go to any of their pages ( e.g. http://php.net/manual/en/function.mysql-errno.php) you will see recommendations for better alternatives. You would probably want to use PDO.

Anyone who wants to edit my answer to change mysql to PDO or add the PDO version, go ahead.