Josh Z Josh Z - 1 year ago 43
MySQL Question

PHP Count Arrays from MySQL counts Incorrectly

I am having trouble correctly counting elements within the array that I pulled from my database. Please see my code below:

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn)
die("Connection failed: " . mysqli_connect_error());
echo "Connected successfully!";

$query = $conn -> query("Select distinct Race from Races");
while($race[] = $query->fetch_object());

//Check how many elements are within our query
$racecount = count($race);
echo "<br>" . $racecount . "<br><br>";
$racecount = count($race,COUNT_RECURSIVE);
echo "<br>" . $racecount . "<br><br>";


echo "<br><br><br>";

Using both type count() in both ways yields the same result of "4". However, please see the result from my var_dump.

array(4) { [0]=> object(stdClass)#3 (1) { ["Race"]=> string(5) "Human" } [1]=> object(stdClass)#4 (1) { ["Race"]=> string(7) "Vampire" } [2]=> object(stdClass)#5 (1) { ["Race"]=> string(5) "Demon" } [3]=> NULL }

Var_dump shows that it is an array with 4 elements within it. So count was correct, it's just not giving me the number that I'm looking for.

Thus, I have three questions.

1) How do I count my elements correctly?

2) Could someone explain to me why this reads as 4 elements?

3) Is my array not multi-dimensional? (since both counts yield the same result of 4)

Best Regards and Thanks in advance,


Answer Source

The reason is actually pretty straight forward. When inserting data into your array using while($race[] = $query->fetch_object()) you always insert the final $query->fetch_object() which is gonna be equal to null. That is when you exit your while loop. Thus, your array's last item will always be null, just like in your own var_dump.

One way to fix this would simply to subtract 1 from your result. Another way would be to implement inserting like this:

$race = [];
while($row = $query->fetch_object()) {
    array_push($race , $row);

The best way to count however would be by simply executing an SQL COUNT command:

$query = $conn->query("SELECT COUNT(DISTINCT Race) FROM Races");