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

Question

我一直在尝试列出带有城市的州组以及每个城市的位置数量。有点像下面。TexasAustin (5)Dallas (8)Houston (3)除了计算城市的数量并像上面一样显示它之外，我一切都在进行。$sql ="SELECT DISTINCT  city,  state,  stateAbv,COUNT(CASE WHEN open = 'Y' THEN 1 END) AS cntFROM $tbl_nameWHERE open = 'Y'GROUP BY  city,  state,  stateAbvORDER 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)){

    $state = $row['state'];

    $stateAbv = $row['stateAbv'];

    $city = $row['city'];

    $numStores = $values['cnt']; 

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

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

    }

    $rows[$row['state']][] = ['city' => $city, 'numStores' => $numStores];

}

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

    echo $state." (".array_sum(array_column($cities, 'numStores')).")\n";

}