How do I tell the user their email is already registered?

Question

I have put a UNIQUE index on my email column in the database, and when I enter an already registered email the database does not update. so that works well. I now need to tell the user entering an already existing email(on the signup page) that it is already registered and redirect them to the homepage.

Please check my SQL injection code too and correct if there are any errors.

<?php

$fullname = $_POST['fullname'];
$email = $_POST['email'];
$mobilenumber = $_POST['mobilenumber'];

//prevent sql injection
$fullname = stripslashes($fullname);
$email = stripcslashes($email);
$mobilenumber = stripslashes($mobilenumber);
$fullname = mysql_real_escape_string($fullname);
$email = mysql_real_escape_string($email);
$mobilenumber = mysql_real_escape_string($mobilenumber);


//Database Connection

$conn = new mysqli("#","#","#","#");
if($conn->connect_error){
    die('connection Failed : '.$conn->connect_error);
}else{
        $stmt = $conn->prepare("insert into signup(fullname,email,mobilenumber)values(?,?,?)");
        $stmt->bind_param("ssi",$fullname,$email,$mobilenumber);
        $stmt->execute();
        header("Location:thankyou.html");
        $stmt->close();
        $conn->close();

}

?>

Answer 1

As per the comment - if you do a simple select before trying to do the insert you can fork the program logic and let the user know.

<?php

    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( 
        $_POST['fullname'], 
        $_POST['email'], 
        $_POST['mobilenumber'] 
    )){
        
        $fullname = $_POST['fullname'];
        $email = $_POST['email'];
        $mobilenumber = $_POST['mobilenumber'];
        
        
        
        $dbport =   3306;
        $dbhost =   'localhost';
        $dbuser =   'dbo-user-xxx';
        $dbpwd  =   'dbo-pwd-xxx';
        $dbname =   'db-xxx';
        
        
        
        error_reporting( E_ALL );
        mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
        $conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
        
        try{
        
            #check email before insert
            $sql='select `email` from `signup` where `email`=?';
            $stmt=$conn->prepare( $sql );
            $stmt->bind_param('s',$email);
            $stmt->execute();
            $stmt->store_result();
            
            if( $stmt->num_rows==0 ){
                /* email does not exist - perform insert */
                $sql='insert into `signup` ( `fullname`, `email`, `mobilenumber` ) values ( ?, ?, ? )';
                $stmt=$conn->prepare( $sql );
                $stmt->bind_param('sss', $fullname, $email, $mobilenumber );
                $stmt->execute();
                $stmt->close();
                $conn->close();
                
                exit( header('Location: thankyou.html') );
                
            }else{
                /* email does exist - tell user */
                $stmt->free_result();
                $stmt->close();
                
                exit( header('Location: ?error=true&email=true' ) );
            }
            
        }catch( mysqli_sql_exception $e ){
            exit( $e->getMessage() );
        }
    }
?>

Alternatively you can try/catch as before but use the return error code to fork the logic

<?php
    /*
    
        mysql> describe signup;
        +--------------+------------------+------+-----+---------+----------------+
        | Field        | Type             | Null | Key | Default | Extra          |
        +--------------+------------------+------+-----+---------+----------------+
        | id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
        | fullname     | varchar(50)      | NO   |     | NULL    |                |
        | email        | varchar(64)      | NO   | UNI | NULL    |                |
        | mobilenumber | varchar(16)      | NO   |     | NULL    |                |
        +--------------+------------------+------+-----+---------+----------------+
        
        mysql> select * from signup;
        +----+----------+-----------------------------+--------------+
        | id | fullname | email                       | mobilenumber |
        +----+----------+-----------------------------+--------------+
        |  1 | fred     | fred.flintstone@bedrock.com | 123          |
        +----+----------+-----------------------------+--------------+
    */




    /* Attempt to insert duplicate - but use error code 1062 to fork the logic */
    $dbport =   3306;
    $dbhost =   'localhost';
    $dbuser =   'dbo-user-xxx';
    $dbpwd  =   'dbo-pwd-xxx';
    $dbname =   'db-xxx';
            
    
    /* same email and phone number but different fullname */
    $email='fred.flintstone@bedrock.com';
    $fullname='freddy boy';
    $mobilenumber=123;
    
    
            
    error_reporting( E_ALL );
    mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
    $conn = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
    
    try{
    
        $sql='insert into `signup` ( `fullname`, `email`, `mobilenumber` ) values ( ?, ?, ? )';
        $stmt=$conn->prepare( $sql );
        $stmt->bind_param('sss', $fullname, $email, $mobilenumber );
        $stmt->execute();
        

    }catch( mysqli_sql_exception $e ){
        if( $e->getCode()==1062 ){
            /* redirect the user and let them know the email already exists */
            exit( header( sprintf('Location: ?error=%s',$e->getMessage() ) ) );
        }
    }
    
?>

Answer 2

  // first check the database to make sure 
  // a email does not already exist with the same  email
  $fullname = $_POST['fullname'];
  $email = $_POST['email'];
  $mobilenumber = $_POST['mobilenumber'];

  $user_check_query = "SELECT * FROM signup WHERE email='$email'LIMIT 1";
  $result = mysqli_query($cons, $user_check_query);
  $emailcheck= mysqli_fetch_assoc($result);
  
  if ($emailcheck) { // if email exists
    if ($emailcheck['email'] === $email) {
      array_push($errors, "email already exists");
    header('location: index.php');
    }

  }

  // Finally, register user if there are no errors in the form
  if (count($errors) == 0) {
     $sql = "insert into 
     signup(fullname,email,mobilenumber)values($fullname,$email,$mobilenumber)";
     $runsql = mysqli_query($cons, $sql);

    if($runsql) {
        header("Location:thankyou.html");
    } else {
        echo"Some thing is wrong";
    }
  }
}