CODEWITHSUNDEEP
phpmysql
David
David

Reputation: 149

mysqli_num_rows not returning accurate results

I have a script that is supposed to take a form email value and query the database for duplicates. However, regardless of if I use an existing email (in the database) or a new one, I get "success".

My script looks like this:

if(!empty($_POST['email'])) {

    $email = $_POST['email'];

    // query the database for duplicates
    $query = 'SELECT email FROM user_info WHERE email = "$email"';
    $result = mysqli_query($db, $query);
    if(mysqli_num_rows($result)){
        echo "Email already taken";
    } else {
        echo "Success";
    }

Obviously mysqli_num_rows is returning some kind of data - or none at all.

Does anyone understand why I would not get a returned row if I'm selecting an email that already exists in the database?

EDIT

    $('#email').blur( function() {
    if(!this.value){
        $('#jquery_msg').html("Email can't be empty");
    } else if (!filter.test(this.value)){
        $('#jquery_msg').html("Not a valid email");
    } else {
        var emailVal = $('#email').val();
        // make an ajax call
        $.ajax({
            url: 'validation.php',
            type: 'post',
            data: {'email': 'emailVal'},
            success: function(data, status) {
                $('#jquery_msg').html(data);
                //  console.log(data);
            },
            error: function(xhr, desc, err) {
                console.log(xhr);
                console.log("Details: " + desc + "\nError:" + err);
            }
        }); // end ajax call
    }
});

The problem is in the data that's being passed to the PHP script via Ajax. What I thought was the value of $('#email').val(); was literally emailVal. Huh.. Now I need to solve how to pass the value into the Ajax call properly.

Thank you all for you help.

Upvotes: 0

Views: 700

Answers (1)

jeroen
jeroen

Reputation: 91742

This is wrong:

$query = 'SELECT email FROM user_info WHERE email = "$email"';

Apart from the potential sql injection problem, variables do not get parsed when you use single quotes.

You should use a prepared statement instead:

$query = 'SELECT email FROM user_info WHERE email = ?';

Then you can prepare the statement, bind the placeholder and get the results. Check the manual for more information.

A quick solution would also be:

$query = 'SELECT email FROM user_info WHERE email = "'
            . mysqli_real_escape_string($db, $email) . '"';

Edit: To trouble-shoot further problems, you should add error handling to your database calls. You can have mysqli throw exceptions telling you exactly what is wrong - if anything - by adding this to the top of your script:

ini_set('display_errors',1);
error_reporting(E_ALL | E_STRICT);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Upvotes: 4

Related Questions