String together values of parent nodes from a multidimensional PHP array

Question

I have a table of workplaces and their parent ids. Each parent could have any number of levels.

id    workplace              parent
1     WCHN                   0
2     Acute Services         1
3     Paediatric Medicine    2
4     Surgical Services      2
5     Nursing and Midwifery  1
6     Casual Pool            5
7     Clinical Practice      5

I need to create a single select input that lists the workplaces with all their parent workplaces, a bit like this:

<select>
    <option>WCHN > Acute Services > Paediatric Medicine</option>
    <option>WCHN > Acute Services > Surgical Services</option>
    <option>WCHN > Nursing and Midwifery > Casual Pool</option>
    <option>WCHN > Nursing and Midwifery > Clinical Practice</option>
</select>

By modifying this solution I've been able to turn my flat list into a multidimensional array and output pairs of values, but no the full paths.

<?php
public function list_all_workplaces()
{
    $temp = $result = array();
    $list = array();

    // Get the data from the DB
    $table = mysql_query("SELECT * FROM workplaces");

    // Put it into one dimensional array with the row id as the index
    while ($row = mysql_fetch_assoc($table)) {
      $temp[$row['workplace_id']] = $row;
    }

    // Loop the 1D array and create the multi-dimensional array
    for ($i = 1; isset($temp[$i]); $i++)
    {
        if ($temp[$i]['parent'] > 0)
        {
            $tmpstring = ($temp[$i]['workplace']); // workplace title

            // This row has a parent
            if (isset($temp[$temp[$i]['parent']])) {

                $list[$i] = $temp[$temp[$i]['parent']]['workplace']." > ".$tmpstring; 
                //example output: Acute Services > Paediatric Medicine

                // The parent row exists, add this row to the 'children' key of the parent
                $temp[$temp[$i]['parent']]['children'][] =& $temp[$i];

            } else {
                // The parent row doesn't exist - handle that case here
                // For the purposes of this example, we'll treat it as a root node
                $result[] =& $temp[$i];
            }
        } else {
            // This row is a root node
            $result[] =& $temp[$i];
        }

    }
    // unset the 1D array
    unset($temp);

    //Here is the result
    print_r($result);
}

Example print_r() output:

[1] => WCHN > Acute Services
[2] => Acute Services > Paediatric Medicine

Where do I go from here to get all of the parent workplaces into the select options?

Answer 1

I don't understand how you even use your function if there is no return value.

If your array is built correctly and the way you want it and your asking about getting your results into an html select field then that's the easy part.

Let's get back to your function real quick . You don't have return $result listed in your function so it's not going to return anything when called.

You'll want to add that to the end of the function to start.

You'll then loop through the array to start your html processing.

$data = list_all_workplaces()
foreach( $data as $key => $value )
{
    $options = "<option value='{$key}'>{$value}</option>";
}
echo "<select>{$options}</select>";

Answer 2

I can't see how your code will produce the print_r output you say you're seeing, as you're not ever using the string you create. But something along these lines should get you on your way - it generates the strings you need.

Replace your for loop with the following:

foreach ($temp as $i => $details)
{
    $parentID = $details['parent'];
    $tmpstring = ($details['workplace']);
    if ($parentID > 0 && isset($temp[$parentID]))
    {
        $temp[$parentID]['children'][] =& $temp[$i];
        while ($parentID > 0 && isset($temp[$parentID]))
        {
            $tmpstring = $temp[$parentID]['workplace']." > ".$tmpstring;
            $parentID = $temp[$parentID]['parent'];
        }
    }
    $result[] = $tmpstring;
}

As @Syx said, you'll need to return something from your function too, probably $result, and then use that to generate your <select>.