PHP MySqli GROUP BY [英] PHP MySqli GROUP BY

问题描述

我有一个数据库来存放我的客户，我需要将它们显示在按州和城市分组和排序的页面上.

I have a database where I house my clients, I need to display them on a page grouped and ordered by State and City.

如果我使用下面的代码，则得到想要的结果，但每个城市的州名均重复. 如果我将ORDER BY更改为GROUP BY，那么它仅显示1个结果，则该州有多个城市，我显然希望将它们全部列在相关州下.

If I use the code below I get the result I want but the State duplicates for each city. If I change the ORDER BY to GROUP BY then it only displays 1 result, there are several cities in the State and I obviously want them all listed under the relevant State.

欢迎任何帮助.

"SELECT state, city, company_name  FROM mytable WHERE status='T' AND city <>'Head Office' AND company_name <>'my company' ORDER BY state ASC"

进一步的代码，如下所示:

Further code as requested below:

$coverage="";
$sql = "SELECT state, city, company_name  FROM mutable WHERE status='T' AND city <>'Head Office' AND company_name <>'my company' GROUP BY state, city ORDER BY state ASC, city ASC";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
$currentCity = NULL;
while($row = mysqli_fetch_assoc($result)) { 
    $id = $row["id"]; 
    $state =$row["state"];
    $office = $row["city"]; 
    $agent = $row["company_name"]; 
    if($row["city"] !== $currentCity) {
        $currentCity = $row["city"];
        $coverage .= '
        <div id="stateCoverage" class="fluid ">
        <p><strong class="white">' . $state . '</strong></p>
        </div>
            <div id="officeCoverage" class="fluid ">
                <h3>' . $office . '</h3>
            </div> 
        ';
    }
    $coverage .= '
        <div id="imageCoverage" class="fluid ">
                <img src="http://foo/bar/logos/' . $agent . '.jpg" alt="' . $agent . '">
            </div>
    ';

}
}else{
$coverage="<p>There seems to have been a problem locating your content, please try again, if the problem persists we would be grateful if you reported it to us, Thank you.</p>";
}

推荐答案

您可能正在寻找的查询属于此类查询:

The query you are probably looking for it's something of this sorts:

SELECT 
    state, city, company_name
FROM
    mytable
WHERE
    status = 'T' AND city <> 'Head Office'
    AND company_name <> 'my company'
group by state, city, company_name
ORDER BY state ASC, city ASC, company_name ASC

GROUP BY 运算符将所有与参数匹配的行折叠为单行.为了避免将所有记录折叠成一行，您只需要根据需要在该运算符中添加更多字段即可.

The GROUP BY operator collapses all the rows into a single row, as they match the parameters. To avoid collapsing all the records into a single row you just have to add more fields into that operator as you need.

这篇关于PHP MySqli GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！