Using PHP to create unordered lists based on MySQL results

Question

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?

deceze · Accepted Answer · 2011-10-24 03:50:34Z

2

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.

edited Oct 24, 2011 at 3:50

answered Oct 24, 2011 at 3:44

deceze♦

525k89 gold badges806 silver badges954 bronze badges

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

1 Comment

bozdoz Over a year ago

Great answer. I've been in this situation before too. I just used multiple while loops. This should be way more efficient.

Nick Q. · Accepted Answer · 2011-10-24 03:42:05Z

1

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

answered Oct 24, 2011 at 3:42

Nick Q.

3,9862 gold badges26 silver badges39 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

bozdoz · Accepted Answer · 2011-10-24 03:51:11Z

0

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.

answered Oct 24, 2011 at 3:51

bozdoz

12.9k8 gold badges74 silver badges100 bronze badges

Collectives™ on Stack Overflow

Using PHP to create unordered lists based on MySQL results

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related