How to debug php/MySQL COUNT(id) returning 1 inste

Question

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;

Answer 1

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!

Answer 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 = $results->fetch_assoc();
$recordCount = $row["COUNT(id)"];