I have a database where I house my clients, I need to display them on a page grouped and ordered by State and City.
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.
Any help is welcome.
"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
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.