如何将结果集转换为嵌套的无序列表并隐藏值为0的子列表项？

Question

I am trying to display the below table value in list and sub-list.

and here is my for loop to display

$sql ="SELECT *FROM objectives"; 
$result = $conn->query($sql);

$categories = array();

foreach ($result as $result) {
    $category = $result['content'];
    $categories[$category][] = $result['sub_content'];
}
?>
<ul>
<?php foreach ($categories as $category => $subcategories): ?>
  <li>
    <?php echo $category; ?>
    <ul>
<?php foreach ($subcategories as $subcategory):?>
      <li><?php echo $subcategory; ?></li>
<?php endforeach; ?>
    </ul>
  </li>
<?php endforeach; ?>
</ul>

The data is displayed in list and with sub list. I don't want to display the 0 value in the sub-list.

Everything is fine except the display of 0 in sub-list. Please advise.

Answer 1

Simply implementing echo ($subcategory != '0')? '<li>'.$test.'</li>' : ''; will result in needless markup in the dom. Specifically, you will have empty <ul></ul> tags as nested lists where only a single row containing $subcategory is 0. (Demonstration) These extra bits of markup may cause funky side-effects when css/styling is applied.

Further refinements are advisable as a matter of best practice:

• When querying the database, only SELECT the columns that you specifically require for your task.
• Add stability to your process by using an ORDER BY clause that will group the rows by content and possibly sort sub_content
• Never use more loops than necessary. This task can be (and therefore, theoretically, should be) performed in a single loop.

Recommended Code: (Demo)

$result = $conn->query("SELECT content, sub_content FROM objectives");
$category = null;
$output = '';
foreach ($result as $row) {
    if ($category !== $row['content']) {             // new parent
        if ($category !== null) {                    // not first iteration
            $output .= "<li>$category";              // print parent
            if ($sublist) {
                $output .= "<ul>$sublist</ul>";      // print all children
            }
            $output .= "</li>";
        }
        $category = $row['content'];                 // overwrite $category
        $sublist = '';                               // reset sublist
    }
    if ($row['sub_content'] !== '0'){                // filter row
        $sublist .= "<li>{$row['sub_content']}</li>";
    }
}
if ($result) {                                       // in case the resultset is empty
    echo "<ul>";
        echo $output;                                // print stored markup
        echo "<li>$category";                        // print last parent
        if ($sublist) {
            echo "<ul>$sublist</ul>";                // print all children from last parent
        }
        echo  "</li>";
    echo "</ul>";
}

Source Code Output:

<ul>
    <li>Demonstrate where to find the following documentation:
        <ul>
            <li>Operating and Safety Strategy</li>
        </ul>
    </li>
    <li>Explain the different turbine main operating states:
        <ul>
            <li>Power Production</li>
            <li>Idle</li>
            <li>Stop</li>
        </ul>
    </li>
    <li>Explain how to recognise the current operating mode on the display of the operating panel</li>
    <li>Explain the subsystem operating modes:
        <ul>
            <li>Stop</li>
            <li>Manual</li>
        </ul>
    </li>
    <li>Explain the difference between local and remote point of operation</li>
    <li>Explain that only one point of operation can be active at a time</li>
</ul>

Rendered Output: (courtesy of phptester.net)