MySQL COUNT does not return correct value [duplicate]

Question

This is my PHP code snippet:

$conn = mysqli_connect($servername, $dbusername, $dbupassword, $dbname);
$sql = "SELECT COUNT(*) FROM `users`;";
$count = $conn->query($sql);
echo $count->num_rows;

It always echos 1 even though I have 5 users in my table (see image below).

I also tried using COUNT(1) but it also returned 1. But when I change the statement to SELECT * FROM `users`; it returns 5.

If I do a var_dump($count) (same code as above but I don't use the num_rows), I get the information below:

object(mysqli_result)#2 (5) {
  ["current_field"]=> int(0)
  ["field_count"]=> int(1)
  ["lengths"]=> NULL
  ["num_rows"]=> int(1)
  ["type"]=> int(0)
}

I am running phpMyAdmin, Version information: 5.0.2, with database version: 10.4.14-MariaDB, and PHP 7.4.10

nope, it does return the correct value, it's 1 because when you execute the query count() you'll get one row. what you have to do is fetch the row, to get to the yielded value of count() which is 5 — Kevin
– Kevin, Commented Dec 22, 2020 at 6:44
I see.. I thought COUNT lets you count the total number of records in a table... — astigmatik
– astigmatik, Commented Dec 22, 2020 at 6:46
yes it does that, but the you need to fetch the record set to get to the value that count() showed you. just testing it in phpmyadmin should show 1 row. COUNT(*) then under that 5. that's where you need fetch to — Kevin
– Kevin, Commented Dec 22, 2020 at 6:52

bassxzero · Accepted Answer · 2020-12-22 06:45:53Z

2

$conn = mysqli_connect($servername, $dbusername, $dbupassword, $dbname);
$sql = "SELECT COUNT(*) FROM `users`;";
$result = $conn->query($sql);
$row = $result->fetch_row();
echo "row count = " . $row['0'];
var_dump($row);

answered Dec 22, 2020 at 6:45

bassxzero

5,01924 silver badges36 bronze badges

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

Comments

Reavy Willdan · Accepted Answer · 2020-12-22 06:50:25Z

0

if you want to count the row of table, just run query "SELECT COUNT() FROM users". Its return one row that contain total table row. Dont use num_rows method, because "SELECT COUNT() FROM users" query, just return one row

answered Dec 22, 2020 at 6:50

Reavy Willdan

162 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT does not return correct value [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related