php - 基于数据库中的数据在 PHP 中动态构建菜单太慢

Question

我正在尝试使用以下代码创建动态菜单，但加载时间太长:

 <?php
 ob_start();
 include ('admin/db/db.php');

 echo '<li><a href="#">Exports</a>';
 $sql3 = "SELECT * FROM category_master where type = 'Export' order by      sequence asc ";
 $result3 = mysqli_query($connection, $sql3);

 if (mysqli_num_rows($result3) > 0)
     {

// output data of each row

echo '<ul>';
while ($userrows3 = mysqli_fetch_assoc($result3))
    {
    $cid = $userrows3['product_category_id'];
    $cname = $userrows3['category_name'];
    echo '<li><a href="category_details.php?cid=' . $cid . '&cname=' . $cname . '">' . $cname . '</a>';
    $sql4 = "SELECT group_product FROM products,category_master,cat_wise_pro where category_master.product_category_id = cat_wise_pro.category_id and products.product_id = cat_wise_pro.product_id and category_master.product_category_id =$cid group by products.group_product ";
    $result4 = mysqli_query($connection, $sql4);
    if (mysqli_num_rows($result4) > 0)
        {

        // output data of each row

        echo '<ul>';
        while ($userrows4 = mysqli_fetch_assoc($result4))
            {
            $gname = $userrows4['group_product'];
            echo '<li><a href="product_details.php?gname=' . $gname . '">' . $gname . '</a>';
            $sql5 = "SELECT product FROM products,group_products where products.group_product = group_products.group_product and  products.group_product = '$gname'";
            $result5 = mysqli_query($connection, $sql5);
            if (mysqli_num_rows($result5) > 0)
                {

                // output data of each row

                echo '<ul>';
                while ($userrows5 = mysqli_fetch_assoc($result5))
                    {
                    $pname = $userrows5['product'];
                    echo '<li><a href="product_info.php?pname=' . $pname . '">' . $pname . '</a>';
                    }

                echo '</ul>';
                }
            }

        echo '</ul>';
        }
    }
}

 echo '</ul>';
 ?>

我应该使用 View 而不是表... find below the view i create

有什么办法让它更快吗？

Answer 1

您的代码所做的是:

categories = load C categories from DB
foreach category:
    groups = load G groups from DB
    foreach group:
        products = load products from DB

向数据库发送 1 + C x G 查询。你应该做的是:

products = load products with groups with categories
$tree = [];
foreach ($products as $p):
    $tree[$p['category']][$p['group']][] = $p;

foreach ($tree as $category => $groups):
    foreach ($groups as $group => $products):
        foreach ($products as $p):
            ...

使用JOIN 一次获取数据，例如像这样的东西:

SELECT p.name as product, g.name as group, c.name as category
  FROM product p
  JOIN group_product g ON p.group_product = g.id
  JOIN category_master c ON g.category = c.id;