PHP While loop Break in Mysqli_fetch_array

Question

I have been trying to pull a list of states, cities and a count from a database and display them like below. Hitting the more button will expand the rest of the cities in each state. Unfortunately, all the states and their cities and counts have been in same display column and I can't separate the states from each other. I can't seem to separate the states with their specific cities into separate columns. Any help is greatly appreciated.

Alaska(46)
Anchorage(22)
Eagle River(2)
Elmendorf Afb(1)
Fairbanks(7)
Juneau(4)
More...

After hitting more link:

Alaska(46)
Anchorage(22)
Eagle River(2)
Elmendorf Afb(1)
Fairbanks(7)
Juneau(4)
Ketchikan(1)
Kodiak(1)
Alabama(54) <-- next state loaded in output, I want this separate
Auburn(1)
Bessemer(1)
Birmingham(52)
Less...

Here is my query:

  $query = "
    SELECT 
      stateAbv,
      stateName,
      '' AS cityName,
      count(*) AS state_count 
    from
      my_data 
    GROUP BY stateAbv, stateName 
    union
    SELECT 
      stateAbv,
      stateName,
      city AS cityName,
      COUNT(*) AS city_count 
    FROM
      my_data 
    GROUP BY stateAbv, stateName,
      city 
    ORDER BY stateAbv,
      cityName asc
  ";
  $sqlResult = mysqli_query($con, $query);


 $i = 0;


 while ($row = mysqli_fetch_array($sqlResult)) {
    if ($i++ == 6) break;
    if ($row['cityName']) {
        echo $row['cityName'] . "(" . $row['state_count'] . ")" . "<br/>";
    } else {
        echo $row['stateName'] . "(" . $row['state_count'] . ")" . "<br/>";
    }
 }
 echo "<a class='more-detail' style='cursor: pointer; font-color:#0000ff;'>More...</a>";

 echo "<div class='detail-section' style='display: none;'>";
  while ($row = mysqli_fetch_array($sqlResult)) {
    if ($i++ > 6) {
        if ($row['cityName']) {
            echo $row['cityName'] . "(" . $row['state_count'] . ")" . "<br/>";
        } else {
            echo $row['stateName'] . "(" . $row['state_count'] . ")" . "<br/>";
        }
    }

 }
 echo "<a class='less-detail' style='cursor: pointer; font-color:#0000ff;'>Less...</a>";
 echo "</div>";

?>

Here is my js to expand/collapse the list:

<script>
$(document).ready(function(){

  $(".more-detail").click(function(){
    $(".detail-section").css("display", "block");
    $(".more-detail").css("display", "none");
  });

  $(".less-detail").click(function(){
    $(".detail-section").css("display", "none");
    $(".more-detail").css("display", "block");
  });

});
</script>

Simon · Accepted Answer · 2019-05-24 19:36:31Z

The problem with this approach is that mysqli_fetch_array has already fetched the results in the first loop. The simple thing to do would be to do something like:

  $query = "
    SELECT 
      stateAbv,
      stateName,
      '' AS cityName,
      count(*) AS state_count 
    from
      my_data 
    GROUP BY stateAbv, stateName 
    union
    SELECT 
      stateAbv,
      stateName,
      city AS cityName,
      COUNT(*) AS city_count 
    FROM
      my_data 
    GROUP BY stateAbv, stateName,
      city 
    ORDER BY stateAbv,
      cityName asc
  ";
  $sqlResult = mysqli_query($con, $query);


 $i = 0;


 while ($row = mysqli_fetch_array($sqlResult)) {

    if ($row['cityName']) {
        echo $row['cityName'] . "(" . $row['state_count'] . ")" . "<br/>";
    } else {
        echo $row['stateName'] . "(" . $row['state_count'] . ")" . "<br/>";
    }

    if ($i == 6){
     echo "<a class='more-detail' style='cursor: pointer; font-color:#0000ff;'>More...</a>";

     echo "<div class='detail-section' style='display: none;'>";
     }
    $i++;
 }

 echo "<a class='less-detail' style='cursor: pointer; font-color:#0000ff;'>Less...</a>";
 echo "</div>";

?>

SpacePhoenix · Accepted Answer · 2019-05-25 06:41:19Z

$query = "
    SELECT
          stateAbv
        , stateName
        , city AS cityName
        , COUNT(city) As city_count
        , COUNT(stateName) As state_count
    FROM
        my_data
    GROUP BY
          stateName
        , city
    ORDER BY
          stateName
        , city      
";
$sqlResult = mysqli_query($con, $query);
while ($row = mysqli_fetch_array($sqlResult)) {
    $state_name = "{$row['stateName']} ({$row['state_count']})"
    $city_name = "{$row['cityName']} ({$row['city_count']})"
    $cities[$state_name][{$row['cityName']}] = $city_name;
}

foreach ( $cities As $state ) {
    echo "<p>State: $state </p>";
    foreach ( $state As $city ) {
        echo "$city<br />";
    }
    echo '<br />';
}

?>

None of that has been tested, I've not got any sample data to test it with and it's not clickable to show and hide the detail. What it does is create a multi-dimensional array from the query results. You'd use javascript to when a state is clicked on it would expand the detail for the state (think the technique is known as an accordion)

Collectives™ on Stack Overflow

PHP While loop Break in Mysqli_fetch_array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related