PHP - MySQL查询中的计数元素返回元素数量的两倍

Question

I'm trying to get the number of columns on a SQL query result. Whenever I try to use count($row) the table should have only 4 elements but it shows 8, and the implode(",",$row) show that in fact there are 8 elements but the output of that is a bit odd:

Code fragment:

$query = $con->query($sql);

$rows = array();

error_log($sql);
if(!$query){error_log(mysqli_error($con));exit;}
while($row = $query->fetch_array()){
    $rows[]=$row;
}

if(count($rows)==0) { echo "101";   exit; }

echo '{"row":[';
foreach($rows as $key=>$row) {
    error_log(count($row));
    error_log(implode(",",$row));
    error_log($row[0]." ".$row[1]." ".$row[2]." ".$row[3]);
}
echo "]}";

Result:

[Tue May 29 13:47:21 2018] 8
[Tue May 29 13:47:21 2018] AFI,AFI,001,001,Pancho,Pancho,a1d7584daaca4738d499ad7082886b01117275d8,a1d7584daaca4738d499ad7082886b01117275d8
[Tue May 29 13:47:21 2018] AFI 001 Pancho a1d7584daaca4738d499ad7082886b01117275d8

As you can see, the eight elements are there but they are just duplicates of the correct output, represented by error_log($row[0]." ".$row[1]." ".$row[2]." ".$row[3]);.

Does anyone know why PHP does it like this? Is there a way to get the correct number of elements on the query row?

Answer 1

The default behaviour of fetch_array() is to both have indexes with column-names as with numerical positions. When you do a var_dump of your $row, you would see something similar to:

array(
    0 => 'AFI',
    'column_name_1' => 'AFI',
    1 => '001',
    'column_name_2' => '001',
    // and so on
)

fetch_array() takes one single argument to change this behaviour. The default is MYSQLI_BOTH giving above output. You can change this to $query->fetch_array(MYSQLI_NUM) to get only the numerical indexes or $query->fetch_array(MYSQL_ASSOC) to get only the values with column-names on the index.

When using either one of those, the count() will also give the correct number of columns.

Note: $query->fetch_row() is the same (and shorter) as $query->fetch_array(MYSQLI_NUM) and $query->fetch_assoc() the same as $query->fetch_array(MYSQLI_ASSOC). Instead of using fetch_array() with the array-notation-argument you might prefer one of those functions instead.