Grouping a list of names by country using PHP and MySQL

I have a MySQL database containing a person's forename, surname, their country, and whether they are happy for their name to be listed on a website.

The field names are 'firstname', 'lastname', 'country' and 'listedagree'. In the database, the 'listedagree' result is either Y or N.

I'd like to list only the people that have agreed to have their names listed, grouped by country, and ordered by their surname

For each country, the outputted markup should look like this:

开发者_如何学JAVA<h4>Country name</h4>
<ul>
    <li>Amy Adams</li>
    <li>Kenneth Branagh</li>
    <li>Ray Charles</li>
</ul>

My PHP skills are very limited - I'm really struggling to find the code I need to get this working. Can anybody help please?

Many thanks in advance!

---

SELECT * FROM users WHERE listedagree = 'Y' ORDER BY country, lastname, firstname

Then with your PHP loop through the records. Every time the country name changes close the preceeding ul and add a h4 and opening ul before echoing the users name. eg:

<?php
$country = null;
while($row = mysql_fetch_assoc($result)) {
    if($row['country'] != $country) {
        if(!is_null($country)) {
            echo '</ul>';
        }
        $country = $row['country'];
        echo "<h4>$country</h4>";
        echo '<ul>';
    }
    echo "<li>{$row['firstname']} {$row['lastname']}</li>";
}
echo '</ul>';

This code assumes that your country name data is clean and you don't have things like UK and United Kingdom or USA and United States of America in the database.

---

I wrote example code :)

<?php
 $result = mysql_query("SELECT country FROM tablename group by country") 
or die(mysql_error());  


while($row = mysql_fetch_array( $result )) {
        $country = $result['country'];
    $result2 = "SELECT country FROM tablename where country = '$country' and listedagree = 'y' order by surname";
            echo '<h4>'.$country.'</h4>';
            echo <ul>;
            while($row2 = mysql_fetch_array( $result2 )) {
            echo '<li>'.$row2["firstname"].' '.$row2["lastname"].'</li>';
        }
        echo '</ul>';
} 

?>

---

You need to do two thing, first is an SQL query to retrieve the values, you can do something like:

SELECT firstname,lastname,country FROM TABLE WHERE listedagree='Y' ORDER BY country

When you retrieve the data on PHP you need to iterate over it, keeping the actual country in a temp variable to be able to detect when it changes, for example you can do something like:

//Conect to the database
mysql_connect("hostaddress.com", "username", "password") or die(mysql_error()); 
//Selet the database to work with
mysql_select_db("Database_Name") or die(mysql_error());

$sql = "SELECT firstname,lastname,country FROM TABLE WHERE listedagree='Y' ORDER BY country";

//retrive the data
$result = mysql_query($sql) or die(mysql_error());

//Now iterate over the result and generate the HTML
$html = '';
$tmp_country = '';
while($data = mysql_fetch_array( $result )) 
{  
   //If the country have changed
   if($tmp_country != $data['country'])
   {
      //Check if we need to close the UL 
      if($tmp_country != '') {$html .= "</ul>"};
      //Print the country header
      $html .= "<h4>$data['country']</h4>";
      $htm l .= "<ul>";
      //store the last country
      $tmp_country = $data['country'];
   }
   //Add the names to each country
   $html .= "<li>$data['firstname'] $data['lastname']</li>";
} 
//Close the last UL
$html .= "</ul>";

I couldn't try the code so maybe it need some fine tuning to get it working but the it will do the job, if you have any problems let me know and I will help you.