MySQL/PHP/HTML How to insert children in hierarchical dropdown form menu?

Question

I have the following variables that fetch values from my database. These variables are storing Parent values of a hierarchical list (formats) and children values of the same hierarchical list:

$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
        "));

$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)
        "));

Although the above isn't necessarily important, the following is as my database looks like this:

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

So logically, my OBJECT ARRAYS contain:

$getParents->parent = 2 // contains only IDs of parent formats
$getSolos->term_id = 1,5 // contains only IDs of formats that have no children

The idea is to populate a dropdown form menu with these formats, with parent formats being optgroups containing their children. SO far I've achieved the following:

<form>
    Format: <select name="format">
        <option value="empty"></option>
            <?php 
                foreach ($getSolos as $solo) {
                    echo "<option value='".$solo->name."' ".$selected.">".$indent.$solo->name."</option>";
                }
                foreach ($getParents as $parent) {
                    echo "<optgroup label='".$parent->name."'>";
                    // How do I get the children in here?
                    echo "</optgroup>";
                }
            ?>
    </select>
</form>

The above outputs:

Entry Form
Twitter
Facebook // Bold as it is an optgroup header

The output should be:

Entry Form
Twitter
Facebook // Bold as it is an optgroup header
    Entry Form
    Page

So here is my question: 1) How do I get the children to appear inside the parent optgroup?

I have tried manually putting in the subquery and yielded no results either. — Sweepster, Jan 02 '13 at 23:51

Ilmari Karonen · Answer 1 · 2013-01-03T01:08:13.710

You could indeed construct a suitable NOT IN condition in PHP and pass it to MySQL, for example like this:

$parents_sql = implode(',', array_map(intval, $getParents->parent));

$getSolos = $wpdb->get_results($wpdb->prepare("
    SELECT term_id FROM wp_term_taxonomy            
    WHERE taxonomy = 'format'           
    AND parent = 0 AND term_id NOT IN ($parents_sql)
    "));

(Note: The intval is there to make sure this code cannot lead to SQL injection. In your case, that shouldn't be an issue, but one can never be too sure, and in any case it's a good habit to get into. If you were doing this with string keys, you could replace it with the appropriate string quoting function for your database.)

However, if there are a lot of parent records, it may be more efficient to use a subquery (demo on SQLFiddle):

SELECT term_id FROM wp_term_taxonomy AS t1            
WHERE taxonomy = 'format'           
AND parent = 0
AND term_id NOT IN (
    SELECT parent FROM wp_term_taxonomy         
    WHERE taxonomy = 'format'           
    AND parent > 0)

or a LEFT JOIN (demo on SQLFiddle):

SELECT t1.term_id
FROM wp_term_taxonomy AS t1            
  LEFT JOIN wp_term_taxonomy AS t2
    ON t1.term_id = t2.parent
WHERE t1.taxonomy = 'format'           
  AND t1.parent = 0
  AND t2.term_id IS NULL

MySQL/PHP/HTML How to insert children in hierarchical dropdown form menu?

1 Answers1