loop inside a loop inside a loop.. indefinatly. help.

[listenmirndt]

I am having trouble getting my head around this, I have a table like this

CATEGORIES - ID - TITLE - CATEGORY

The top level categories are distinguished with an empty CATEGORY field.

I want to display each sub underneath its parent.. unlimited levels deep. IE

Category
- Category
-- Category
--- Category
---- Category

$category is an empty variable..

<?php
$SQL = "SELECT * from CATEGORIES WHERE CATEGORY = '$category' ORDER BY TITLE ASC"; $result = @mysql_query( $SQL );
while( $row = @mysql_fetch_array( $result ) ) {

echo "$row[TITLE]"; 
echo '<br>';

$SQLx = "SELECT * from CATEGORIES WHERE CATEGORY = '$row[ID]' ORDER BY TITLE ASC"; $resultx = @mysql_query( $SQLx );
while( $rowx = @mysql_fetch_array( $resultx ) ) {

echo "- $rowx[TITLE]";
echo '<br>';

}
}
?>

So, this is good one level deep, but the subcategories will have subcategories, which will have their own subcategories, and so on forever.

How can I accomplish this?

Thanks
-Arron

[Kudose]

Setup your table something like this (althought the following isnt SQL):

cat_id INT(99) NOT NULL
parent_id INT (99) NOT NULL
cat_name VARCHAR(250) NOT NULL

Once you have that setup, then you can assign top level categoryies to parent_id 0, then build off of that. Essentially building a system like you need.

If I'm not making myself clear, I apologize., it's late. I can try again. 🙂

[listenmirndt]

Thanks for the reply, this is how I am setup currently.

ID (Auto-incrementing Number)
TITLE (The categories name)
CATEGORY (the ID field)

So,

ID-TITLE-CATEGORY
1 - Books - (empty)
2 - Fiction - 1
3 - Non-Fiction - 1
4 - Fantasy - 1
5 - Subfantasy - 4
6- Subsubfatasy - 5

I still dont understand how to get everything out in the proper order....

Books
- Fiction
- Non-Fiction
- Fantasy
-- Subfantasy
--- Subsubfantasy

Thanks
-Arron

[pingu]

You have to build a recursive function. Which means you made function out of your query. When the query doe not return anything, the quit the function otherwise go through the result and call the very same function again. But be carefully, that you always have condition that will quit the function, e.g.:

function factorial($x) {
    if ($x <= 1)
        return (1);
    else
        return ($x * factorial($x - 1));
}

I hope this gives an impression. I think you will be able to adobt this to fit your needs.

Pingu

[listenmirndt]

Thanks...
could you explain a little more how to use this, i dont understand.

-Arron

[elementaluk]

I know the last post was 2 years ago but this took me a while to figure (I was thinking about it all wrong) so to help anyone who wants to create unlimited sub-categories:

Set up one table like:
cat_id | parent_id | name

Please the $rows value with your mySQL result.

<?php
function fetch_results($value=''){
	//Get first results	
			$array = array();
			$rows = "PUT YOUR MYSQL CODE HERE";

/*Now $rows should be a multi-array with your mySQL result in it. FOR is your friend, while is a tosser*/		for($i=0;count($rows)>$i;$i++){
					if(empty($rows[$i]['cat_id'])) continue;
					$array[$i] = $rows[$i];
		 			$result = fetch_results($rows[$i]['cat_id']);
		 			if(empty($result)) continue;
		 			$array[$i]['sub'] = $result;
				}
		return $array;
}
print("<pre>");
print_r(fetch_results(0));
?>

The above function will create a multi-array with your categories.
Sub categories are placed in ['sub']

Hopefully that'll help someone.

[madwormer2]

Lol, the thread was only 2 months old, not 2 years.

Thanks for the working code

[aprieto]

I did implement it as you can see here

<?php
function fetch_results($value=''){
//Get first results

$array = array();

        $link = mysql_connect('localhost', 'root', '');

        if (!$link) {

die('Not connected : ' . mysql_error());
}

        $db_selected = mysql_select_db('data_guide');

        if (!$db_selected) {

die ('Can\'t use foo : ' . mysql_error());
}

        $sql = "SELECT * from category WHERE parent_id = $value";            

        $result = mysql_query( $sql ) ;

        if (!$result) {

die('Invalid query: ' . mysql_error());
}
$i = 0;
//$rows = array();
while ($row = mysql_fetch_assoc($result)) {
$rows[$i]['category_id'] = $row['category_id'];
$rows[$i]['category_name'] = $row['category_name'];
$rows[$i]['parent_id'] = $row['parent_id'];
$i++;
}
//print_r($rows);

/Now $rows should be a multi-array with your mySQL result in it. FOR is your friend, while is a tosser/

for($i=0;count($rows)>$i;$i++){

                if(empty($rows[$i]['category_id'])) continue;

                $array[$i] = $rows[$i];
                 $result = fetch_results($rows[$i]['category_id']);
                 print $result;
                 if(empty($result)) continue;
                 $array[$i]['sub'] = $result;
            }
    return $array;

}
print("<pre>");
print_r(fetch_results(0));
?>

but I think it is making to many database queries, don't you?

[thorpe]

what.... all 1 of them?

[aprieto]

I mean .... It was not be faster make only one query to the database to build a full array and then iterate over the array itself, instead of making a query each time the iterator is called?