Prevent duplicate records to a table using PHP

Question

I want to prevent duplicate values into a database table from a form using PHP.

I have created the following:

A database with a table named clients:

CREATE TABLE clients(
 firstName varchar(20),
 lastName varchar(20),
 primary key(firstName, lastName));

A simple form named form.html

<h2>Enter your First and Last Name</h2>
<form action="frm_script.php" method="post">
<p><strong>First Name:</strong><br /> <input type="text" name="firstName" /></p>
<p><strong>Last Name:</strong><br /> <input type="text" name="lastName"/></p>
<input type="submit" name="submit" value="Add Customer" />
</form>

A form processing script named frm_script.php

<?php
if(isset($_POST['submit']))
{

//get the name and comment entered by user
$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];

//connect to the database
$dbc = mysqli_connect('host', 'username', 'password', 'dbname') or die('Error connecting to MySQL server');

//insert results from the form input
$query = "INSERT IGNORE INTO clients(firstName, lastName) VALUES('$firstName', '$lastName')";

$result = mysqli_query($dbc, $query) or die('Error querying database.');

mysqli_close($dbc);
}
echo "Customer Added";
?>

So far with my frm_script.php file the above works and for a unique record displays customer added. However, for a duplicate record it throws "Error Querying Database".

How can I update the frm_script.php script for the following?

If a duplicate row is found on entering a First / Last name combination it should display the message "Client already listed" along with that record.

If no duplicate row is found on entering a First / Last name combination on the form it should insert the entry to the database and display the message "customer added"

I have read that a SELECT should be run first then an INSERT but I am not sure how to implement this into my existing code.

Replace `die('Error querying database.')` with `die(mysqli_error($dbc));` — Amal Murali, Oct 31 '13 at 18:48
Hi Amal, I like your solution. A nice easy way to see a duplicate entry is causing a problem with inserting the record. — Andy R, Nov 01 '13 at 15:58

Sudheer · Accepted Answer · 2015-08-22T14:47:35.347

  <?php
    if(isset($_POST['submit'])) {

    //get the name and comment entered by user
    $firstName = $_POST['firstName'];
    $lastName = $_POST['lastName'];

    //connect to the database
    $dbc = mysqli_connect('host', 'username', 'password', 'dbname') or die('Error connecting to MySQL server');
    $check=mysqli_query($dbc,"select * from clients where firstname='$firstname' and lastname='$lastname'");
    $checkrows=mysqli_num_rows($check);

   if($checkrows>0) {
      echo "customer exists";
   } else {  
    //insert results from the form input
      $query = "INSERT IGNORE INTO clients(firstName, lastName) VALUES('$firstName', '$lastName')";

      $result = mysqli_query($dbc, $query) or die('Error querying database.');

      mysqli_close($dbc);
    }
    echo "Customer Added";
    };
  ?>

just check for rows in your db for firstname and lastname if exists echo- your message else insert

**Warning:** This answer is wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jul 25 '20 at 00:18

score 4 · Answer 2 · answered Oct 31 '13 at 20:50

Since your primary key is (firstName, lastName), you don't need php to prevent insertion of duplicate values. Mysql does that for you, because primary keys have to be unique.
(if it was'nt your primary key you could use a unique constraint)

To display your error message "Client already listed" on duplicate entries, you can use mysqli_errno($dbc) and check for the error code 1062.

Quick fix that should do what you want: replace or die('Error querying database.'); with
or die(mysqli_errno($dbc) == 1062 ? "Client already listed" : 'Error querying database.');

score 1 · Answer 3 · answered Oct 31 '13 at 18:48

1

After you process the $_POST data, use a SELECT query followed by an if statement to check if there are any rows in the table with the same first name and last name as the $_POST first name and last name data.

If there is, display a message that this data has already been inserted.

If there isn't, use an INSERT query to add this data to the database table, and display a message that this data has been added to the table.

answered Oct 31 '13 at 18:48

Charles

4,372
9
41
80

Hi goddfree, I did just that using the reply from Sai above.Still getting errors though - the first: mysqli_query() expects at least 2 parameters and the second: mysqli_num_rows() expects parameter 1 to be mysqli_result, null. I don't know exactly what these mean - any ideas? Thanks ;-) – Andy R Nov 01 '13 at 16:06
`mysqli_query() expects at least 2 parameters` this error means you have not pass the connection or your connection is not establish to data base. `mysqli_num_rows() expects parameter 1 to be mysqli_result, null` means you query is not working properly. test your query in database first then use it in php as good practice – Ali Raza Jul 13 '19 at 20:25

score 1 · Answer 4 · answered Oct 31 '13 at 19:05

I have this problem all the time. I've gone from using an auto-incremented primary to using an md5 hash most of the time. So while I haven't implemented this myself yet - no opportunity/need presently - you could do create a primary key (VARCHAR 32) that is an md5 hash of the first and last name. Like this:

$key = md5(strtolower($firstName . $lastName));

Make the column this key goes into te primary key. Then you use an INSERT IGNORE and you can ignore any errors.

Then you run a SELECT on the $key to return everything related to $keyand display any info you have on the user in a friendly format. You don't have to show the user an error at that point, simply provide them with the info already captured. If you are worried about users with the same name seeing each other's data then the data can be obfuscated. For example instead of showing a phone number (333) 444-5555 you show (333) 444-5*5 or (333) **-5555. Something that gives the user enough info to know if it is them or someone else with the same name.

Typically I've used the SELECT, if empty INSERT and SELECT again, method. And it works but it is significantly slower than a simple INSERT IGNORE and then SELECT. I use a DAO I've built on top of PDO and the INSERT IGNORE works fine when a single column is the primary key. I have not tried it on a primary key based on more than 1 column nor have I tried it with the mysqli_* function.

Wow gabe, thanks for your thorough answer. It definitely gives me something to work towards. Thanks for the response I will have to give it a try ;-) — Andy R, Nov 01 '13 at 16:02
@AndyR no problem. When I wrote this I didn't think I would need it but I'm in the middle of it right now. For example, I am capturing all of my AdWords campaigns and their status (Paused, Active, etc). Well typically I would put all the campaigns in one table, the statuses in another, and then create link table - camp2stat. However, the status can change over time and I'd like to store when it changed. So I now have a link table with a date not just the ID for the campaign and the ID for the status. However, I don't want to store the status for every day just when they change... — Gabe Spradlin, Nov 01 '13 at 16:38

Prevent duplicate records to a table using PHP

4 Answers4

Linked

Related