php - Check to see if an email is already in the database using prepared statements

Question

I am trying to change my code to msqli prepared statements from mysql. I am not sure how to adapt my code that currently works to check if there is an email already in the database. Below is the code I am currently using that works. How do I change this into a prepared statement and get the same result?

//if email is equal to an email already in the database, display an error message

if(mysql_num_rows(mysql_query("SELECT * FROM users WHERE email = '".mysql_real_escape_string($_POST['email'])."'")))
{
  echo "<p class='red'>Email is already registered with us</p>";
} else {
  // missing code?
}

Answer 1

Should be something like this:

// enable error reporting for mysqli
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// create mysqli object
$mysqli = new mysqli(/* fill in your connection info here */);

$email = $_POST['email']; // might want to validate and sanitize this first before passing to database...

// set query
$query = "SELECT COUNT(*) FROM users WHERE email = ?";

// prepare the query, bind the variable and execute
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $email);
$stmt->execute();

// grab the result
$stmt->bind_result($numRows);
$stmt->fetch();

if ($numRows) {
    echo "<p class='red'>Email is already registered with us</p>";
} else {
    // ....
}

This link may help you as well:

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php