Check to See If an Email Is Already in the Database Using Prepared Statements

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

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

PHP prepared statements - check if a value already exists

It seems that you have an issue regarding unbuffered result. You should use store_result:

$getEmail->execute();
$getEmail->store_result();
$countRows = $getEmail->num_rows;

Checking if username already exists in db prepared statement

selec count(*) ... always returns 1 record, with the count. So, when you check else if($stmt->num_rows > 0), then it always returns true. You should check the value returned by count, not the number of records.

check if the email is already in database when creating a new acount

Why don't you add a unique constraint for email in the table using below query?

ALTER TABLE USERS ADD CONSTRAINT UNQ_EML UNIQUE (EMAIL)

If your code tries to insert an already existing email, then MySQL will throw such duplicate data exception while inserting and you can easily catch it and identify. Below is pseudo code to check.

if( mysql_errno() == 1062) {
    // Duplicate key
} else {
    // Non duplicate
}

https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

Prepared Statements Checking if row exists

mysqli_num_rows applicable to SELECT statement.

$stmt_check = $conn->prepare("SELECT * FROM users WHERE email =? AND password =?");
$stmt_check->bind_param("ss", $email, $password);
$stmt_check->execute();
if(mysqli_num_rows($stmt_check) > 0)

Updated Code

<?php 

include '../config.php'; 

$email = $_POST['email'];
$password = $_POST['password'];

$stmt_check = $conn->prepare("SELECT * FROM users WHERE email =? AND password =?");
$stmt_check->bind_param("ss", $email, $password);
$stmt_check->execute();
if($stmt_check->num_rows > 0){
  echo 'user already exists';
} else {
  $stmt = $conn->prepare("INSERT INTO users (email, password) VALUES (?, ?)");
  $stmt->bind_param("ss", $email, $password);
  $stmt->execute();
  header('Location: ../login.php');
}

$stmt->close();
$conn->close();
?>

Quick Link

• mysqli_num_rows
• mysql_num_rows

Which States,

This command is only valid for statements like SELECT or SHOW that
return an actual result set. To retrieve the number of rows affected
by a INSERT, UPDATE, REPLACE or DELETE query, use
mysql_affected_rows().

Edit 1

Change

if(mysqli_num_rows($stmt_check) > 0){

To

if($stmt_check->num_rows > 0){

See Example2 of PHP mysqli_num_rows() Function

PDO Prepared Select statement for checking if a user exists

Just do a simple select. I recommend Using bind value if you may possibly have a null email.

$STH = $dbh->prepare("SELECT email FROM users WHERE email = :email");
$STH->bindValue(':email', $email);
try{
    $STH->execute();
}

Then just check if any records are returned. If so, Update, don't insert. good luck.

---

Related Topics