François Roduit François Roduit - 27 days ago 8
MySQL Question

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

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

Answer

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>
Comments