CODEWITHSUNDEEP
phpmysqlpdo
SethCodes
SethCodes

Reputation: 283

group_concat issue, need unique

I am pulling data from MYSQL and using GROUP_CONCAT and AS. 

<ul class="retailers-list">
<?  
$query = "SELECT id, country, group_concat(DISTINCT city SEPARATOR '<br>') AS city, country FROM retailers GROUP BY country";
$stmt = $db->query($query);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
?>
<li>
<a href="search.php?country=<?= $row['country']; ?>"><?= $row['country']; ?></a>
<br>
<a href="search.php?city=<?= $row['city']; ?>">
<?= $row['city']; ?></a> //group_concat / AS
</li>   
<?  endwhile ?>     
</ul>

The problem is that the output is one link. It seems to group all of the city values into $row['city'];. How Can I get each city to have it's own unique link, I need to use group_concat and AS and also give each city it's own unique URL.

Related post: Listing issue, GROUP mysql

Upvotes: 1

Views: 163

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

In short you should go for the simplest query:

SELECT id, country, city FROM retailers GROUP BY country ORDER BY country

Note you didn't order the result by country and it seems it is a must based on your question.

Then you basically perform (what I think in English is called a) flow-control cut. That is, you will have results such as:

Country   | City
----------+-------------
Argentina | La Plata
Argentina | Buenos Aires
USA       | New York
USA       | Los Angeles
Brazil    | Sao Paulo

So you basically need 2 loops (one for each nesting level). One to distinguish the countries and one to output all the different cities for a country. In pseudo-php-code:

row = get_next_result
while (row) {           // while there are still elements to be fetched
  current_country = row[country]
  while (row && current_country == row[country]) {
    output current_country and row[city]
    row = get_next_result
  }
}

I leave the translation to PHP to you... but this is 99% of the work :P

Upvotes: 1

SethCodes
SethCodes

Reputation: 283

Here was my solution! Solved!

<ul class="retailers-list">
<?  
$query = "SELECT id, country, group_concat(DISTINCT city) AS city, country FROM retailers GROUP BY country";
$stmt = $db->query($query);
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
?>
<li>
<a data-pjax="content" data-toggle="collapse" data-target="#<?= $row['id']; ?>" href="retailers.php?country=<?= $row['country']; ?>">
<?= $row['country']; ?></a>


<div id ="<?= $row['id']; ?>" class="collapse in">
<?php 
  $cities = explode(',',$row['city']);
  foreach ($cities as $city) {
?>
    <a data-pjax="content" href="retailers.php?city=<?= $city ?>"><?= $city; ?></a><BR>
<?php
  }
?>
</div>
</li> 
<?  endwhile ?>

Upvotes: 1

Related Questions