group_contact issue

[sethcodes]

Hey, I am using GROUP_CONCAT and AS to list some data, here is my code:

 

<?  
$query = "SELECT id, country, group_concat(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">
<a data-pjax="content" href="retailers.php?city=<?= $row['city']; ?>">
<?= $row['city']; ?></a><BR>
</div>
</li> 
<?  endwhile ?> 


</ul> 

 

This gets sort of what I am trying to achieve, but the issue I face now is all the results from $row['city']  does not output correctly. Instead it is just one big link and they are seperated by comma, when there is no comma in my code. 

Any ideas? My original post here http://stackoverflow.com/questions/19626632/listing-issue-group-mysql will give you a complete idea of what I want to achieve. 

[.josh]

well uh, you're getting a comma delimited list in one chunk because of the group_concat().. because that's what it does.

[.josh]

However.. working with what you have, it seems to me what you want to do is this (though tbh I read your SO post and i'm still not 100% clear on what you're trying to do. Perhaps a better explanation of what you are actually trying to accomplish would be helpful):

 

<?  
$query = "SELECT id, country, group_concat(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 ?> 


</ul> 

[sethcodes]

However.. working with what you have, it seems to me what you want to do is this (though tbh I read your SO post and i'm still not 100% clear on what you're trying to do. Perhaps a better explanation of what you are actually trying to accomplish would be helpful):

 

<?  
$query = "SELECT id, country, group_concat(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 ?> 


</ul> 

Yes, this is what I am looking to do, is this method above the best approach though? It works fine and thank you, but is their an alternative way to your above example?

[.josh]

Eh, TBH IDK. I'm not a sql expert. If you just needed 2 dimensions (e.g. country and cities) then I would leave out the group_concat() and then put the results in a 2d array and then have a nested loop (or just do a nested loop with a condition for the countries if you don't need to save the results for something later). But with that 3d dimension there (the id).. I guess what you've got now is probably a decent enough way to handle it. IOW I can't really think of a "better" way to handle it.. but again, I'm not a sql expert. You may wanna try posting in the sql forum here..there's a bunch of really frakking smart folks there that would be able to tell you if there's a better way!