MYSQL query to create nested lists.

[poleposters]

Hi,

 

I was wondering iff there was a way to structure a query to create a set of nested lists.

 

ie. I have a table with the columns NAME and CITY.

 

I want to structure a query to retrieve records to create a list grouped by location that displays every name within that location.

 

I tried the GROUP by clause, however that only returns one value for each location.

 

• SYDNEY
  • Name-one
    
  • name-two
    

  [*]MELBOURNE

  • name-three
    
  • name-four
    
  • name-five
    

[Hybride]

Can you show your code, please?

 

Are you using a loop to show all of your values?

[sasa]

try

<?php
$con = mysql_connect('localhost','root','');
mysql_select_db('test');
$sql = "SELECT `CITY`, GROUP_CONCAT(name SEPARATOR '</li><li>') AS list FROM `data_table` GROUP BY CITY";
$result = mysql_query($sql);
echo "<ul>\n";
while ($row = mysql_fetch_assoc($result)){
echo "<li>$row[CITY]</li>\n";
echo "<ul><li>$row[list]</li></ul>\n";
}
echo "</ul>";
?>