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);
}

your reading how many rows the `SELECT` returned not the row number — cmorrissey, Aug 05 '13 at 18:40
`COUNT` is a group function. It combines the rows into one result. What *exactly* do you want this query to return? You probably need a `GROUP BY` in there. — gen_Eric, Aug 05 '13 at 18:42

score 6 · Accepted Answer · edited Aug 17 '16 at 17:47

6

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

You either:

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

.

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

edited Aug 17 '16 at 17:47

Funk Forty Niner

74,450
15
68
141

answered Aug 05 '13 at 18:40

John Conde

217,595
99
455
496

Thanks, went with your first suggestion – jayjay Aug 05 '13 at 18:44
I fixed a slight typo there John, after Googling something for another question today. Let's see if they come your way ;-) Question being http://stackoverflow.com/q/39002738/ – Funk Forty Niner Aug 17 '16 at 17:49
@Fred-ii- Good catch! – John Conde Aug 17 '16 at 18:01
@JohnConde :-) I've my best glasses on today. – Funk Forty Niner Aug 17 '16 at 18:06

Ronni Skansing · Answer 2 · 2013-08-05T18:51:57.150

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().

edited Aug 05 '13 at 18:51

answered Aug 05 '13 at 18:45

Ronni Skansing

1,494
1
17
33

in this particular case it won't be very slow but actually the same. But in general you are right – Your Common Sense Aug 05 '13 at 19:06

mysqli_num_rows returns 1 no matter what

2 Answers2

Linked