PHP MySQLi jQuery checking database value doesn't exist

Question

I'm using the below PHP and jQuery script and I'm trying to check that the username put through doesn't exist in the database already.

PHP

        if(mysqli_query($con, "SELECT id FROM user WHERE user_name='$username'")){
            echo '1'; //If there is a  record match in the Database - Not Available
        } else {
            echo '0'; //No Record Found - Username is available 
        }
    }
?>

jQuery

$(document).ready(function(){
    $("fieldset input[name=username]").change(function(){
        var username = $("fieldset input[name=username]").val();//Get the value in the username textbox
        if(username.length > 3){
            $(".username_status").html('<img src="loader.gif" align="absmiddle">&nbsp;Checking availability...');
            $.ajax({
                type: "POST",  
                url: "/donald/settings/check-username.php",  //file name
                data: "username="+ username,  //data
                success: function(data){
                    console.log("server_response: "+data);
                    if(data == '0'){
                        $(".username_status").html('<font color="Green"> Available </font>');
                    } else  if(data == '1'){  
                        $(".username_status").html('<font color="red">Not Available </font>');
                    }
                } 
            }); 
        } else {
            $(".username_status").html('<font color="#cc0000">Username too short</font>');
        }
        return false;
    });
});

The result always comes through as 1

mysqli_query see Return Values section. And btw you wanna bookmark php.net — Ejaz
– Ejaz, Commented Apr 15, 2013 at 10:58
@YourCommonSense please share an example of how to prevent this — ngplayground
– ngplayground, Commented Apr 15, 2013 at 11:53

Vyktor · Accepted Answer · 2013-04-15 10:58:08Z

3

If you take a look at mysqli_query() documentation:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

So with valid query it should never return FALSE. Therefore you probably want to do this, using mysqli_result:

$q = mysqli_query($con, "SELECT id FROM user WHERE user_name='$username'");
if( $q && $q->num_rows ){
    echo '1';
} else {
    echo '0';
}

answered Apr 15, 2013 at 10:58

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ivan Yonkov · Accepted Answer · 2013-04-15 10:57:39Z

1

mysqli_query() returns true everytime you have a correct query, no matter its result. You need to use another mysqli_ function which will return rows i.e. mysqli_num_rows, _result or _fetch_array.

Ofcourse, each of them, has its own syntax, so you have to check it and use it properly in order to check if there's a result or not. i.e. if num_rows>0 or result($query, 0)

answered Apr 15, 2013 at 10:57

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

Comments

alwaysLearn · Accepted Answer · 2013-04-15 11:03:24Z

1

You should check mysqli_num_rows instead . mysqli_query always return true if query is executed

Try changing php code to

$resource = mysqli_query($con, "SELECT id FROM user WHERE user_name='$username'");
$rowCount = mysqli_num_rows($resource);  
 if($rowCount > 0){
        echo '1'; //If there is a  record match in the Database - Not Available
    } else {
        echo '0'; //No Record Found - Username is available 
    }
}

edited Apr 15, 2013 at 11:03

answered Apr 15, 2013 at 11:01

alwaysLearn

6,9507 gold badges45 silver badges69 bronze badges

Comments

Eugen Rieck · Accepted Answer · 2013-04-15 10:59:22Z

-1

(mysqli_query($con, "SELECT id FROM user WHERE user_name='$username'")) will allways evaluate as true, if the query went OK. THis is not what you want.

answered Apr 15, 2013 at 10:59

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

PHP MySQLi jQuery checking database value doesn't exist

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related