mysqli_num_rows returns 1 no matter what [duplicate]

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
– cmorrissey, Commented Aug 5, 2013 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
– gen_Eric, Commented Aug 5, 2013 at 18:42

Funk Forty Niner · Accepted Answer · 2016-08-17 17:47:41Z

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, 2016 at 17:47

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Aug 5, 2013 at 18:40

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

4 Comments

jayjay Over a year ago

Thanks, went with your first suggestion

Funk Forty Niner Over a year ago

I fixed a slight typo there John, after Googling something for another question today. Let's see if they come your way ;-) Question being stackoverflow.com/q/39002738

John Conde Over a year ago

@Fred-ii- Good catch!

Funk Forty Niner Over a year ago

@JohnConde :-) I've my best glasses on today.

score 1 · Accepted Answer · 2013-08-05 18:51:57Z

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

in this particular case it won't be very slow but actually the same. But in general you are right

Collectives™ on Stack Overflow

mysqli_num_rows returns 1 no matter what [duplicate]

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related