PHP - problem with nested mysql_fetch_array() driving me crazy

Question

The MySQL query:

SELECT title,alias,parent FROM '._prefix.'categories 
WHERE (language = \''._language.'\' || language = \'all\') 
&& status = \'published\'
ORDER BY rank ASC

The Result (from phpMyAdmin):

title    |  alias   |  parent
Home     | home     |    0
Todo     | todo     |    0
Multiuser| todo_mu  |    21
Modulsys | todo_mod |    21

The PHP:

while($c = mysql_fetch_array($category_result)) {
    if($c['parent'] == 0) {
        $output .= '<li><a href="'._rewrite_string.$c['alias'].'" title="'.$c['title'].'">'.$c['title'].'</a>';

        $output .= '<ul>';

        mysql_data_seek($category_result,0);

        while($d = mysql_fetch_array($category_result)) {

            $output .= '<li class="space_left"><a href="'._rewrite_string.$c['alias'].'/'.$d['alias'].'" title="'.$d['title'].'">'.$d['title'].'</a>';

            $output .= '</li>';

        }

        $output .= '</ul>';

        $output .= '</li>';
    }
}

This should generate a category list like that

cat 1

• subcat 1
• subcat 2

cat 2

cat 3

but it generates something like that

cat 1

• cat 1
• cat 2
• subcat 1
• subcat 2

using the mysql_fetch_array into another (nested) without using mysql_data_seek causes an abort once the nested mysql_fetch_array was called. it only outputs cat1 and nothing more.

please provide me the solution, thank you

Answer 1

$i = 0;
while($c = mysql_fetch_array($category_result)) {
    $parent = $c['parent'];
    $next_row = $i + 1;

    $not_last_row = (mysql_data_seek($category_result, $next_row)); //Move ahead to get next parent or returns false if on last row...
    $next_parent = (!$not_last_row) ? $c['parent'] : FALSE;
    mysql_data_seek($category_result, $i); //Move back to current row

    $open_list = ($parent != $prev_parent);
    $close_list = (($parent > $next_parent) || !$not_last_row); //close list if next parent is smaller number or if this is last row.

    $output .= ($open_list) ? "<ul>" : "";

    $output .= '<li>'.$c['title'].'</li>';

    $output .= ($close_list) ? '</ul>' : "";

    $prev_parent = $parent; //Set this so that on next loop, we can see if they match
    $i++;
}

It's a bit rushed, but I think this is what you have in mind. Basically, if the previous parent does not match the current parent, open a list. If the current parent is greater than the next parent, close the list.

You will probably need to add something to account for it being the last iteration.

Answer 2

You may want to try the quick and dirty solution like this:

1. prepare 2 result sets with the same SQL query so that you'll get $category_result and $category_result2.

2. Then use $category_result for the outer loop and $category_result2 for the inner loop.

Hope it helps.

Answer 3

You are getting the expected result. If you have the result set a,b,c,d, you are starting with a which is a parent category, so it rewinds to the start of the set, and iterates through a,b,c,d again as subcategories. Now you are at the end of the set so both loops will exit as there is no more data.

What you probably want to do is to read all the data into a PHP array first, then iterate over that and build some sort of a tree structure. You might also be able to build your tree structure directly in the mysql_fetch loop.

Depending on what you are trying to achieve, there are also better ways to store your data. It's worth reading up on how to store trees and hierarchical data in SQL. Nested sets are probably what you want.

One other thing: don't use mysql_fetch_array, use mysql_fetch_assoc instead. Otherwise you end up with numeric and associative keys and a row array which contains twice the amount of data it should.

Answer 4

Think of what your code is doing:

1. fetches a row of data into $c and spits out the cat1 header
2. Enters the inner loop and repeatedly fetches more data into $d, spitting out subcat1, subcat2, etc..
3. The loop reaches the end of the result set, and exits out into the parent $c loop
4. There's no more data to fetch, so the parent loop exits

Syntactically, if you were to strip away all the database operations, you're essentially doing this:

 for ($i = 0; $i < 10; $i++) {   // $c = mysql_fetch_array()
    for ($i = 1; $i < 10; $i++) {  // $d = mysql_fetch_array()
         echo $i;
    }
 }

By the time the inner loop has finished, $i is 10, which kills the inner loop, and then also kills the outer loop.