Listing issue, GROUP mysql
- by SethCodes
Here is a mock-up example of Mysql table:
| ID | Country | City |
________________________________
| 1 | Sweden | Stockholm |
| 2 | Sweden | Stockholm |
| 3 | Sweden | Lund |
| 4 | Sweden | Lund |
| 5 | Germany | Berlin |
| 6 | Germany | Berlin |
| 7 | Germany | Hamburg |
| 8 | Germany | Hamburg |
Notice how both rows Country and city have repeated values inside them.
Using GROUP BY country, city in my PDO query, the values will not repeat while in loop. Here is PDO for this:
$query = "SELECT id, city, country FROM table GROUP BY country, city";
$stmt = $db->query($query);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
The above code will result in an output like this (some editing in-between). GROUP BY works but the country repeats:
Sweden - Stockholm
Sweden - Lund
Germany - Berlin
Germany - Hamburg
Using bootstrap collapse and above code, I separate the country from the city with a simple drop down collopase. Here is code:
<li>
<a data-toggle="collapse" data-target="#<?= $row['id']; ?>"
href="search.php?country=<?= $row['country']; ?>">
<?= $row['country']; ?>
</a>
<div id ="<?= $row['id']; ?>" class="collapse in"> //collapse div here
<a href="search.php?city=<?= $row['city']; ?>">
<?= $row['city']; ?><br></a>
</div> //end
</li>
It then looks something like this (once collapse is initiated):
Sweden
> Stockholm
Sweden
> Lund
Germany
>Berlin
Germany
>Hamburg
Here is where I face the problem. The above lists the values Sweden and Germany 2 times. I want Sweden and Germany to only list one time, and the cities listed below, so the desired look is to be this:
Sweden // Lists one time
> Stockholm
> Lund
Germany // Lists one time
>Berlin
>Hamburg
I have tried using DISTINCT, GROUP_CONTACT and other methods, yet none get my desired output (above). Suggestions? Below is my current full code in action:
<?
$query = "SELECT id, city, country FROM table GROUP BY country, city";
$stmt = $db->query($query);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
?>
<li>
<a data-toggle="collapse" data-target="#<?= $row['id']; ?>"
href="search.php?country=<?= $row['country']; ?>">
<?= $row['country']; ?>
</a>
<div id ="<?= $row['id']; ?>" class="collapse in"> //collapse div here
<a href="search.php?city=<?= $row['city']; ?>">
<?= $row['city']; ?><br></a>
</div> //end
</li>
<? endwhile ?>
