0

I am trying to check if a name exists. Query should return 0 because the field is empty but returning 1 instead. help.

function profile_exists($user) { //checks that profile exists
    $profile_ex = mysql_query(
        "SELECT COUNT(profile_name) FROM user_info WHERE id = '{$user}'");

    if(!confirm_query($profile_ex)) {
        return false;   
    } else {
        if (mysql_result($profile_ex, 0) == 1) {
            echo mysql_result($profile_ex, 0);
            exit;
            return true;
        } else {
            return false;
        }
    }
}
Improve this question
1

3 Answers 3

Reset to default
2

You have one row instead of 0 rows because COUNT(*) returns one row of data, the count which has the value of 0 in this case.

You will need to run the query and then check the value of count.

Example:

$result = mysql_query("SELECT COUNT(*) AS count FROM ...");
$row = mysql_fetch_assoc($result);

if( $row['count'] == 0){
    return False;
}else{
    return True;
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

"What if count is > 1"; Then something really really bad had happened and False should be returned; look at the query in the question WHERE id = '{$user}' - this makes the assumption that id is a auto-incrementing prime key which is/should always be unique.
That's a pretty large assumption (id is a PK with AUTO_INCREMENT). Basically you're saying that the user doesn't exist if they have more than 1 profile.
Basically, I'm answering their question of why they have a result set of one row instead of zero rows. The table structure is irrelevant to that ends, and without the table schema I made an assumption.
1

Your syntax looks abit complex, try this instead:

$profile_ex = mysql_query("SELECT * FROM user_info WHERE id = '$user'");
$profile_ex = mysql_num_rows($profile_ex);

if ( $profile_ex > 0 ) {
echo "exists"; } else {
echo "doesn't exist";
}

Hope this helps

Improve this answer

Comments

0

You should be using mysqli_ or PDO with prepared statements to prevent against SQL injection.

It will always return 1 because you are using an aggregate function (COUNT). Select a column from your table instead:

SELECT profile_name FROM ...

However, if you are only interested in checking whether the profile exists, use mysqli_num_rows. If you wish to check whether the profile exists and return data, use mysqli_fetch_assoc.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.