PHP/MYSQL populate dropdown sorted by query results

Question

What I'd like is to create a new unordered list for each new value in a mysql result column.

My query looks like this and i'm throwing it into a data array:

$connection = dbconnect();

$getusers = "SELECT users.usr_id, users.usr_firstname, users.usr_lastname, user_groups.group_name FROM users INNER JOIN user_groups ON users.usr_group = user_groups.group_id ORDER BY group_name ASC, users.usr_firstname ASC, users.usr_lastname ASC";
$result = mysql_query($getusers);

$data_array = array();
while($data = mysql_fetch_array($result)){
    $data_array[] = $data;
}

Now I need to display that data so that each new user_group is an unordered list, and each row with that same group, comes up as a list item of that unordered list.

it's very similar to this question ( PHP/MySQL Query Results ), but i'm having trouble getting the closing ul's in the right place. Here's my code for outputting, though I know it's wrong because the li's aren't really children of the ul's.

$previousgroup = "";
foreach($data_array as $users){
    if($users['group_name'] != $previousgroup){
        echo "<ul class=\"group\">" . $users['group_name'] . "</ul>";
    }
    else{
        echo "<li id=\"m" . $users['usr_id'] . "\"><h4>" . $users['usr_firstname'] . " " . $users['usr_lastname'] . ", " . $users['cred_name'] . "</h4></li>";
    }
}

Is there a more efficient way of doing this? Thanks for your help.

Just to clarify: you want to get a output similar to the one of the question you linked? because it seems to be that you are using almost the same query (inner join is implicit in that one, while you explicitly use it), except for that "DISTINC" — Erenor Paz
– Erenor Paz, Commented Jun 18, 2012 at 16:32
each user belongs to a group, which i get in the query, but i want to display those groups as headers of a list. I'd just like to sort the users by group. I'm using jquery to hide the results until they click on a group, which would then drop down displaying all the users that have that group as their "group_name" — Edward Glasser
– Edward Glasser, Commented Jun 18, 2012 at 16:36

Erenor Paz · Accepted Answer · 2012-06-18 16:42:02Z

1

You could change the while loop this way:

$data_array = array();
while($data = mysql_fetch_array($result)){
    $data_array[$data['group_name']][] = $data;
}

and you would get an array that contains your group_name's as index of other arrays which contain your data. To create the list, you could do the following:

<ul>
  <?php
  foreach ($data_array as $temp_key => $temp_array)
  {
    ?>
    <li>
      <?php echo $temp_key; ?>
      <ul>
        <?php
        foreach ($temp_array as $datum)
        {
          echo ("<li>" . $datum['usr_firstname'] . "</li>";
        }
        ?>
      </ul>
    </li>
    <?php
  }
  ?>
</ul>

Hope it works for you

answered Jun 18, 2012 at 16:42

Erenor Paz

3,1915 gold badges40 silver badges48 bronze badges

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

2 Comments

Edward Glasser Over a year ago

This is exactly what I was hoping to see. Thanks Erenor.

Erenor Paz Over a year ago

I'm glad i could be of help :)

Joe Green · Accepted Answer · 2012-06-18 16:32:24Z

1

if($users['group_name'] != $previousgroup){
    echo "</ul><ul class=\"group\"><li>" . $users['group_name'] . "</li>";
}
echo "<li id=\"m" . $users['usr_id'] . "\"><h4>" . $users['usr_firstname'] . " " . $users['usr_lastname'] . ", " . $users['cred_name'] . "</h4></li>";

The code in your loop should look like this, and you need to open and close a <ul> from outside the loop.

echo '<ul>';
foreach () { // here is the loop }
echo '</ul>';

THis is doing it the "dirty" way with printing out as you go, at least.

The way I would do it would be to build a 2D array indexed first by group_name and then by user ID. It would mean making two passes at the data as a second loop would be needed to display the data, but generally speaking you're not going to feel the difference.

answered Jun 18, 2012 at 16:32

Joe Green

1,7871 gold badge13 silver badges17 bronze badges

2 Comments

Edward Glasser Over a year ago

hmm. yeah I see what you are doing here, but i agree with it being dirty. I'll probably look at this from passing over the data twice. I'd actually kind of like to get away from the way i'm currently doing it to a more 'readable' or programatically acceptable style.

Edward Glasser Over a year ago

I'm going to try to do this the cleaner style. thanks for the effort, Joe.

Collectives™ on Stack Overflow

PHP/MYSQL populate dropdown sorted by query results

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related