如何获取子/子创建新UL的查询

I am trying to make un-order list for parent child categories where if there is any child category than it will create another un-order list ( like indented text) so user can understand properly.

I have fetch sql but with foreach I don't understand how to set so where child category only will display under parent category by creating another un-order list under the parent category.

Here is my code

$query_cat =    "SELECT * FROM ^categories";
$query = qa_db_query_sub($query_cat);
$catsid = qa_db_read_all_assoc($query);

echo '<UL>';
foreach ($catsid as $catid){
    echo '<LI>'. $catid['title'].' '. $catid['categoryid'].'</LI>';
}
echo '</UL>';

So final result would be

• First Category
  • Sub Category1
• Second Category

EDIT:

After modified code with @vlcekmi3 answer https://stackoverflow.com/a/13451136/1053190 I am getting this result

Now how to exclude subcategory from parent list?

There's no really easy solution for this with your design. The most effective way would be to add column like order_in_list (and maybe depth_in_list).

They would be pre calculated in loop (pseudocode):

START TRANSACTION
UPDATE t1 SET order_in_list = 0 // Restart whole loop

$ids = array(0);

while $id = array_shift($ids){
   $record = SELECT * FROM t1 WHERE id = $id // Get id details, order_in_list is important
   $children = SELECT * FROM t1 WHERE parent_id = $id // get list of all childs
   // If it's root element, start indexing from 0
   $root_order = ($record ? $record->order_in_list : 1) 
   $child_no = count($children) // How many child will be adding

   // No children, nothing to do:
   if $child_no < 1{
      continue;
   }

   append_to_array($ids, $children) // Store ids to process

   // Shift all later records, we'll be creating gap in order_in_list 1,2,3,4,5
   // To 1,2,5,6,7 to insert items on places 3,4
   UPDATE t1 SET order_in_list = (order_in_list + $child_no)
      WHERE order_in_list > $record->order_in_list

   // Okay, set IDs for direct children
   foreach( $children as $child){
       UPDATE t1 SET order_in_list = $root_order, depth_in_list = $record->depth_in_list+1
          WHERE id = $child->id
       $root_order++;
   }
}
COMMIT

This way you'll get records like:

First category, 1, 1
Second category 3, 1
Sub category, 2, 2

Which you could display with simple loop:

$last_depth = 0;
foreach( (SELECT * FROM t1 ORDER by `order_in_list`) as $row){
    if( $last_detph > $row['depth_in_list'])){
       // Close level </ul>
    } else if($last_detph < $row['depth_in_list']){
       // Opening level <ul>
    } else {
       // The same depth
    }
    $last_depth = $row['depth_in_list'];
}

Without modifying database

It would be probably most effective to build two arrays containing root elements and all elements:

$root_elements = array();
$all_elements = array();

foreach( (SELECT * FROM t1) as $row){
    // Store details into all_elements, note that entry may have already be created when
    // processing child node
    if( isset( $all_elements[$row['id']])){
      // set details
    } else {
      $all_elements[$row['id']] = $row;
      $all_elements[$row['id']]['children'] = array(); // Array of child elements
    }

    if( $row['parent_id'] == NULL){
        $all_elements[] = $row['id'];  // Add row element
    } else {
        if( isset( $all_elements[ $row[ 'parent_id']])){
            $all_elements[ $row[ 'parent_id']]['children'][] = $row['id'];
        } else {
            // Create new record:
            $all_elements[ $row[ 'parent_id']] = array();
            $all_elements[ $row[ 'parent_id']]['children'] = array($row['id']);
        }
    }
}

And then write it as:

foreach( $root_elements as $element_id){
    write_recursive( $all_elements[ $element_id]);
}

// And display
function write_recursive( $element)
{
   echo '<ul>...';
   if( count( $element['children'])){
      foreach( $element['children'] as $child){
         write_recursive( $all_elements[ $child]);
      }
   }
   echo '</ul>';
}

You better create class for that (to replace using global variables), but you should have a solid way to do this. Anyway try avoid using this with large number of records (I wouldn't go past 2000-5000 menu entries), try to at least cache it.

Note: solutions are oriented towards minimal number of requests on database when displaying list.

you can use complicated query or something like this

foreach ($catsid as $catid) {
    ...
    $subquery_cat = "SELECT * FROM ^categories WHERE parentid='".$catid['categoryid']."'";
    $query = qa_db_query_sub($subquery_cat);
    $subcatsid = qa_db_read_all_assoc($query);
    // wrap into html
    ...
}