The name of this thread probably wasn't the best, but I couldn't think of a better way to phrase this.

My last SOPW was regarding database structures for my current project. I found a work around that I think I like better, but I've stumbled into a question.

I have a table set up as.. (mysql)

Categories
       id int auto_increment NOT NULL,
       catname VARCHAR(300) NOT NULL,
       catdesc VARCHAR(300) NOT NULL,
       path VARCHAR(300) NOT NULL,
       root VARCHAR(1) NOT NULL,
       primary key  (id)
[download]

Path is a text location of the specific category, so the user can create endless number of subcategories to any category/sub category they want. Ie: /root/colors/red banana , /root/colors/blue/circle/blah .

My question is, how do I run through the table and query each of these so it knows which is a main category and which categories are it's siblings? I sort of cheated and made a ROOT column which, when set to 1, means it's a root category (it has no parent). When ROOT == 0, it's a subcategory of whatever is in it's path.

What I need to do is make an HTML SELECT field show all the categories and subcategories and I don't know how to run through it to keep all the information together.

The end result I want would look like (each inside a SELECT option in HTML)

Console Games (root)
  * News Sites
Fishing (root)
  * Fishing Tips and Links (sub)
  * Local Fishing (sub)
     * Freshwater Fishing (sub sub)
Other (root)
Science for Kids  (root)
[download]

I need a way to find out which sub categories belong with which. Path will always start with a / and will never end with one. So the one at the end without a / is the final path.

I know this is a ramble and most probably doesn't make as much sense to you as it does me, but I'd appreciate any help.

This is the code I have so far. It sort of works at this point but it doesn't search or sort any sub categories, it puts everything in together so it doesn't look like there are sub categories.

print qq~
<form action="" method="post">
<table width="400" border="1" cellspacing="0" cellpadding="1">
  <tr>
    <td colspan="2"><div align="center">Suggest A Site </div></td>
  </tr>
  <tr>
    <td>Name</td>
    <td><input name="name" type="text" id="name" /></td>
  </tr>
  <tr>
    <td>Email</td>
    <td><input name="email" type="text" id="email" /></td>
  </tr>
  <tr>
    <td>URL</td>
    <td><input name="url" type="text" id="url" /></td>
  </tr>
  <tr>
    <td>Title</td>
    <td><input name="title" type="text" id="title" /></td>
  </tr>
  <tr>
    <td>Keywords</td>
    <td><textarea name="keywords" id="keywords"></textarea></td>
  </tr>
  <tr>
    <td>Description</td>
    <td><textarea name="description" id="description"></textarea></td>
  </tr>
  <tr>
    <td>Category</td>
    <td>
         <select name="category" size="3">
~;

  my $data = qq(SELECT id, catname FROM categories WHERE 1 ORDER BY ca
+tname ASC);
  my $sth = $dbh->prepare($data);
  $sth->execute() or die $dbh->errstr;

  my ($id, $catname);
  $sth->bind_columns(\$id, \$catname);
  
  while($sth->fetch)
  {
     print qq(<option value="$catname">$catname</option>);
  }

  print qq~ 
        </select>
    </td>
  </tr>
  <tr>
    <td colspan="2"><div align="center">
      <input type="submit" name="Submit" value="Submit" />
    </div></td>
  </tr>
</table>
</form>
~;
[download]

To make it harder, I need everything to be SORT BY catname ASC where the root categories are sorted first, then every subcat put in a-z order thereafter.