MySQL query with COUNT and ORDER BY and hidden results

55
May 19, 2019, at 12:50 PM

Im trying to get a query correct where I can display the data by State with each of the States cities with stores in it with the COUNT (Number of stores in each city). Any help is greatly appreciated.

California
Los Angeles (5)
San Diego (8)
San Francisco (3)
...

Right now I have below which does not give me the counts in each city.

$sql = "SELECT DISTINCT city, state FROM locations  WHERE open = 'Y' ORDER BY state ASC";
$result = mysqli_query($conn,$sql);
$num_columns = 1;
$rows = array();
while($row = mysqli_fetch_assoc($result)){
    if(!isset($rows[$row['state']])){
        $rows[$row['state']] = array();
    }
    $rows[$row['state']][] = $row['city'];
}
echo "<table>";
foreach($rows as $state => $cities){
    echo '<tr><th colspan="'. $num_columns .'">'. $state .'</th></tr>';
    $cityData = array_chunk ($cities, $num_columns); 
    sort($cityData);  // split array into chunk of $num_columns cities per array
    foreach($cityData as $row){
        echo "<tr>";
        for($i=0; $i<$num_columns; $i++){
            $city = isset($row[$i]) ? $row[$i] : "";
            echo "<td>$city</td>";
        }
        echo "</tr>";
    }
}
echo "</table>";
Answer 1

I can suggest the following GROUP BY query:

SELECT
    city,
    state,
    COUNT(CASE WHEN open = 'Y' THEN 1 END) AS cnt
FROM locations
GROUP BY
    city,
    state
ORDER BY
    state;
READ ALSO
Only allow users on page if IP address is approved

Only allow users on page if IP address is approved

How can I make an HTML (and CSS/PHP/JavaScript) document which only allows certain IP addresses on a page?

66
What is the proper Query and PHP to print an archive of the database based on Year, Month?

What is the proper Query and PHP to print an archive of the database based on Year, Month?

I've found plenty of examples that either don't show the query, or don't show the codeI'm looking to format the output as such:

39
control structures &amp; functions- filter function [on hold]

control structures & functions- filter function [on hold]

You are given a list of strings such as

61
Native Android Application for website without WebView

Native Android Application for website without WebView

I need to build a Native Android Application for a Website that I have successfully developedHow do I go about this without using the webView

46