Please help! Trying to show error message if variable already exists in DB!

[zoldos]

Hi, I’m using the below object oriented code. It is sort of reversed. I have a simple database and one of the columns is “email”. Basically, I want the code to search the e-mails that already exist in the DB and return an error if what was entered in a form and validated via PHP already exists. It should then offer to return the user to the form page to try again. This is what I have so far and it doesn’t work! Help please! Thanks!

require_once('config/connect.php');

$sql = "SELECT * FROM referrals WHERE email='$friendmail'";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
            
    while($row = $result->fetch_array()){
    echo "E-mail Already Referred!";
    }
    
    // Free result set
    $result->free();
  
}
die(); 
}

[phdr]

A problem with trying to SELECT data to decide if it already exists, is multiple concurrent occurrences of your script (think of duplicate form submissions, hackers intentionally trying to trigger errors, or the rare case of two different users submitting the same value) can all find that the value doesn’t exist, then try to insert it, producing errors or resulting in duplicate data.

The simple and fool-proof way to do this is to define the column(s) in your database table as a unique index, just attempt to insert the data, and detect if a unique index error occurred. If the data value doesn’t exist, it will get inserted, if it already exists, you will get an error number that you can test for to produce a message for the user.

[zoldos]

I did notice if duplicate info tries to be submitted, such as e-mail, the data is NOT inserted. But I see no error, it simply produces a blank page and the script breaks.

How do I capture this instead, and show an error? I’m literally flying in the dark here. Brand new at PHP.

[zoldos]

@phdr After some research, I tried below, but it simply skips it, even if a matching result is found, and continues to process the PHP. Can you offer some suggestions? Thanks!

$result = mysqli_query("SELECT * FROM email WHERE email='$friendmail'");
if(mysqli_num_rows($result) == 1){
echo "User exist";
die();
}

I also tried this, and same thing:

$query = "SELECT referrals, FROM email, WHERE email='$friendmail'"; 
  

$result = mysqli_query($query); 
  
if ($result) 
{ 
echo "Duplicate";    

mysqli_free_result($result); 
die();
}

[benanamen]

You dont seem to understand what you were told. You shouldn’t be SELECTING AT ALL.

1. Set a Unique constraint on the Database Column.
2. Insert the record. If there is a duplicate there will be a database error that you capture using a try/catch block.

Here is the basic jist of it.

try
{
    //INSERT Data
}
catch (PDOException $e)
{
    if ($e->getCode() == '23000')
    {
        //Handle Duplicate Error
    }
    else
    {
        throw new Exception($e);
    }
}