how to change a while sql query loop into an array loop

Question

hey guys

i record number of queries of my website and in page the below script runs , 40 extra queries added to page .

how can I change this sql connection into a propper and light one

    function tree_set($index)
    {
        //global $menu; Remove this.
        $q=mysql_query("select id,name,parent from cats where parent='$index'");
        if(mysql_num_rows($q) === 0)
        {
            return;
        }

        // User $tree instead of the $menu global as this way there shouldn't be any data duplication
        $tree = $index > 0 ? '<ul>' : ''; // If we are on index 0 then we don't need the enclosing ul
        while($arr=mysql_fetch_assoc($q))
        {
            $subFileCount=mysql_query("select id,name,parent from cats where parent='{$arr['id']}'");
            if(mysql_num_rows($subFileCount) > 0)
            {
                $class = 'folder';
            }
            else
            {
                $class = 'file';
            }

            $tree .= '<li>';
            $tree .= '<span class="'.$class.'">'.$arr['name'].'</span>';
            $tree .=tree_set("".$arr['id']."");
            $tree .= '</li>'."\n";
        }
        $tree .= $index > 0 ? '</ul>' : ''; // If we are on index 0 then we don't need the enclosing ul

        return $tree;
    }

//variable $menu must be defined before the function call
$menu = '....<ul id="browser" class="filetree">'."\n";
$menu .= tree_set(0);
$menu .= '</ul>';

echo $menu;

i heard , this can be done by changing it into an array , but i don't know how to do so

thanks in advance

Answer 1

It can be done by copying your data out into an array, and then using that copy: i.e.

while($arr=mysql_fetch_assoc($q))
    {
        $results[] = $arr;
    }

later on, you then do whatever op you want on $results

The main problem with your code is you are mixing your display logic all in with your SQL query.

Answer 2

Select whole tree in single query, like "select id,name,parent from cats". Iterate on result, build array in PHP that will represent your tree, then draw HTML using array as source

Answer 3

Try this (untested code):

function tree_set($index)
{
    //global $menu; Remove this.
    $q=mysql_query("select id,name,parent from cats where parent='$index'");
    if(mysql_num_rows($q) === 0)
        return;

    $cats = array();
    $cat_ids = array();

    while($arr=mysql_fetch_assoc($q))
    {
       $id = intval($arr['id']);
       $cats[$id] = $arr;
    }

    $subFilesCountQuery="select parent,count(*) as subFileCount from cats where parent=".
                   join(" OR parent=",array_keys($cats))." GROUP BY parent";

    $subFileCountResult=mysql_query($subFilesCountQuery);

    while($arr=mysql_fetch_assoc($subFileCountResult))
    {
       $id = intval($arr['parent']);
       $cats[$id]['subFileCount'] = $arr['subFileCount'];
    }

    // If we are on index 0 then we don't need the enclosing ul
    $tree = $index > 0 ? '<ul>' : ''; 
    foreach($cats as $id => $cat)
    {
        if($cat['subFileCount'] > 0)
            $class = 'folder';
        else
            $class = 'file';

        $tree .= '<li>';
        $tree .= '<span class="'.$class.'">'.$arr['name'].'</span>';
        $tree .=tree_set("".$arr['id']."");
        $tree .= '</li>'."\n";
    }
    $tree .= $index > 0 ? '</ul>' : '';

What I'm doing is two queries: One to fetch all the categories (your original first query) followed by a second query to fetch all the subcategory counts in one fell swoop. I am also storing all categories in an array which you can loop through, rather than displaying as you fetch from the database.