Echo number of records in row with COUNT()

Question

How to get and echo number of records in row?this is my code

$b = "SELECT COUNT(Users) FROM room";
$r= mysqli_query($connection,$b);
$c = mysqli_fetch_array($r);
echo $c['Users'];

I get this error while i have it in room table

Notice: Undefined index: ID in C:\xampp\htdocs\php\blog0\rooms.php on line 31

Add an alias to your SQL query: `SELECT COUNT(Users) AS user_cnt FROM room` then `echo $c['user_cnt'];` — David Faber, Mar 22 '18 at 19:24

score 1 · Accepted Answer · answered Mar 22 '18 at 19:24

1

Pretty simple answer (And one I made in the past) You forgot to name the count, it's becomes a system variable and very hard to remember

Change the select to

 SELECT COUNT(Users) AS Users FROM room

answered Mar 22 '18 at 19:24

Forbs

"And one I made in the past"? Couldn't you flag the question as a duplicate, then? (I get it can be kind of hard to find our old answers if you can't remember exactly when it was, though.) – Don't Panic Mar 22 '18 at 19:38
What I mean is this is some error I made constantly in the past, I didn't answer it in the past. – Forbs Mar 23 '18 at 18:11
Ah, I see. Yeah, I remember getting tripped up by that too. – Don't Panic Mar 23 '18 at 22:31

score 0 · Answer 2 · answered Mar 22 '18 at 19:35

0

Instead of letting MySQL do the counting, get the data and let php count() do the work.

Change:

$b = "SELECT COUNT(Users) FROM room";
$r= mysqli_query($connection,$b);
$c = mysqli_fetch_array($r);
echo $c['Users'];

To:

$b = "SELECT `Users` FROM room";
$r= mysqli_query($connection,$b);
$c = mysqli_fetch_array($r);
echo count($c['Users']);

removed COUNT(Users) and replaced it with Users and added count() to count($c['Users']);

answered Mar 22 '18 at 19:35

JamesBond

Wouldn't counting in MySQL be more efficient, though? Why would we want to change that? – Don't Panic Mar 22 '18 at 19:43
@Don'tPanic not that i know of, from what i can say is that its less stress on the server to call to the MySQL db and then count. My method you only calling the MySQL db and then letting php do a quick count with data that is already there. – JamesBond Mar 22 '18 at 19:45

2 Answers2