[SOLVED] Trouble with recursive function logic

[boo_lolly]

Alright guys, I'm having some trouble figuring out the logic for this function I'm writing. Basically, I want to print out a list of categories, and subcategories, from a database. Read on, I'll explain in further detail...

 

Here's the table structure

+--------------------------------------------------+
|                     categories                   |
+------+--------------+------------------+---------+
|  id  |   parent_id  |       name       |   rank  |
+------+--------------+------------------+---------+
|   1  |              |    painting      |    4    |
+------+--------------+------------------+---------+
|   2  |              |    sculpture     |    3    |
+------+--------------+------------------+---------+
|   3  |       1      |     abstract     |    1    |
+------+--------------+------------------+---------+
|   4  |       2      |   clay molding   |    3    |
+------+--------------+------------------+---------+
|   5  |              | crafts/jewelery  |    2    |
+------+--------------+------------------+---------+
|   6  |       1      |   oil painting   |    1    |
+------+--------------+------------------+---------+
|   7  |       2      |      plaster     |    7    |
+------+--------------+------------------+---------+
etc....

 

Essentially I want to be able to insert the function in the middle of a while loop that traverses a list of all the categories in the database table. Something like this:

<?php

function list_categories($id) {
    /* logic:
    print $id name
    if $id is found as parent_id in database
        then find them and print them
        list_categories() again
    */
}

$sql = '
    SELECT
        *
    FROM
        categories
    ORDER BY
        name, rank
            ASC
';
$query = mysql_query($sql) OR die(mysql_error());

while ($row = mysql_fetch_array($query)) {
    list_categories($row['id']);
}

?>

 

The reason I want it to be recursive is so I can have literally an unlimited amount of categories, sub-categories, sub-sub-categories, sub-sub-sub-categories, etc... And the code wouldn't have to manually dig into each level of categories. It would be able to simply take the parameter of the id, and then find if there are any other categories in the table with a parent id of the current 'id' being looped. Make sense? That is ideal, but I'm missing some logic here. I should be able to do this, but despite my best efforts, I cannot seem to get it figured out.

 

I'd also like to add some design features to the category listing. Such as HTML lists and embedded lists. The table example would print out something like this:

<ul>
   <li>
   crafts/jewelrey
   </li>
   <li>
   sculpture
       <ul>
           <li>
           clay molding
           </li>
           <li>
           plaster
           </li>
       </ul>
   </li>
   <li>
   painting
       <ul>
           <li>
           abstract
           </li>
           <li>
           oil painting
           </li>
       </ul>
   </li>
</ul>

 

Again, this example only shows a 2-tier category list, but the idea is to have the function do all the work, regardless of how many sub categories there are embedded within one another. Don't worry about the html form, or how to go about inserting a new category into the database or any of that. I've got that covered, and I'm very very good at php and html, I just for some reason cannot figure this one out. Can I get some help?

[Cosizzle]

Just throwing this out there as a suggestion but what about an if statement that checks your current parent_id then checks against the next one, if its greater create a new section?

 

Im not sure if that would work though, just throwing an idea get that ball rolling again

[boo_lolly]

Just throwing this out there as a suggestion but what about an if statement that checks your current parent_id then checks against the next one, if its greater create a new section?

 

Im not sure if that would work though, just throwing an idea get that ball rolling again

 

Yeah it will definitely be necessary to have an if statement, as I illustrated in my pseudo-code above. The thing is, I fear it will get to be too repetitive with mysql queries. So, we have the function inside a while loop, that is looping through the results of a query... The function takes one parameter, which is the current ID of the current row in the database table. This argument, however, is going to have to be used inside the function with another sql query in order to check if it has a parent id. So I'm not exactly sure how to work it. I'm trying to make sure it's not too repetitive with the sql queries.

 

[Cosizzle]

Hm ya... another idea to throw in there, could you use this as a self joining table... Is this one of those rare times you might use one? I can't say ive ever had to use one outside of school or exams... heh

 

Also on a side note, do you have any demo scripts made up for this? Wouldnt mind trying to muck around with it

[boo_lolly]

Yeah that's a good suggestion, or perhaps even a LEFT JOIN table with itself. I have never needed to do that either, but this might be a practical application. I also came across these scripts, but haven't had the chance to play with them yet.

 

http://abeautifulsite.net/notebook/21

http://www.hawkee.com/snippet/1800/

http://www.cybertechhelp.com/forums/showthread.php?t=149051

 

I also found out that what I'm trying to do is print out a Tree structured list. If you guys get a chance to mess with them, let me know what you find. I'll post my own results later today. This will be a win for all of us guys!

[sasa]

try

<?php
function show_cat($data, $id = 0, $pre = ''){
echo "$pre<ul>\n";
foreach ($data[$id] as $row){
	echo "$pre\t<li>\n\t$pre$row[name]\n";
	if (isset($data[$row['id']])) show_cat($data, $row['id'], $pre."\t");
	echo "$pre\t</li>\n";
}
echo "$pre</ul>\n";
}

mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('test');
$sql = 'SELECT * FROM categories ORDER BY rank';
$query = mysql_query($sql) OR die(mysql_error());

while ($row = mysql_fetch_assoc($query)) {
$data[$row['parent_id'] + 0][] = $row; 
}

show_cat($data);
?>

[boo_lolly]

sasa, you're a life-saver.

<?php    
    function show_cat($data, $id = 0) {
        print "<ul>\n";
        foreach ($data[$id] as $row) {
            print "\t<li>\n\t$row[name]\n";
            if (isset($data[$row['id']])) {
                show_cat($data, $row['id'], ."\t");
            }
            print "\t</li>\n";
        }
    print "</ul>\n";
    }

    $sql = '
        SELECT
            *
        FROM
            categories
        ORDER BY
            parent_id, rank
    ';
    $query = mysql_query($sql) OR die(mysql_error());

    while ($row = mysql_fetch_assoc($query)) {
        $data[$row['parent_id'] + 0][] = $row; 
    }

    show_cat($data);
?>

 

output html:

<ul>
<li>
Drawings
<ul>
	<li>
	Black and White
	</li>
	<li>
	Color
	<ul>
		<li>

		Crayon
		</li>
		<li>
		Map Pencils
		</li>
	</ul>
	</li>
</ul>
</li>
<li>

Paintings
<ul>
	<li>
	Abstract
	</li>
	<li>
	Acryllic
	</li>
	<li>
	Realist
	</li>

	<li>
	Water Color
	</li>
</ul>
</li>
</ul>

 

dude, seriously, it doesn't get any more perfect than this. This is exactly what I wanted. Exactly!