Thinkphp unlimited classification and Transformation Speed is extremely slow

I know that I am writing this very slowly. I know the train of thought, but I cannot do it. I should extract all the data at a time and then sort it. {code...} I know that this writing is very slow.
I know the idea, but I cannot do it.
All data should be retrieved at one time
Sort and ask for advice

        function getBookmarkcategory($pid = 0, &$result = array(), $spac = 0)        {            if(MODULE_NAME=="Home"){                $ispublic=0;                $uid=session('uid');                $ispublic+="And bookmarkcategory_staffid = $uid";            }else if(MODULE_NAME=="Admin"){                $ispublic=1;            }            $Model = new Model();            $spac = $spac + 8;            $res=$Model->query( "SELECT * FROM x_bookmarkcategory WHERE bookmarkcategory_pid=$pid And bookmarkcategory_ispublic = $ispublic order by bookmarkcategory_sort");            foreach($res as $key=>$value){                $result[]=$res[$key];                getBookmarkcategory($value['bookmarkcategory_id'],$result,$spac);            }            return $result;        }        $list = getBookmarkcategory();        return $list;

Reply content:

I know that writing is slow.
I know the idea, but I cannot do it.
All data should be retrieved at one time
Sort and ask for advice

        function getBookmarkcategory($pid = 0, &$result = array(), $spac = 0)        {            if(MODULE_NAME=="Home"){                $ispublic=0;                $uid=session('uid');                $ispublic+="And bookmarkcategory_staffid = $uid";            }else if(MODULE_NAME=="Admin"){                $ispublic=1;            }            $Model = new Model();            $spac = $spac + 8;            $res=$Model->query( "SELECT * FROM x_bookmarkcategory WHERE bookmarkcategory_pid=$pid And bookmarkcategory_ispublic = $ispublic order by bookmarkcategory_sort");            foreach($res as $key=>$value){                $result[]=$res[$key];                getBookmarkcategory($value['bookmarkcategory_id'],$result,$spac);            }            return $result;        }        $list = getBookmarkcategory();        return $list;

Without talking about the requirement and table structure, who knows what the code is?

For an unlimited php classification algorithm, refer to the PHPCMS category table structure. It stores all the parent (tree) and child (tree) nodes in the database recursively. You can remember to store all the data to the array at a time and then recursion.

You can refer to the answer to the following question: "category" is similar to the Department in permission management.
Http://segmentfault.com/q/1010000004365843/a-1020000004365952

            $res=$Model->query( "SELECT * FROM x_bookmarkcategory WHERE bookmarkcategory_pid=$pid And bookmarkcategory_ispublic = $ispublic order by bookmarkcategory_sort");

It should be slow here. You can find a way not to put it in recursion for query. First, you can query it at once, and then recursion is N times faster than this.

Function getBookmarkcategory ($ pid = 0, & $ result = array (), $ spac = 0) {if (MODULE_NAME = "Home") {$ ispublic = 0; $ uid = session ('uid'); $ ispublic + = "And bookmarkcategory_staffid = $ uid";} else if (MODULE_NAME = "Admin") {$ ispublic = 1 ;} $ Model = new Model (); // It turns out to be one of the reasons why the instance Model is slow for recursive search every time. put it outside the function $ spac = $ spac + 8; $ res = $ Model-> query ("SELECT * FROM x_bookmarkcategory WHERE bookmarkcategory_pid = $ pid And bookmarkcategory_ispublic = $ ispublic order by bookmarkcategory_sort "); // SELECT * This write method has low performance bookmarkcategory_pid = $ pid -- bookmarkcategory_pid determines whether to index foreach ($ res as $ key => $ value) {$ result [] = $ res [$ key]; getBookmarkcategory ($ value ['bookmarkcategory _ id'], $ result, $ spac);} return $ result ;} // The best speed is that the data is retrieved from the outside at a time, and then recursive. The mysql operation is very slow.

One query finds all the data, then cyclically adjusts the data structure using the passed value reference, and then comes out.

Left and right value trees can be used
Http://wenku.baidu.com/view/cb2bb3eeaeaad1f346933ffd.html

High Efficiency

You can use a data dictionary to extract all the data at a time and recycle it once.

Because you are constantly reading data from external storage. If the data volume is small, you can read all the required data at a time and then construct a tree in the memory. The associated tables can be saved when data is generated. If the data volume is very large, you can consider storing associated data in a tree hierarchy and reading the data several times.
PS: Check your code. Be careful with SQL injection.