将 MySQL 计数添加到 PHP 数组值中

Question

我一直在尝试列出带有城市的州组以及每个城市的位置数量。有点像下面。

Texas

Austin (5)

Dallas (8)

Houston (3)

除了计算城市的数量并像上面一样显示它之外，我一切都在进行。

$sql ="SELECT DISTINCT

  city,

  state,

  stateAbv,

COUNT(CASE WHEN open = 'Y' THEN 1 END) AS cnt

FROM $tbl_name

WHERE open = 'Y'

GROUP BY

  city,

  state,

  stateAbv

ORDER BY

  state;";

$result = mysqli_query($conn,$sql);

$num_columns = 1;

$rows = array();

 $k=0;

 while($row = mysqli_fetch_assoc($result)){

    $state = $row['state'];

    $stateAbv = $row['stateAbv'];

    $city = $row['city'];

    //Count of stores in every city

    $values = mysqli_fetch_assoc($result); 

    $numStores = $values['cnt']; 

    if(!isset($rows[$row['state']])){

        $rows[$row['state']] = array();

    }

    $rows[$row['state']][] = $city;

}

foreach($rows as $state => $cities){

    echo '<b>'. $state .'</b>';

    $cityChunks = array_chunk ($cities, $num_columns); 

    sort($cityChunks); 

    foreach($cityChunks as $row){

        for($i=0; $i<$num_columns; $i++){

            $city = isset($row[$i]) ? $row[$i] : "";

            if ($k<3){

                echo "$city($numStores)";

            }

            $k++;

        }  

    }

    $k=0;

}

$rows通过将城市放入其中，我的阵列现在看起来像这样，但是我无法获得城市并一起计数并正确显示它。

Array

 (

   [Alabama] => Array

    (

        [0] => Mobile

        [1] => Auburn

        [2] => Hoover

        [3] => Foley

    )

  )

Answer 1

你$numStores没有被传递到$rows数组。一旦你有了它，你就可以array_column()用来获取每个州的所有位置，然后array_sum()用来获取总和。while($row = mysqli_fetch_assoc($result)){&nbsp; &nbsp; $state = $row['state'];&nbsp; &nbsp; $stateAbv = $row['stateAbv'];&nbsp; &nbsp; $city = $row['city'];&nbsp; &nbsp; $numStores = $values['cnt'];&nbsp;&nbsp; &nbsp; if (!isset($rows[$row['state']])){&nbsp; &nbsp; &nbsp; &nbsp; $rows[$row['state']] = array();&nbsp; &nbsp; }&nbsp; &nbsp; $rows[$row['state']][] = ['city' => $city, 'numStores' => $numStores];}foreach($rows as $state => $cities){&nbsp; &nbsp; echo $state." (".array_sum(array_column($cities, 'numStores')).")\n";}