使用PHP根据MySQL结果创建无序列表

I have a MySQL query that returns a number of records. For example:

+-------+------------+-----------+
| group | first_name | last_name |
+-------+------------+-----------+
| Red   | John       | Doe       |
+-------+------------+-----------+
| Red   | Jane       | Doe       |
+-------+------------+-----------+
| Green | Bob        | Anybody   |
+-------+------------+-----------+
| Black | Betty      | Anybody   |
+-------+------------+-----------+

I also have defined several group names in a PHP array:

$importantGroups = array('Red', 'Blue', 'Green');

Using the query results, I'm trying to write a PHP script that will create an unordered HTML list for each group that's defined in the array.

• If a group doesn't appear in any query results, then that list doesn't get created.
• If a result has group value that doesn't appear in the array, it's placed in a ul at the end.

Using the query results above, the HTML output would be:

<ul id="Red">
    <li>John Doe</li>
    <li>Jane Doe</li>
</ul>

<ul id="Green">
    <li>Bob Anybody</li>
</ul>

<ul id="Other">
    <li>Betty Anybody</li>
</ul>

Any help on the best way to make this work?

This is a little janky, I think there may be some way to add efficiency but it certainly works.

<?php
$importantGroups = array('Red', 'Blue', 'Green');

$sql = "SELECT * FROM `table`";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
    if(array_search($row['group'],$importantGroups)){
        $groupName = $row['group'];
    }else{
        $groupName = "Other";
    }
    $groups[ $groupName ][] = $row;
}

$importantGroups[] = "Other";

foreach($importantGroups as $groupName){
    echo '<ul id="' . $groupName . '">';
    foreach($groups[$groupName] as $element){
        echo '<li>' . $element['first_name'] . ' ' . $element['last_name'] . '</li>';
    }
    echo '</ul>';
}
?>

This is a really basic script, and you should really look into finding out more about how PHP and mySQL work together instead of just asking for help. I'll post the basic flow to help you in the correct order of operations and the resources to help you along. To start I'd recommend reading this how-to on Tizag, which should help you fully understand how these languages work together.

Query SQL to get the data you need

//Connect to the database
//Query for the data
$sql = "SELECT group, first_name, last_name FROM ... WHERE ..."
$results = mysql_query($sql);

Parse the SQL results to create a multi-dimensional array of results

while($result = mysql_fetch_assoc($results){
    $array_of_groups[$result["group"]][] = $result;
}

Iterate through the results array to create the desired output

foreach($array_of_groups as $group_name => $group){
    //We are now looping groups
    echo("<ul id='$group_name'>");
    foreach($group as $item){
        $item_name = $item["first_name"] . " " . $item["last_name"];
        echo("<li>$item_name</li>");
    }
    echo("</ul>");
}

The above code is untested and really just a stub of what you need. You'll need a test above to figure out what groups are not needed. I'm really going to recommend reading up on that Tizag article and patching up that example code to make sure you understand how it works going forward.

First, do the ordering in SQL like this:

SELECT ... ORDER BY FIELD(`group`, 'Green', 'Blue', 'Red') DESC

The parameters for FIELD should be in reverse order of their importance, results not in this group will be sorted to the end.

During output, just break into a new group whenever you encounter one:

$group = null;

while ($row = /* get result */) {
    if (!in_array($row['group'], $importantGroups)) {
        $row['group'] = 'other';
    }
    if ($row['group'] != $group) {
        if ($group !== null) {
            echo '</ul>';
        }
        printf('<ul id="%s">', $row['group']);
        $group = $row['group'];
    }

    printf('<li>%s</li>', $row['name']);
}

if ($group !== null) {
    echo '</ul>';
}

This is the most efficient way for large result sets. If the sets aren't that large, it's a lot more readable to group them together in PHP and output the groups, as demonstrated in the other answers.

I think it might be better to have three while loops. SELECT * FROM Table WHERE 'Group'='Red', then SELECT * FROM Table WHERE 'Group'='Green', then SELECT * FROM Table WHERE 'Group' NOT IN ('Red','Green'). With each result, put them in UL tags.

For example:

<?php
echo '<ul>';
while($row = mysql_fetch_array($result_Red))
  {
  echo "<li>" . $row['first_name'] . " " .  $row['last_name'] . "</li>";
  }
echo "</ul>";

mysql_close($con);
?>

And then again for Green and Other. It might save more time than saving each value to an array, then running a foreach loop to call them. Maybe not. Try it and find out.