Check If Record Exists Before Insert PHP

penwell · May 18, 2020, 6:37pm

I need to check if the “username” and “afri_mac” exist before a user can register a new account. If the account exists i should be able to echo out “account exists” if the account dose not exist it should then insert the new record

 <?php

 $servername='localhost';
 $usernames='CCC';
 $password='XXX';
 $dbname = "RRR";

 if(isset($_POST['insert']))      
 {
  try {

    $afri_first_name = $_POST['afri_first_name'];
    $afri_last_name = $_POST['afri_last_name'];
    $username = $_POST['username'];
    $value = $_POST['value'];
    $afri_address = $_POST['afri_address'];
    $afri_street = $_POST['afri_street'];
    $afri_route = $_POST['afri_route'];
    $afri_city = $_POST['afri_city'];
    $afri_postal = $_POST['afri_postal'];
    $afri_country = $_POST['afri_country'];
    $afri_mac = $_POST['afri_mac'];
    $afri_ip = $_POST['afri_ip'];
    $attribute = $_POST['attribute'];
    $op = $_POST['op'];
    $afri_note = $_POST['afri_note'];
    $afri_phone = $_POST['afri_phone'];

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $usernames, $password);
    /* set the PDO error mode to exception */
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO radcheck (afri_first_name,afri_last_name,username,value,afri_address,afri_street,afri_route,afri_city,afri_postal,afri_country,afri_mac,afri_ip,attribute,op,afri_note,afri_phone)
    VALUES ('$afri_first_name', '$afri_last_name','$username','$value','$afri_address','$afri_street','$afri_route','$afri_city','$afri_postal','$afri_country','$afri_mac','$afri_ip','$attribute','$op','$afri_note','$afri_phone')";
    $conn->exec($sql);
    /* echo "<meta http-equiv='refresh' content='0'>"; */
    echo "<div class='alert alert-success fade show' align='center'>Registration <strong>Successfully</strong> Completed</div>";              }
    catch(PDOException $e)
    {

      echo "<div class='alert alert-danger fade show' align='center'>Oops... Your registration <strong>failed</strong>. Please try again</div>";
    }

    $conn = null;
  }
  ?>

phdr · May 19, 2020, 12:02am

Define the column(s) in your database table to be a unique index, then inside the catch{…} logic, test if the INSERT query produced a duplicate index error number. If the error number is not for a duplicate index, re-throw the exception and let php handle it. Since you have more than one unique index, you would then execute a SELECT query to find which column(s) already have values matching the data you tried to insert.

Next, use a prepared query when supplying external, unknown, dynamic values to a query when it is executed. You should be using prepare() (with ? place-holders in the sql query statement) and execute() (with an array of the values), not exec().

Some other points for the posted code -

Name your database connection variables with names that indicate they are for the connection, such as $db_host, $db_user, … This will eliminate things like the $usernames/$username variables in your code.
Your form processing code should detect if a post method form was submitted, not test if the submit button is set.
Don’t write out line after line of code copying variables to other variables. This is just an error-prone waste of time. Instead, keep the set of form data as an array, and just operate on elements of the array. This will let you do things like trim all the data at once using a single line of code and will lead to dynamically validating and processing the form data.
Your form processing code should trim all the input data, then validate it, storing validation error messages in an array. This array is also an error flag. If the array is empty, you can use the submitted form data. To display the validation error messages, test/display the contents of this array at the appropriate point in the html document.
When you make the database connection, you should ALSO set the character set to match your database tables, set emulated prepared queries to false, and set the default fetch mode to assoc.
Php closes the database connection when you script ends, so in most cases you don’t need to do so in your code.