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 ?>
