Getting wrong number when counting rows in PHP

Question

I have a table called quote in my database with 5 rows. However when I try to count the rows it always returns 1 instead of 5. I am using the code below:

$connection = mysqli_connect("host","username","password","database"); 

$querya = "SELECT COUNT(id) FROM quote";
$resulta = mysqli_query($connection, $querya);
$max = mysqli_num_rows($resulta);
$srow = rand(1,$max);

<br /> There are <?php echo $max ?> number of rows

I am counting the id column which is a primary key and therefore never null. I have also tried it by using count(*) but get the same result. Where am I going wrong?

It is because you're doing a count (1 row) instead of counting rows. If you want to use `mysqli_num_rows()` remove the `COUNT()` from your query. — Jay Blanchard, Jun 20 '17 at 21:42
You need to get in the habit of [accepting answers](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) which help you to solve your issues. You'll earn points and others will be encouraged to help you. — Jay Blanchard, Jun 20 '17 at 21:50
Thank you for your answer and the pointer re acceppting answers. I appreciate both! — Maz, Jun 20 '17 at 22:20

score 6 · Accepted Answer · answered Jun 20 '17 at 21:42

6

You can't pair mysqli_num_rows with count. If you want to use mysqli_num_rows you'd have to select * (which would be slow). Instead select count(*) as total, and use total.

answered Jun 20 '17 at 21:42

Scott C Wilson

19,102
10
61
83

Of course! I totally missed that. I just amended by query to SELECT * FROM quote and it works perfectly! Thank you so much! – Maz Jun 20 '17 at 21:45

score 0 · Answer 2 · answered Jun 20 '17 at 21:46

If you want to use COUNT() in your query give it an alias that you can return:

$querya = "SELECT COUNT(`id`) AS `Total` FROM `quote`";
$resulta = mysqli_query($connection, $querya);
$row = mysqli_fetch_assoc($resulta);
echo $row['Total']; // identifier here is the same as the alias in the query

Getting wrong number when counting rows in PHP

2 Answers2