PHP While 循环在 Mysqli_fetch_array 中中断

Question

我一直在尝试从数据库中提取州、城市和计数的列表，并将它们显示如下。点击更多按钮将扩展每个州的其他城市。不幸的是，所有州及其城市和计数都在同一个显示列中，我无法将州彼此分开。我似乎无法将具有特定城市的州分成单独的列。任何帮助是极大的赞赏。Alaska(46)Anchorage(22)Eagle River(2)Elmendorf Afb(1)Fairbanks(7)Juneau(4)More...点击更多链接后：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 separateAuburn(1)Bessemer(1)Birmingham(52)Less...这是我用于展开/折叠列表的 js：<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>

Answer 1

这种方法的问题是mysqli_fetch_array已经在第一个循环中获取了结果。要做的简单的事情是做这样的事情：

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

?>

Answer 2

$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 />';

}

?>

所有这些都没有经过测试，我没有任何样本数据来测试它，并且无法点击显示和隐藏细节。它的作用是根据查询结果创建一个多维数组。当点击状态时，您会使用 javascript 来扩展该状态的细节（认为该技术被称为手风琴）