mysqli_num_rows returns 1 no matter what

Question

When I do a SQL search in phpMyAdmin (substituting the variable for the actual value) it returns the correct row number but when using PHP to return this value it always returns 1 no matter what. Thanks in advance.

function user_exists($username) {
    $link = mysqli_connect('localhost','root','','test');
    $username = sanitize($username);
    $query = mysqli_query($link, "SELECT COUNT(`user_id`) FROM `new_base` WHERE `username`='$username'");
    $row_cnt = mysqli_num_rows($query);
    echo $row_cnt;
    mysqli_free_result($query);
    mysqli_close($link);
}

Answer 1

Count retrives a single row. Try testing the SQL in phpmyadmin and see the result. This single row returned by the query has the number you are looking for.

I suggest also doing something like

SELECT COUNT('user_id') AS user_matches FROM ....

This way you can access the via the 'user_matches' key.

*I would not recommend using SELECT * FROM ... * with the num_rows, this would be very slow compared to a count().

Answer 2

When you use COUNT(*) you always get one row returned even if the count is zero.

You either:

1. Want to remove the count(*) and then use mysqli_num_rows() or
2. Get the result of count(*)

.

$row = mysqli_fetch_assoc($query);
echo $row['COUNT(`user_id`)'];