php print more results from array and group them

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?

You need multiple queries and nested for loop. First query to get unique cities (using DISTINCT clause). Second query to get list of all names in that city. As you iterate through the result of 2nd query, you create the div. — Maximus2012
– Maximus2012, Commented Apr 6, 2015 at 19:21
you could do a group_concat query - SELECT GROUP_CONCAT(name) as name, city FROM table_name GROUP BY city dev.mysql.com/doc/refman/5.0/en/… — Sean
– Sean, Commented Apr 6, 2015 at 19:24
With Sean suggestion I got only cities, and with Maximus2012 I couldn't manage anyhow. Now I google it to figure out those function. — Vladimir Glisovic
– Vladimir Glisovic, Commented Apr 6, 2015 at 19:31

Alex · Accepted Answer · 2015-04-06 19:44:47Z

2

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>';
}

edited Apr 6, 2015 at 19:44

answered Apr 6, 2015 at 19:39

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vladimir Glisovic Over a year ago

That's it. Thanks a lot. Topic can be closed :)

Collectives™ on Stack Overflow

php print more results from array and group them

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related