Add MySQL count into PHP array values

Question

I've been trying to list groups of states with their cities and the number of locations in each city. Kinda like below.

Texas
Austin (5)
Dallas (8)
Houston (3)

I have everything going except getting the count with the city and displaying it like above.

$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;
}

My $rows array looks like this right now by getting the city in there but I am having trouble getting the city and count together and displaying it properly.

Array
 (
   [Alabama] => Array
    (
        [0] => Mobile
        [1] => Auburn
        [2] => Hoover
        [3] => Foley
    )
  )

Qirel · Accepted Answer · 2019-05-19 11:38:28Z

1

Your $numStores isn't being passed down to the $rows array. Once you have that, you can use array_column() to fetch all the locations in each state, then use array_sum() to get the 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";
}

edited May 19, 2019 at 11:38

answered May 19, 2019 at 11:33

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add MySQL count into PHP array values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related