基本PHP的MySQL阵列分组问题 [英] Basic PHP MySQL array grouping question

问题描述

快速的问题，我觉得有一个人谁拥有以上的PHP / MySQL的最基本的知识，作为东西我做的一个非常简单的解决方案。

Quick question, which I think has a very easy solution for someone who has anything above the most rudimentary knowledge of PHP/MySQL as I do.

我有个城市中的各种存储在与城市，州和一些其他变量的数据库状态的列表。现在他们被拉由城市名称排序的列表：

I have a list of cities in a variety of states stored in a database with city, state and some other variables. Right now they get pulled as a list sorted by city name:

• 安克雷奇


• 马里兰州巴尔的摩


• 芝加哥，伊利诺伊
  等等等等。

我希望能够通过状态组，然后再列出所有有状态值的城市。所以它会是这样的：

I want to be able to group by state first, then list all the cities that have that state value. So it'd look like:

AK

• 安克雷奇


• 朱诺

CA

• 洛杉矶


• 圣迭戈


• 旧金山


• 等等等等

我知道我需要做一些的foreach，并在网上搜索，但没有发现，我可以开始工作的例子。

I know I need to do some sort of foreach and have searched online, but haven't found an example that I can get to work.

这就是我要拉的基本列表：

Here's what I have to pull the basic list:

  $list = mysql_query("SELECT id, alphaname, state FROM regional ORDER BY alphaname",$db);

while ($thearray = mysql_fetch_array($list)) {
  echo "<li><a href='info.html?id=$thearray[id]'>$thearray[alphaname], $thearray[state]</a></li>";
  } 

我知道如何做到这将是运行的每个状态的查询这将是一个痛苦和唯一真正的方法完全愚蠢的......

The only real way I know how to do it would be to run a query for each state which would be a pain and totally stupid...

感谢您的帮助！

更新 - 解决。我rockacola的做法了，虽然我-G的工作也是如此。

Update - solved. I went with rockacola's approach though i-g's worked as well.

推荐答案

试试这个..

查询所有的城市与国家，为了由国家先再由城市：

Query all city with state, order by state first then by city:

SELECT id, alphaname, state 
FROM regional 
ORDER BY state ASC, alphaname ASC

整理数据集到2维数组：

Organise your dataset into 2 dimension array:

$states = array();
while($thearray = mysql_fetch_array($list)) 
{
    $states[$thearray[state]][$thearray[id]] = $thearray[alphaname];
} 

现在你的 $状态的内容应该是这个样子：

Now contents of your $states should look something like:

Array
(
    [AK] => Array (
        [id_1] = Anchorage
        [id_2] = Juneau
    )
    [CA] => Array (
        [id_3] = Los Angeles
        [id_4] = San Diego
        [id_5] = San Francisco
    )
)

生成的HTML presentation：

Generate your HTML presentation:

的注意：添加锚点，以反映提出的问题的

foreach($states as $state_name => $cities)
{
    echo '<h3>'.$state_name.'</h3>';
    echo '<ul>';
    foreach($cities as $id => $city_name)
    {
        echo '<li><a href="info.html?id='.$id.'">'.$city_name.'</a></li>';
    }
    echo '</ul>';
}

这篇关于基本PHP的MySQL阵列分组问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！