Organizing in categories & subcategories

[delfa]

I have this structure in my sql tables:

id | categoryname | father

1 | Services |
2 | Webdesign | 1
3 | WebHosting | 1
4 | Portfolio |
5 | xxxxxxxxxx | 4
and so on.......

father means that a defined category is subcategory of another.

The question is: How do I display this:

<select name="select">
        <option value="services">Services:</option>
        <option value="webdesign">____Webdesign</option>
        <option value="webhosting">____Webhosting</option>
        <option value="port">Portfolio</option>
        <option value="xxx">____xxx</option>
      </select>

😕

[codeDV]

This is what I'd do:

<select name="select">
        <option value="1">Services:</option>
        <option value="2.1"____Webdesign</option>
        <option value="3.1">____Webhosting</option>
        <option value="4">Portfolio</option>
        <option value="5.4">____xxx</option>
</select>

The first number being the ID and the second number after the dot being the ID for which its a sub category.

[barand]

Select those items with no father in your first query.

Loop thru the selection and , as you add the item to the options, do a second query to select where 'father = current item' and add those as options.

This assumes only one level, of course. Another way is to use a recursive function.

hth

[delfa]

CodeDV I don´t understand what r u trying to say!?

😕

Oh barand!

does that mean I have to write a query for every category ?

that would kill my box because I have about 300 categories and 50 subcategories

can u give me an example of a recursive function. I never used such thing!

:rolleyes:

[codeDV]

I wa making an assumtion that the person filling in you form will choose a category and it be inserted in to another table. There for have the value fot the catgory as:

2.1 you would be able to use the to get the info from the table it is relating to. Assuming you have a table called users you can constuct a query that can get the category and sub category.

I will have have a look a bit later at making this query as I am not that confident in giving you an accurate answer right now and would rather play around with it first.

[barand]

A recursive function is one that calls itself (I'll come back to you with that solution) but it would generate just as many queries as my original suggestion.

If we use an array we can do it in two queries.

(I used my own existing table so substitute parent = father and description = category)

<?

$cnx = mysql_connect("localhost") or die("Not connected");
mysql_select_db("test",$cnx);
/* select those with no parent so we have all categories first 
and place in an array */
$sql =  "select id,description from tablea where parent is null";
$res =  mysql_query($sql);
while (list($id, $d) = mysql_fetch_row($res)) {
	$options[$id][0] = "<option value=\"$id\">$d</option>";
}
/* now get subcats and append them to the cats already in our array */
$sql =  "select parent,description,id from tablea where parent is not null";
$res =  mysql_query($sql);
while (list($pa, $d, $id) = mysql_fetch_row($res)) {
	$options[$pa][] = "<option value=\"$id\">___$d</option>";
}


/* build options */
echo "<select name=\"category\"><br>\n";
foreach ($options as $opts) {
	foreach($opts as $op) {
		echo $op;
	}
}
echo "</select><br>";
?>

hth

[delfa]

thanks man!

I worked just like I wanted!

can you tell why php is so easy???

hhe!

:p

[barand]

Delfa

Recursive soulution as promised. Would work if subcats have subsubcats and so on. Also useful for directory tree lists etc.

function addOptions($parent,$depth) {
    $sql =  $parent>0 ? "select id,description from tablea where parent = $parent"
				: "select id,description from tablea where parent is null" ;
    $res =  mysql_query($sql);
    $indent = str_repeat('__',$depth);  // indent subcats according to depth of call
    while (list($id, $desc) = mysql_fetch_row($res)) {
			echo "<option value=\"$id\">$indent$desc</option>";
			/* the recursive bit */
			addOptions($id, $depth+1); // add options for those whose parent is this id
    }

}

$cnx = mysql_connect("localhost") or die("Not connected");
mysql_select_db("test",$cnx);

/* build options */
echo "<select name=\"category\"><br>\n";
addOptions(0,0);  // call to recursive function
echo "</select><br>";