Multi tool use

How to debug php/MySQL COUNT(id) returning 1 instead of total entries value

I need the total number of entries in my database.

Question: Why does the line "print_r($rows)" return "Array ( [COUNT(id)] => 79 )" but then the line "$recordCount = Count($row); echo "
record count = " . $recordCount;" returns "record count = 1".

I've tried a massive amount of different versions of this code, but I cant type them all here or it would take forever to read. Here is the current version of the code, which is giving me the unexpected results I mention above:

// create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// check connection
if ($conn->connect_error) {
die("connection failed: " . $conn->connect_error);
}


$sql = "SELECT * FROM games ORDER BY id DESC LIMIT $startRow, $rowsPerPage";
$result = $conn->query($sql);

$sql = "SELECT COUNT(id) FROM games";
$results = $conn->query($sql);
$row = $results->fetch_assoc();
print_r($row);
$recordCount = Count($row);
echo "<br/> record count = " . $recordCount;



$totalPages = ceil($recordCount / $rowsPerPage);
$pagination = "<div class='pagination'>";

for ($i = 0; $i <= $totalPages; $i++) {
$pagination .= "<a href='index.php?page=" . $i . "'>" . $i . "</a>";
echo 'wtf!';
}

$pagination .= "</div>";
echo ' <br/> pagination = ' . $pagination;


THANKS TO YOU HEROES I NOW HAVE IT CORRECT: Here incase someone else in 2018 is reading online and finding lots of incorrect implementations of it:

$sql = "SELECT COUNT(id) as countid FROM games";
$results = $conn->query($sql);
$row = $results->fetch_assoc();

$recordCount = $row["countid"]; // the countid gives me the total i expected :DDDD
echo "<br/> record count = " . $recordCount;


SHOW CREATE TABLE games

Count($row)

echo $row["COUNT(id)"];

$row

[COUNT(id)] => 79

SELECT COUNT(id) total_games FROM games

echo $row['total_games'];

COUNT(id) as id_count

$row['id_count']

$row["COUNT(id)"];

2 Answers
2

row is an associative array with an entry for every column in the result set. Since there's only one column there, count($row) returns 1. Instead, you should just access the only column there:

row

count($row)

1

$row = $results->fetch_assoc();
$recordCount = $row["COUNT(id)"];


COUNT(id)

COUNT(id)

COUNT(id)

Yes, you could either aliase the column so that you can then use it as a key in associative array or since you already know that this statement:

1.$results = $conn->query($sql); looks something like this:
+-----------+
| count(id) |
+-----------+
| 13 |
+-----------+
1 row in set (0.01 sec)

2.$row = $results->fetch_assoc();returns an array with 1 element


So, you can just write $recordCount = $row[0]; to fetch that count.

The above answer is correct as well, but wanted to let you know about this direct, alternate approach in case you forget to aliase for whatever reason!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.