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

Question

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

Josh · Accepted Answer · 2010-05-20 21:23:05Z

2

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.

edited May 20, 2010 at 21:23

answered May 20, 2010 at 21:09

Josh

11.1k11 gold badges67 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mac Taylor Over a year ago

Thanks man , but after executing your code , appache stop working because of overflow of queries

Josh Over a year ago

@Mac: Sorry, I had a bug, please see the changes I made and let me know if that fixes the error.

Josh Over a year ago

@Mac: OH. Silly me, I just realized your algorithm is recursive. My answer is completely wrong... Please acknowledge that you saw this comment, because I want to delete this answer.

Mac Taylor Over a year ago

@josh thanks again for your try ,hope to find an answer for this very important question

Josh Over a year ago

@Mac: I'm trying to think of a way to do this in as few queries as possible. What I have would be an improvement but needs more work.

|

Zak · Accepted Answer · 2010-05-20 21:02:50Z

0

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.

answered May 20, 2010 at 21:02

Zak

25.3k11 gold badges42 silver badges68 bronze badges

1 Comment

Mac Taylor Over a year ago

i don't get it , would you mind updating your answer with a more complete code ?! after defining $result[] what should i do ?!

Piotr Pankowski · Accepted Answer · 2010-05-20 21:07:51Z

-1

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

answered May 20, 2010 at 21:07

Piotr Pankowski

2,4161 gold badge16 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related