checking if table contains value won't work (SELECT COUNT)

Question

I want to prevent user from registering an email address that is already set in my table. I am doing it like this:

$emailcheck = $bdd->prepare('SELECT COUNT(*) FROM ' . DB_TABLE . ' WHERE MATCH(email) AGAINST '.$_POST['email'].' ');
$emailcheck->execute();
$emailcheckrows = $emailcheck->fetch();

if ($emailcheckrows > 0) {
    $_SESSION['err_msg']="This email address is already registered";
    $error=true;
    $emailcheck->closeCursor();
}

But this doesn't work. I have already tried almost everything (also with LIKE, = and in-array). The "if" is not executed when I enter an already submitted email.

Any idea ? Thank you

If you want the email field to be unique - why not just add a `UNIQUE` index on the database table? It'll **definitely** be unique then. — CD001, Nov 07 '16 at 10:40

score -1 · Accepted Answer · answered Nov 07 '16 at 10:48

you can use it as an simple function like:

class Validation {
    public static function emailUnique($conn, $email)
        {
            $sql = "SELECT email FROM formular WHERE email = '".$email."'";
            $emailUnique = $conn->query($sql);
            return (boolean) $emailUnique->num_rows;
        }
}

this returns a true if an entry has been found and false if not and then you can call your function in your script like this. i've used this together with bootstrap-alerts:

$errorField = "";
$labelClass = array(
 "emailUnique"=>"",
);
$email = mysqli_real_escape_string($conn, $_POST["email"]);
$errorMessages["emailUnique"] = Validation::emailUnique($conn ,$email);

$DisplayErrorForm = array();
$hasErrors = false;
$formErrorMessage = "";
foreach ($labelClass as $key=>$value) {
          if($errorMessages[$key]){
          $labelClass[$key] = "has-error";
          $hasErrors = true;

          $DisplayErrorForm["emailUnique"] = array("style" => "red", "text" => "Email is already taken");

          if($key == "emailUnique"){
               $formErrorMessage .= "<li style='" . $DisplayErrorForm["emailUnique"]["style"] . "'>" . $DisplayErrorForm["emailUnique"]["text"] . "</li>";
             }
          }
       }

if(count($DisplayErrorForm)) {
  $errorField = "<div class=\"alert alert-danger\">".
                            "<strong>Whoops!</strong> There were some problems with your input.<br><br>".
                            "<ul>".$formErrorMessage."</ul>".
                        "</div>";
        }
        if (!$hasErrors) {
        //Do the database input

and then down in your html part call the $errorField

<div>
<?php echo $errorField; ?>
</div>

this function is simple but insecure – Your Common Sense Nov 07 '16 at 10:48 — Your Common Sense, Nov 07 '16 at 10:48
@YourCommonSense can you explain what insecure is? – Blueblazer172 Nov 07 '16 at 10:53 — Blueblazer172, Nov 07 '16 at 10:53
Yes this is even better. Thanks – François Roduit Nov 07 '16 at 11:19 — François Roduit, Nov 07 '16 at 11:19
@FrançoisRoduit accept it if it worked :) – Blueblazer172 Nov 07 '16 at 11:20 — Blueblazer172, Nov 07 '16 at 11:20
@FrançoisRoduit thanks :) – Blueblazer172 Nov 07 '16 at 11:24 — Blueblazer172, Nov 07 '16 at 11:24

François Roduit · Answer 2 · 2016-11-07T11:23:10.887

The answer was to bind the value and use rowCount(). It worked with the following code:

$emailcheck = $bdd->prepare('SELECT * FROM ' . DB_TABLE . ' WHERE email = ?');
$emailcheck->bindValue( 1, $_POST['email'] );
$emailcheck->execute();


if ($emailcheck->rowCount() > 0) {
    $_SESSION['err_msg']="e-mail addresse already registered";
    $erreur=true;
    $emailcheck->closeCursor();
    header ('Location: form.php');
}

query instead of prepare is maybe easier..

checking if table contains value won't work (SELECT COUNT)

2 Answers2