Prevent duplicate records to a table using PHP [duplicate]

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.

Answer 1

  <?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

Answer 2

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.');