Hierarchical dropdown menu from array?

Question

I have the following code which fetches fields from my database and places them into an html form's dropdown menu for Formats. In it's current form, I am fetching from my database 3 times, how the code works is explained in the comments of the code:

$getSolos = $wpdb->get_results($wpdb->prepare("
        SELECT * FROM wp_terms p 
        LEFT OUTER JOIN wp_term_taxonomy t ON p.term_id = t.term_id
        WHERE t.taxonomy = 'format'
        AND t.parent = 0
        AND t.term_id NOT IN (SELECT parent FROM wp_term_taxonomy WHERE taxonomy = 'format' AND parent > 0)
        ORDER BY t.parent
        "));  // This fetches all rows that do not have children or parents.

$getParents = $wpdb->get_results($wpdb->prepare("
        SELECT * FROM wp_terms p 
        LEFT OUTER JOIN wp_term_taxonomy t ON p.term_id = t.term_id
        WHERE t.taxonomy = 'format'
        AND t.parent = 0
        AND t.term_id IN (SELECT parent FROM wp_term_taxonomy WHERE taxonomy = 'format' AND parent > 0)
        "));    // This fetches all rows that have children

$getChildren = $wpdb->get_results($wpdb->prepare("
        SELECT * FROM wp_terms p 
        LEFT OUTER JOIN wp_term_taxonomy t ON p.term_id = t.term_id
        WHERE t.taxonomy = 'format'
        AND t.parent > 0
        ORDER BY t.parent
        AND p.name
        "));    //This fetches all rows that ARE children

<select name="format">  //start the dropdown
    <option value="empty"></option>  //default field is empty
        <?php 
            foreach ($getSolos as $solo) {  //start loop through solos for output
                echo "<option value='".$solo->name."'>".$solo->name."</option>"; // output solos as options in the dropdown
            }
            foreach ($getParents as $parent) { //start loop through parents for output
                echo "<optgroup label='".$parent->name."'>"; // Spit out parent as an optgroup
                foreach ($getChildren as $child) { //Start loop through children for output
                    if ($child->parent == $parent->term_id) { // if child's parent value matches the ID of the parent
                        echo "<option value='".$child->name."'>    ".$child->name."</option>"; //Spit out the child as an option
                    }
                }
                echo "</optgroup>"; //close the optgroup
            }
        ?>
    </select> // end the dropdown

The output is as follows:

Entry Form
Twitter
Facebook
 - Entry Form
 - Page

The combined table from the database looks like this:

term_id       name           slug                 taxonomy     parent
1             Entry Form     entry-form           format       0
2             Page           page                 format       3
3             Facebook       facebook             format       0
4             Entry Form     facebook-entry-form  format       3
5             Twitter        twitter              format       0

There is a problem with this method however.

1) It is inefficient to access the database 3 times.

2) It is ineffective if a child also has a child. While the children of children do all go into $getChildren, the code will only spit out 1st level children and ignore the rest.

For demonstration purposes, if I have 6th row:

term_id       name          slug                  taxonomy     parent
6             Single        single                format       2

Then the code would do this:

Entry Form
Twitter
Facebook
 - Entry Form
 - Page

Notice that Single is completely ignored, ALTHOUGH it is contained inside the $getChildren array.

So how can this code be made better?

Can we have an example of database source? Feels like this is can be done with some sql finesse... — Khez
– Khez, Commented Jan 8, 2013 at 23:34
You gave one table, but used more in your examples... shrugs — Khez
– Khez, Commented Jan 9, 2013 at 1:11

feskr · Accepted Answer · 2013-01-08 08:33:59Z

1

+50

Row 6 has a parent with the value 2. Though the row with term_id 2 has a parent with the value 3 and thus is not included in your parents object.

I would probably use a function to achieve this;

$getTerms = $wpdb->get_results($wpdb->prepare("
              SELECT * FROM wp_terms p 
              WHERE t.taxonomy = 'format'
              ORDER BY p.name ASC"));

$terms = array();              
foreach($getTerms as $key => $term){
  $terms[$term->parent][$term->term_id] = $term;
} 

function printTerms($terms, $parent = 0, $deep = 0){
  if(count($terms[$parent]) > 0){

    $indent = "";
    for($i = 0; $i < $deep; $i++){
      $indent .= "&nbsp;&nbsp;&nbsp;";
    }

    foreach($terms[$parent] as $key => $term){

      if(count($terms[$term->term_id]) > 0){

        if($deep == 0){
          echo "<optgroup label='".$term->name."'></optgroup>";
        } else {
          echo "<option value='".$term->name."'>".$indent.$term->name."</option>";
        }

        printTerms($terms, $term->term_id, ($deep+1));

      } else {

        echo "<option value='".$term->name."'>".$indent.$term->name."</option>";

      }

    }
  }
}

?>    

<select name="format">
   <option value="empty"></option>
   <?php printTerms($terms); ?>
</select>

edited Jan 8, 2013 at 8:33

answered Jan 6, 2013 at 19:13

feskr

8497 silver badges11 bronze badges

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

9 Comments

Sweepster Over a year ago

Hmm. It's a start but ideally the optgroup would only apply to a top level parent while sub-parents would be selectable.

feskr Over a year ago

In that case you could build in a check on parent = 0. When its not 0 you can simply output an option. ill update answer tomorrow, i'm on a phone ;)

feskr Over a year ago

I edited the printTerms() function. It now supports selectable sublevel-parents, optgroup doesnt support a selectable label so i used an indentation, to visualise the level hierarchy, depending on deepness.

feskr Over a year ago

Also worth mentioning; I used 1 query to retrieve the data we need. Let PHP do the work for you! :)

Sweepster Over a year ago

The code doesn't output any indentation at all. It prints out Facebook as an optgroup but it's children have no indents and neither do the children of those children. I think the reason why is because it echoes "<optgroup label='".$term->name."'></optgroup>"; which closes the optgroup before anything can be inserted into it.

|

ROY Finley · Accepted Answer · 2013-01-02 20:33:07Z

0

You would need to set up your output to display like this to create headers in the select:

    <select>
  <optgroup label="Swedish Cars">
    <option value="volvo">Volvo</option>
    <option value="saab">Saab</option>
  </optgroup>
  <optgroup label="German Cars">
    <option value="mercedes">Mercedes</option>
    <option value="audi">Audi</option>
  </optgroup>
</select>

This means you will need to somehow recognize what output should be a label and what should be an option.

answered Jan 2, 2013 at 20:33

ROY Finley

1,4161 gold badge9 silver badges18 bronze badges

2 Comments

Sweepster Over a year ago

I believe this to be possible but I am going to need help to make that happen. Please see my updated question with the new information.

ROY Finley Over a year ago

You are going to need to get out of the foreach loop and get this infomation in a while loop. then you will just need to do conditional checks. EX: if($format->parent) do this ELSE do this

Francois Bourgeois · Accepted Answer · 2013-01-07 09:38:17Z

0

Did not test it, but i would first create a hierachical array with all IDs:

$items = array();
$ids   = array();
foreach ($getFormats as $format) {
    $items[$format->term_id] = $format;
    if (isset($format->parent)) {
        $ids[$format->parent->term_id][] = $format->term_id;
    } else $ids[$format->term_id] = null;
}

Then it's easy to walk through this array and create the HTML code:

foreach ($ids as $id => $subIds) {
    if (!empty($subIds)) {
        echo '<optgroup label="' . $items[$id]->name . '">';
        foreach ($subIds as $subId) 
            echo '<option value="' . $items[$subId]->name . '">' . $items[$subId]->name . '</option>';
        echo '</optgroup>';
    } else {
        echo '<option value="' . $items[$id]->name . '">' . $items[$id]->name . '</option>';
    }
}

edited Jan 7, 2013 at 9:38

answered Jan 4, 2013 at 13:24

Francois Bourgeois

3,7185 gold badges34 silver badges41 bronze badges

1 Comment

Sweepster Over a year ago

Well, it creates the dropdown, but everything is treated the same. The optgroups don't seem to work.

Collectives™ on Stack Overflow

Hierarchical dropdown menu from array?

3 Answers 3

9 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related