Check for duplicate entry in database before inserting

Question

I’m trying to make a form that will check if the NRIC that is keyed exists in the database before it will insert the value into the database. However, I can’t seem to make it warn the user that there is already a duplicate entry. How do I go about doing it ?

Form:

<?php require_once("includes/session.php"); ?>
<?php require_once("includes/db_connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php require_once("includes/validation_function.php"); ?>
<?php find_selected_page(); ?>
<?php
if (isset($_POST['submit'])) {
// Process the form

//validations
    $required_fields = array("first_name", "last_name",  "nric", "address", "birthdate", "phone", "doctor");
validate_presences($required_fields);

$fields_with_max_lengths = array("phone" => 8);
validate_max_lengths($fields_with_max_lengths);

    if( verify_nric($_POST['nric'])) {
       $errors[] = 'This NRIC exists already.'; 
    }


    if( !isValid( 'phone', $_POST['phone'] ) ) {
        $errors[] = 'Please enter a valid phone number';       
    }


       if( !isValid( 'nric', $_POST['nric'] ) ) {
        $errors[] = 'Please enter a valid nric number';       
    }




    if (empty($errors)) {
      // perform Create


    $name = mysql_prep($_POST["name"]);
    $age = (int) $_POST["age"];
    $nric = mysql_prep($_POST["nric"]);
    $birthdate = mysql_prep($_POST["birthdate"]);
    $allergy = mysql_prep($_POST["medical_allergy"]);
    $history = mysql_prep($_POST["medical_history"]);
    $phone = (int)$_POST["phone"];
    $address = mysql_prep($_POST["address"]);
    $doctor = mysql_prep($_POST["doctor"]);

    //escape content


        // 2. Perform database query

    $query = "INSERT INTO patients (";
    $query .= " name, age, nric, birthdate, medical_allergies, medical_history,
    phone, address, doctor_assigned";
    $query .= ") VALUES (";
    $query .= " '{$name}', {$age}, '{$nric}', '{$birthdate}',
    '{$allergy}', '{$history}', {$phone},  '{$address}', '{$doctor}'";
    $query .= ")";

    $result = mysqli_query($connection, $query);

    if ($result ) {
        // Success
        $_SESSION["message"] = "Record Created.";


    }else {
        // Failure
        $_SESSION["message"] = "Record creation failed.";
    }   
  }
} else {
     // This is probably a GET request

} // End: If(isset($_POST['submit']))

?>
<?php $layout_context = "admin"; ?>
<link rel="stylesheet" type="text/css" href="css/dashboard-icons.css" />
<link rel="stylesheet" type="text/css" href="css/dashboard-component.css" />

<?php echo message(); ?>
<?php echo form_errors($errors); ?>

<h2>Create Patient</h2>

<form action="create_patient.php" method="post">
<p>First Name:
<input type="text" name="first_name" value="" />
</p>

<p>Last Name:
<input type="text" name="last_name" value="" />
</p>

<p> NRIC/ Foreign ID/ Passport:
<input type="text" name="nric" value="" />
</p>

<p>Date Of Birth:<br />
<input type="text" name="birthdate" value="" />
</p>

<p>Contact Number:
<input type="text" name="phone" value="" />
</p>

<p>Address: 
<textarea name="address" rows="1" cols="40" align="right"></textarea>
</p>

<p>Dentist Assigned:<br />
<input type="text" name="doctor" value="" />
</p>

<div id="limit">
<p>Medical Allergies:<br />
<textarea name="medical_allergy" rows="15" cols="40"></textarea>
</div>

<p>Medical History:<br />
<textarea name="medical_history" rows="15" cols="40"></textarea>



<input type="submit" name="submit" value="submit" />
</form>

<br />
<a href="manage_content.php">Cancel</a>
</div>

Validation Function:

function verify_nric($nric){
      global $connection;

      $query = "SELECT nric ";
      $query .= "FROM patients ";
      $query .= "ORDER BY nric ASC";
      $nric_set = mysqli_query($connection, $query);
      confirm_query($nric_set);
      if ($nric == $nric_set) {
      return $nric_set;
      }
}

function isValid( $what, $data ) {

    switch( $what ) {

        // validate a phone number     
        case 'phone':
            $pattern = "/^[0-9-+()\s]+$/";
        break;


        case 'nric':
        $pattern = "/^(A-Z)?[0-9]{7}[A-Z]$/i";
        break;

        default:
            return false;
        break;

    }

    return preg_match($pattern, $data) ? true : false;

}

confirm_query

  function confirm_query($result_set) {
    if (!$result_set) { 
      die("Database query failed: ".
    mysqli_connect_error() .
    " (" . mysqli_connect_errno(). ")"
    );
}
  }

Answer 1

Not sure what confirm_query() does but you could change your function to:

function verify_nric($nric){
  global $connection;

  $query = "SELECT nric ";
  $query .= "FROM patients ";
  $query .= "WHERE nric='".mysqli_real_escape_string($connection,$nric)."'"; //changed your query a little here
  $nric_set = mysqli_query($connection, $query);
  confirm_query($nric_set); // you haven't mentioned what this function does so I'm going to leave it that way.
  $nric_found=false;                        //Added
  if(mysqli_num_rows($nric_set)>0){         //
  $nric_found=true;                         //These
  }                                         //
  return $nric_found;                       //Lines
}

Now to explain where you went wrong:

1. Your select query returned all the nric but you weren't fetching the values and checking against $nric. You need to use mysqli_fetch_array() to get the values from the resultset $nric_set
   
   
2. $nric == $nric_set is invalid because you are comparing a resultset($nric_set) with a value $nric