php - 如何将类别和子类别放在带有 optgroup 的选择框上，只需使用 PHP 进行一次查询？

Question

我想知道如何仅用一个查询就可以使用 optgroup 创建带有类别和子类别的选择框。

表格类别:

cat_id  int(10) unsigned Auto Increment  
cat_catid   int(10) unsigned NULL    
cat_name    varchar(100)

内容:

| cat_id | cat_catid | cat_name      |
+--------+-----------+----------------
|      1 |      NULL | Category - A  |
|      2 |         1 | 1             |
|      3 |         1 | 2             |
|      4 |      NULL | Category - B  |
|      5 |         4 | 1             |
|      6 |         4 | 2             |
|      7 |         4 | 3             |

用一个查询和 PHP 将其变成:

<select>
<optgroup label="Area - A">
    <option>1</option>
    <option>2</option>
</optgroup>
<optgroup label="Area - B">
    <option>1</option>
    <option>2</option>
    <option>3</option>
</optgroup>
</select>

Answer 1

您将需要在 PHP 中对您的查询进行一些后处理。我更喜欢将行重组为多维数组:

$rows = query("SELECT * FROM table ORDER BY cat_catid"); // (Just make sure NULL's are first)

$cats = [];
foreach ( $row as $row ) {
    // Do we have a root category?
    if ( $row['cat_catid'] === null ) {
        // Start a new array, and no children
        $cats[ $row['cat_id'] ] = [
            'name' => $row['cat_name'],
            'children' => []
        ];
    } else {
        // Add this category name to the parent category ID.
        $cats[ $row['cat_catid'] ]['children'][] = $row['cat_name'];
    }
}

echo '<select>';

foreach ( $cats as $cat ) {
    echo '<optgroup label="', $cat['name'], '">';
    foreach ( $cat['children'] as $child ) {
        echo '<option>', $child, '</option>';
    }
    echo '</optgroup>';
}

echo '</select>';