[RESOLVED] Help with loops

[swisstoni]

Hi there,

I'm building a form using the <select> tags to display a group of categories and sub categories like so:

Parent
--->Sub cat
--------> Sub sub cat
Parent 2
---->Another sub cat

I've written the following script, but it seems inefficient to keep writing it out multiple times for each sub category but I can't figure out how I'd write a loop to do this automatically... See what you think:

<?php

//Retrieve parent categories
$query_0 = "SELECT id,name,parent FROM categories WHERE parent =0 ORDER BY name ";
$result_0 = mysql_query($query_0) or die ("Couldn’t execute query.");
while ($row_0 = mysql_fetch_array($result_0,MYSQL_ASSOC))
{
	extract ($row_0);
	echo "<option value='$id'>$name</option>";

//Retrieve first sub categories
$query_1 = "SELECT id,name,parent FROM categories WHERE parent=$id ORDER BY name";
$result_1 = mysql_query($query_1) or die ("Couldn’t execute query.");
while ($row_1 = mysql_fetch_array($result_1,MYSQL_ASSOC))
{	
	extract ($row_1);
	echo "<option value='$id'>----&gt; $name</option>";

	//Retrieve second sub categories
	$query_2 = "SELECT id,name,parent FROM categories WHERE parent=$id ORDER BY name";
	$result_2 = mysql_query($query_2) or die ("Couldn’t execute query.");
	while ($row_2 = mysql_fetch_array($result_2,MYSQL_ASSOC))
	{	
		extract ($row_2);
		echo "<option value='$id'>--------&gt; $name</option>";

		//Retrieve third sub categories
		$query_3 = "SELECT id,name,parent FROM categories WHERE parent=$id ORDER BY name";
		$result_3 = mysql_query($query_3) or die ("Couldn’t execute query.");
		while ($row_3 = mysql_fetch_array($result_3,MYSQL_ASSOC))
		{	
			extract ($row_3);
			echo "<option value='$id'>------------&gt; $name</option>";
		}
	}
}
}	

?>

Thanks for any help you can offer, if you need more info please let me know.

[Drakla]

You see, Paul, creating a php script is very much like making love to a beatiful woman...

Anyway, this sort of thing works well with recursion. This code isn't tested, but should get you started.

echo '<select name="foo">';
fetch_options();
echo '</select>';


function fetch_options($parent = 0)
{
	static $depth = -1;

$q = "SELECT id,name,parent FROM categories WHERE parent=$parent ORDER BY name";
$res = mysql_query($q) or die ("Couldn’t execute query.");

$depth++;

	// create a visual prefix if this is a child category

if ($depth)
{
	$prefix = str_repeat('--', $depth) . '&gt;';
}
else
{
	$prefix = '';
}

	// start fetching children

while ($row = mysql_fetch_assoc($res))
{
	echo '<option value="'.$row['id'].'">'.$prefix.$row['name'].'</option>';
		// recursion time
	fetch_options($row['id']);
}

$depth--;
}

Read up on recursion in the manual, oh, and static variables in functions.

EDIT: I didn't change enough of the names when I wrote the above code, should all be in place now

[swisstoni]

Cheers for that, I'll have a mess around in a bit!

Thanks for the speedy reply, and yes indeed - writing PHP is much like making love to a beautiful woman!

Ben.

[swisstoni]

Only just noticed your edit!

But I've got it working now, final code with a few tiny mods is shown below (probably very similar to your edited version now) if it's of use to anyone else:

<?php

function display_categories($parent = 0) 
{ 
    static $depth = -1; 

$query = "SELECT id,name,parent FROM categories WHERE parent=$parent ORDER BY name"; 
$result = mysql_query($query) or die ("Couldn’t execute query."); 

$depth++; 

    // create a visual prefix if this is a child category 

if ($depth) 
{ 
    $prefix = str_repeat('----', $depth) . '&gt;'; 
} 
else 
{ 
    $prefix = ''; 
} 

    // start fetching children 

while ($row = mysql_fetch_array($result)) 
{ 
    echo '<option value="'.$row['id'].'">'.$prefix.$row['name'].'</option>'; 
        // recursion time 
    display_categories($row['id']); 
} 

$depth--; 
} 

echo '<select size="10" name="catid">'; 
display_categories(); 
echo '</select>';

?>

Thanks for your help Drakla!

One more question.... how do i do the quote style PHP code box like you have in your posts?

[bodzan]

[ php ] before and [/ php ] after...

Only without spaces...

[Drakla]

Use the

 tags.  The closing one needs a forward slash before PHP just like a html tag with square brackets.

I can't show you directly as they'll get interpreted.

Edit your previous posts in this thread and put them in as a test.

[swisstoni]

Cheers guys, thats much better!

No doubt I'll be back again in the future!

Cheers, Ben