php从数组打印更多结果并将它们分组

Question

I have one problem which I couldn't resolve.

Let's say that I have a table in mysql like this:

 id |  name   |  city
 ---|---------|---------
 1  |  En     |  Madrid
 2  |  Nicole |  Paris
 3  |  Robin  |  London
 4  |  Wayne  |  Paris
 5  |  John   |  Madrid
 6  |  Frank  |  Madrid

I wanna list all name of one city in one div, I wanna do that for all cities.

Examples:

En, John, Frank
Nicole, Wayne
Robin

All those rows to be in div, one below to another. In this particular case result is 3 div. I have managed only write every name in particular div, 6 of them. I did that in this way:

$result = mysql_query("SELECT * FROM table_name");
$count=mysql_num_rows($result);    
$i=0;
for($i=0;$i<=$count-1;$i++)
  {            
    $row=mysql_fetch_array( $result );    
    echo "<div>$row[name]</div>";        
  }  

Can anyone helps me?

Answer 1

Solution 1

$result = mysql_query("SELECT * FROM table_name ORDER BY city");
$count=mysql_num_rows($result);

$i=0;
$city = '';
for($i=0;$i<=$count-1;$i++) { 

    $row=mysql_fetch_array( $result );
    if ($city == '' ) {
       echo "<div>";    
    } elseif ($city<>$row['city']) {
       echo "</div><div>";  
    }  
    $city = $row['city']
    echo $row['name'].' &nbsp';
} 

if ($city<>'') echo '</div>';

Solution 2

$result = mysql_query("SELECT GROUP_CONCAT(name) as name_list, city FROM table_name GROUP BY city");
$count=mysql_num_rows($result);
$i=0;
$city = '';
for($i=0;$i<=$count-1;$i++) { 

    $row=mysql_fetch_array( $result );
    echo '<div>'.$row['name_list'].'</div>';
}