MySQL category script help

listenmirndt

So I am working on a category management script, and I am trying to figure out how to select subcategories of each category and display those subcategories under their main category.

No problem with that, but, how can I also display the subcategories of the subcategories, and the subsubb categories of the subsub categories ( as deep down as the category system goes )

The top level categories use an empty CATEGORY field, and the subcategories use the ID from the parent category... hope somone can point me in the right direction.

Thanks
-Arron

$SQL = "SELECT * from TABLE WHERE CATEGORY = '$nothing' ORDER BY TITLE ASC"; $result = @mysql_query( $SQL );
while( $row = @mysql_fetch_array( $result ) ) {

echo "<b>$row[TITLE]</b><br>";

    $SQL = "SELECT * from TABLE WHERE CATEGORY = '$row[ID]' ORDER BY TITLE ASC"; $result = @mysql_query( $SQL );
    while( $row = @mysql_fetch_array( $result ) ) {
    echo "<b>$row[TITLE]</b><br>";
    }

}

lucasrd

it's called recursion....a function that calls itself...if you use recursion, I would recommend implementing some caching though, since deep recursion can take up lots of resources.

let me know if you want some code...I have several examples...

listenmirndt

An example would be just what I need.
Thanks!

lucasrd

this example builds a select list recursively...

   function rootLocationSelect($root,$id,$attributes,$default){
      global $mysql;
      echo "<select name='$id' id='$id' $attributes>";
      echo "<option value=''>Select a Location</option>";
      $sql = "select * from locations where location_id='$root'";
      $mysql->query("location",$sql);
      while($mysql->fetchRow("location")){
         $l = new Location();
         $l->setLocation($mysql->_current_row['location']);
         $s = "";
         if($default == $l->_location_id) $s = " selected";
         echo "<option value='".$l->_location_id."'$s>".$l->_location_name."</option>";
         $this->loadLocationChildren($l->_location_id,1,$default);
      }

  echo "</select>";
   }

   function loadLocationChildren($parent,$level,$default){
      $sql = "select * from locations where location_parent_id='$parent' order by location_name asc";
      $result = mysql_query($sql);
      $spacer = "";

  for($x=0; $x<$level; $x++){
     $spacer .= "....";
  }

  while($row = mysql_fetch_array($result)){
     $l = new Location();
     $l->setLocation($row);
     $s = "";
     if($l->_location_id == $default) $s = " selected";
     echo "<option value='".$l->_location_id."'$s>".$spacer . $l->_location_name."</option>";
     $this->loadLocationChildren($l->_location_id, $level +1,$default);
  }
   }

listenmirndt

Thanks lucasrd 🙂