jQuery Validate Remote - Check if email already exists

Question

I have a bit of an issue getting jQuery Validation to check if an email address already exists in a mysql table.

Every time I submit the form, it tells me the email already exists, even though I know it doesn't.

Here's the code I have:

validation.js

$(document).ready(function () {

    $('#signup').validate({ 
        errorLabelContainer: "#cs-error-note",
        wrapper: "li",
        rules: {
            email: {
                required: true,
                email: true,
                remote: {
                    url: "check-username.php",
                    type: "post"
                }
            }
        },
        messages: {
            email: {
                required: "Please enter your email address.",
                email: "Please enter a valid email address.",
                remote: "Email already in use!"
            }
        },
        submitHandler: function(form) {
            form.submit();
        }
    });

});

check-username.php

<?php
    require('../../private_html/db_connection/connection.php');

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $query = $conn->prepare("SELECT * FROM 'user_accounts' WHERE email = '" . $_POST['email'] . "'");
    $query->execute();

    if( $query->rowCount() > 0 ){
        echo 'true';
    }
    else{
        echo 'false';
    }
?>

Your help is much appreciated!

Solution 1

You have to change the row count if / else condition in query

Script

<script>
$(document).ready(function () {
    $('#signup').validate({ 
    errorLabelContainer: "#cs-error-note",
    wrapper: "li",
    rules: {
        email: {
            required: true,
            email: true,
                remote: {
                    url: "check-username.php",
                    type: "post"
                 }
        }
    },
    messages: {
        email: {
            required: "Please enter your email address.",
            email: "Please enter a valid email address.",
            remote: "Email already in use!"
        }
    },
    submitHandler: function(form) {
                        form.submit();
                     }
    });
});
</script>

HTML

<form class="form-inline" role="form" id="signup">
    <div class="form-group">
    <label for="email">Email address:</label>
        <input type="email" class="form-control" name="email" id="email">
    </div>
</form>

PHP

Warning Do not use this PHP code reason rowCount() may not work so skip it and jump to code at bottom of answer.

<?php
    require('../../private_html/db_connection/connection.php');
    $conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password);    
    if(isset($_POST['email'])) {
        $email = $_POST['email'];
        $query = $conn->prepare("SELECT email FROM user_accounts WHERE email = '$email'");
        $query->execute();
        if( $query->rowCount() > 0 ){
            echo 'false';
        } else {
            echo 'true';
        }
    }
?>

Edit: As @Jay Blanchard very consistent and dead sure that above code will not work

• rowCount() doesn't work for SELECT statements. stackoverflow.com/a/31569733/1011527

• Nope, this will not work because rowCount() doesn't work for SELECT statements. You're not getting a row count at all.

• Try echoing $query->rowCount() and you'll see the issue

and makes me wonder How the above code is working on my live server when It shouldn't so I done some digging and found this;

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

and this

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

Source of Above statements php.net manuals

In both above statements, some databases and For most databases rowCount() does work but on other-hand

• should not be relied on for portable applications
• use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

As OP only wants the count of rows and not all the data of all the rows so can be also done like this. Credit goes to @Jay Blanchard

Use This Code Example

made some changes in PHP, use isset function.

<?php
    require('../../private_html/db_connection/connection.php');
    $conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password);    
    if(isset($_POST['email'])) {
        $email = $_POST['email'];
        $query = $conn->prepare("SELECT email FROM user_accounts WHERE email = '$email'");
        $query->execute();
        $rows = $query->fetchAll();
        $total_rows = count($rows);
            if( $total_rows > 0 ){
                echo 'false';
            } else {
                echo 'true';
            }
    }
?>

See in Action

Solution 2

rowCount() will not work in the situation. From the docs:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Note that it doesn't return anything for a SELECT statement. To get the row count in your code you would do this:

if(isset($_POST['email'])) {
    $email = $_POST['email'];
    $query = $conn->prepare("SELECT email FROM user_accounts WHERE email = ?");
    $query->execute(array($email));
    $rows = $query->fetchAll();
    $num_rows = count($rows);
    if( $num_rows > 0 ){
        echo 'true - email exists';
    } else {
        echo 'false - email does not exist';
    }
}

In order to avoid the possibility of SQL Injection Attack I use a prepared statement, sending an array (of one) containing the values to be used in the query in the execute() statement.