Ajax call with PHP / MySQLi query returning wrong results

Question

I am trying to check if an input value already exists in my database.

So far I got the following but this always returns me "Invalid" (even if I enter a matching value) so my guess is I have an error in the part after my SELECT query or in my If Else logic.

Can someone tell me how to do this right?

My JS:

$('#btnCheck').click(function() {   
    var username = $('#username').val();

    $.ajax({
        url: 'userSubmit.php',
        type: 'POST',
        data: {username: username},
        success: function(response){
            if(response == 'valid') {
                alert('Valid');
            } else {
                alert('Invalid');
            }
        }
    });
});

My PHP:

$username = $_POST['username'];      
$conn = new mysqli($host, $username, $password, $database);
if($conn->connect_error) {
    die("Connection Error: " . $conn->connect_error);
}
$stmt = $conn->prepare("SELECT username FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

if($stmt->num_rows > 0) {
    echo "valid";
} else {
    echo "invalid";
}

Answer 1

There is no reason to use num_rows, which not only is confusing, easy to misuse, but also could cause performance issues if misused.

Just fetch COUNT(1) and then fetch a single column from the first row in your SQL result.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($host, $username, $password, $database);
$conn->set_charset('utf8mb4');

$stmt = $conn->prepare("SELECT COUNT(1) FROM users WHERE username = ?");
$stmt->bind_param("s", $_POST['username']);
$stmt->execute();
$exists = $stmt->get_result()->fetch_row()[0];

if($exists) {
    echo "valid";
} else {
    echo "invalid";
}

If you wanted for some strange reason to stick with num_rows you would either have to call store_result() on the statement and then get the num_rows from the statement or call get_result() and read the value of num_rows on the returned result set.

Answer 2

You need to call store_result() in order for the number of rows to actually be recorded before calling num_rows:

...
$stmt->execute();
$stmt->store_result();

if($stmt->num_rows > 0) {
    ...

The reason why you must call store_result() is that without this, no results have actually been fetched, and the number of rows (num_rows) will therefore be 0.

Reference the official documentation for num_rows:

Returns the number of rows in the result set. The use of mysqli_stmt_num_rows() depends on whether or not you used mysqli_stmt_store_result() to buffer the entire result set in the statement handle.

A link to this is here.