How to pass a MySql Hierarchical data to a array in PHP?

Question

I have this function:

function displayTree($parente, $level) {
    $link = dbCLASS::dbConnect();
    $result = mysql_query("SELECT * FROM procediemntos_profissionais where  PROCEDIEMNTOS_PROFISSIONAIS_PARENTE_ID = $parente ");
    while ($row = mysql_fetch_array($result)) {

        echo str_repeat('&nbsp', $level) . $row['PROCEDIEMNTOS_PROFISSIONAIS_NOME'] .  "<br/>";

        self::displayTree($row['PROCEDIEMNTOS_PROFISSIONAIS_Pk'], $level + 1);
    }
}

How could I change this code if I want to return a Array whith all elements instead of print it?

Answer 1

I don't know exactly what your database class was doing, but you weren't using it so I am just using a dumbed down db class I normally use to fetch data. Also, your query method (mysql_) is outdated and deprecated.

<?php
    class DBEngine
        {
            protected   $con;
            // Create a default database element
            public  function __construct($host = '',$db = '',$user = '',$pass = '')
                {
                    try {
                            $this->con  =   new PDO("mysql:host=$host;dbname=$db",$user,$pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
                        }
                    catch (Exception $e) {
                          return 0;
                        }
                }

            // Simple fetch and return method
            public  function Fetch($_sql)
                {
                    $query  =   $this->con->prepare($_sql);
                    $query->execute();

                    if($query->rowCount() > 0) {
                            while($array = $query->fetch(PDO::FETCH_ASSOC)) {
                                    $rows[]   =   $array;
                                }
                        }

                    return (isset($rows) && $rows !== 0 && !empty($rows))? $rows: 0;
                }

            // Simple write to db method
            public  function Write($_sql)
                {
                    $query  =   $this->con->prepare($_sql);
                    $query->execute();
                }
        }

    class   MakeTree
        {
            public  $_TreeArray;

            public function display($parente = '', $level = '', $_makeArray = false)
                {
                    if(!empty($parente)) {
                            // Create database connection
                            $con    =   new DBEngine('localhost','mydatabase','dbusename','dbpassword');
                            // Fetch results from database
                            $result =   $con->Fetch("SELECT * FROM procediemntos_profissionais where  PROCEDIEMNTOS_PROFISSIONAIS_PARENTE_ID = '$parent'");
                            // If not empty
                            if($result !== 0) {
                                    foreach($result as $row) {
                                            // Write if $_makeArray is false
                                            if($_makeArray !== true)
                                                echo str_repeat('&nbsp', $level) . $row['PROCEDIEMNTOS_PROFISSIONAIS_NOME'] .  "<br/>";
                                            // Save to array
                                            else
                                                $this->_TreeArray[] =   str_repeat('&nbsp', $level) . $row['PROCEDIEMNTOS_PROFISSIONAIS_NOME'] .  "<br/>";

                                            if(isset($row['PROCEDIEMNTOS_PROFISSIONAIS_Pk']))
                                                $this->display($row['PROCEDIEMNTOS_PROFISSIONAIS_Pk'], $level + 1, $_makeArray);
                                        }

                                    // Return the array if isset
                                    if(isset($this->_TreeArray))
                                        return $this->_TreeArray;
                                }
                        }
                }
        }

    // Create instance
    $tree   =   new MakeTree();
    // Set the value as true to start array
    $array  =   $tree->display('value','level',true);
    print_r($array); ?>

Answer 2

It would be something like this:

class myClass
{
    public static function getTree($parente)
    {
        $out = array();
        $result = mysql_query("SELECT * FROM procediemntos_profissionais where  PROCEDIEMNTOS_PROFISSIONAIS_PARENTE_ID = $parente ");
        while ($row = mysql_fetch_array($result))
        {
            $out[$row['PROCEDIEMNTOS_PROFISSIONAIS_NOME']] = self::getTree($row['PROCEDIEMNTOS_PROFISSIONAIS_Pk']);
        }
        return $out;
    }
}

dbCLASS::dbConnect();
$tree = myClass::getTree(0); //or whatever the top level parent id is

This will get you an array where the keys are the PROCEDIEMNTOS_PROFISSIONAIS_NOME, and the values are nested arrays of children. At the bottom level you will get keys pointing to empty arrays. Let me know if you need the array structured differently and we can figure that out.