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 separate

Auburn(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已经在第一个循环中获取了结果。要做的简单的事情是做这样的事情：&nbsp; $query = "&nbsp; &nbsp; SELECT&nbsp;&nbsp; &nbsp; &nbsp; stateAbv,&nbsp; &nbsp; &nbsp; stateName,&nbsp; &nbsp; &nbsp; '' AS cityName,&nbsp; &nbsp; &nbsp; count(*) AS state_count&nbsp;&nbsp; &nbsp; from&nbsp; &nbsp; &nbsp; my_data&nbsp;&nbsp; &nbsp; GROUP BY stateAbv, stateName&nbsp;&nbsp; &nbsp; union&nbsp; &nbsp; SELECT&nbsp;&nbsp; &nbsp; &nbsp; stateAbv,&nbsp; &nbsp; &nbsp; stateName,&nbsp; &nbsp; &nbsp; city AS cityName,&nbsp; &nbsp; &nbsp; COUNT(*) AS city_count&nbsp;&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; my_data&nbsp;&nbsp; &nbsp; GROUP BY stateAbv, stateName,&nbsp; &nbsp; &nbsp; city&nbsp;&nbsp; &nbsp; ORDER BY stateAbv,&nbsp; &nbsp; &nbsp; cityName asc&nbsp; ";&nbsp; $sqlResult = mysqli_query($con, $query);&nbsp;$i = 0;&nbsp;while ($row = mysqli_fetch_array($sqlResult)) {&nbsp; &nbsp; if ($row['cityName']) {&nbsp; &nbsp; &nbsp; &nbsp; echo $row['cityName'] . "(" . $row['state_count'] . ")" . "<br/>";&nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; echo $row['stateName'] . "(" . $row['state_count'] . ")" . "<br/>";&nbsp; &nbsp; }&nbsp; &nbsp; if ($i == 6){&nbsp; &nbsp; &nbsp;echo "<a class='more-detail' style='cursor: pointer; font-color:#0000ff;'>More...</a>";&nbsp; &nbsp; &nbsp;echo "<div class='detail-section' style='display: none;'>";&nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; $i++;&nbsp;}&nbsp;echo "<a class='less-detail' style='cursor: pointer; font-color:#0000ff;'>Less...</a>";&nbsp;echo "</div>";?>

Answer 2

$query = "&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stateAbv&nbsp; &nbsp; &nbsp; &nbsp; , stateName&nbsp; &nbsp; &nbsp; &nbsp; , city AS cityName&nbsp; &nbsp; &nbsp; &nbsp; , COUNT(city) As city_count&nbsp; &nbsp; &nbsp; &nbsp; , COUNT(stateName) As state_count&nbsp; &nbsp; FROM&nbsp; &nbsp; &nbsp; &nbsp; my_data&nbsp; &nbsp; GROUP BY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stateName&nbsp; &nbsp; &nbsp; &nbsp; , city&nbsp; &nbsp; ORDER BY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stateName&nbsp; &nbsp; &nbsp; &nbsp; , city&nbsp; &nbsp; &nbsp;&nbsp;";$sqlResult = mysqli_query($con, $query);while ($row = mysqli_fetch_array($sqlResult)) {&nbsp; &nbsp; $state_name = "{$row['stateName']} ({$row['state_count']})"&nbsp; &nbsp; $city_name = "{$row['cityName']} ({$row['city_count']})"&nbsp; &nbsp; $cities[$state_name][{$row['cityName']}] = $city_name;}foreach ( $cities As $state ) {&nbsp; &nbsp; echo "<p>State: $state </p>";&nbsp; &nbsp; foreach ( $state As $city ) {&nbsp; &nbsp; &nbsp; &nbsp; echo "$city<br />";&nbsp; &nbsp; }&nbsp; &nbsp; echo '<br />';}?>所有这些都没有经过测试，我没有任何样本数据来测试它，并且无法点击显示和隐藏细节。它的作用是根据查询结果创建一个多维数组。当点击状态时，您会使用 javascript 来扩展该状态的细节（认为该技术被称为手风琴）