CODEWITHSUNDEEP
phpmysqlarraysmultidimensional-array
kelsheikh
kelsheikh

Reputation: 1338

Add MySQL count into PHP array values

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
    )
  )

Upvotes: 0

Views: 42

Answers (1)

Qirel
Qirel

Reputation: 26450

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

Upvotes: 1

Related Questions