php - 如何定义一个可以根据MySQL表中的数字获取数据的循环

Question

我正在使用 PHP OOP 开发一个 cms，在这个项目中我想添加一个页面，站点管理员可以在其中简单地查看网站的所有菜单导航并浏览它们并编辑菜单链接。

在 MySQL 数据库中，我创建了一个名为 menu_nav 的表，在这个表中，我创建了 16 列，管理员可以制作最多包含菜单链接的菜单:

print screen of table

例如，我插入了一个名为 top_nav 的菜单，它有 5 个菜单项。

现在为了将这些数据检索到管理员可以编辑菜单链接的页面中，我这样做了:

<div class='box-body table-responsive no-padding'>
                      <table class='table table-hover'>
                        <tr>
                          <th>Number</th>
                          <th>Link</th>
                          <th></th>
                        </tr>

                        <tr>
                          <td>1</td>
                          <td>".$menuSet->GetMenuLink1($id)."</td>
                          <td>
                            <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
                            <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
                            <a title='Move Down' href='#'><span class='glyphicon glyphicon-chevron-down'></span></a>
                          </td>
                        </tr>
                        <tr>
                          <td>2</td>
                          <td>".$menuSet->GetMenuLink2($id)."</td>
                          <td>
                            <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
                            <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
                            <a title='Move Up' href='#'><span class='glyphicon glyphicon-chevron-up'></span></a>
                            <a title='Move Down' href='#'><span class='glyphicon glyphicon-chevron-down'></span></a>
                          </td>
                        </tr>
                        <tr>
                          <td>3</td>
                          <td>".$menuSet->GetMenuLink3($id)."</td>
                          <td>
                            <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
                            <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
                            <a title='Move Up' href='#'><span class='glyphicon glyphicon-chevron-up'></span></a>
                            <a title='Move Down' href='#'><span class='glyphicon glyphicon-chevron-down'></span></a>
                          </td>
                        </tr>
                        <tr>
                          <td>4</td>
                          <td>".$menuSet->GetMenuLink4($id)."</td>
                          <td>
                            <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
                            <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
                            <a title='Move Up' href='#'><span class='glyphicon glyphicon-chevron-up'></span></a>
                            <a title='Move Down' href='#'><span class='glyphicon glyphicon-chevron-down'></span></a>
                          </td>
                        </tr>
                        <tr>
                          <td>5</td>
                          <td>".$menuSet->GetMenuLink5($id)."</td>
                          <td>
                            <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
                            <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
                            <a title='Move Up' href='#'><span class='glyphicon glyphicon-chevron-up'></span></a>
                          </td>
                        </tr>
                      </table>
                    </div>

结果是这样的:

print screen of menu settings page

但我想做的是计算表格中的 menu_items 字段并根据该数字制作表格。例如，如果它设置为 5，那么它应该动态创建一个包含 5 行的表格，并通过循环从表格中的每一列获取数据，直到 menu_link_5 并完成循环。

所以因为这背后的理论对我来说非常复杂，我需要一些编程专家的帮助。所以我才发现这个论坛，我希望有人能帮助我，因为我真的需要它!

如果您想看一下，这里还有 menu.class.php:

<?php 
class Menus
{
    public $id,$mname,$menui,$menul1,$menul2,$menul3,$menul4,$menul5,$menul6,$menul7,$menul8,$menul9,$menul10,$menul11,$menul12,$menul13;
    public function __construct()
    {
        $this->db = new Connection();
        $this->db = $this->db->dbConnect();
    }
    public function GetMenus()
    {
        if(empty($name))
        {
            $menu = $this->db->prepare("select * from menu_nav");
            $menu->execute();
            $menu_array = array();
            while($row = $menu->fetch())
            {
                $menu_array[] = $row;
            }
            return $menu_array;
        }
        else
        {
            header("Location: php/includes/errors/008.php");
            exit();
        }
    }
    public function SelectMenuById($id)
    {
        if(!empty($id))
        {
            $mnu = $this->db->prepare("select * from menu_nav where id = ?");
            $mnu->bindParam(1,$id);
            $mnu->execute();
            while($row = $mnu->fetch())
            {
                $this->id           = $row['id'];
                $this->mname        = $row['menu_name'];
                $this->menui        = $row['menu_items'];
                $this->menul1       = $row['menu_link_1'];
                $this->menul2       = $row['menu_link_2'];
                $this->menul3       = $row['menu_link_3'];
                $this->menul4       = $row['menu_link_4'];
                $this->menul5       = $row['menu_link_5'];
                $this->menul6       = $row['menu_link_6'];
                $this->menul7       = $row['menu_link_7'];
                $this->menul8       = $row['menu_link_8'];
                $this->menul9       = $row['menu_link_9'];
                $this->menul10      = $row['menu_link_10'];
                $this->menul11      = $row['menu_link_11'];
                $this->menul12      = $row['menu_link_12'];
                $this->menul13      = $row['menu_link_13'];
            }
        }
        else
        {
            header("Location: php/includes/errors/009.php");
            exit();
        }
    }
    public function DeleteMenu($id)
    {
        if(!empty($id))
        {
            $adm = $this->db->prepare("delete from menu_nav where id = ?");
            $adm->bindParam(1,$id);
            $adm->execute();
        }
        else
        {
            header("Location: php/includes/errors/010.php");
            exit();
        }
    }
    public function GetId()
    {
        return $this->id;
    }
    public function GetMenuName()
    {
        return $this->mname;
    }
    public function GetMenuItems()
    {
        return $this->menui;
    }
    public function GetMenuLink1()
    {
        return $this->menul1;
    }
    public function GetMenuLink2()
    {
        return $this->menul2;
    }
    public function GetMenuLink3()
    {
        return $this->menul3;
    }
    public function GetMenuLink4()
    {
        return $this->menul4;
    }
    public function GetMenuLink5()
    {
        return $this->menul5;
    }
    public function GetMenuLink6()
    {
        return $this->menul6;
    }
    public function GetMenuLink7()
    {
        return $this->menul7;
    }
    public function GetMenuLink8()
    {
        return $this->menul8;
    }
    public function GetMenuLink9()
    {
        return $this->menul9;
    }
    public function GetMenuLink10()
    {
        return $this->menul10;
    }
    public function GetMenuLink11()
    {
        return $this->menul11;
    }
    public function GetMenuLink12()
    {
        return $this->menul12;
    }
    public function GetMenuLink13()
    {
        return $this->menul13;
    }
}
?>

Answer 1

首先，我认为您的数据库结构不是最佳的。在这种情况下你真的不应该有空列，因为它只会使事情复杂化。我建议您规范化数据以帮助实现数据冗余 ^{read more} .

这消除了对空列的需要。现在我们已经处理了您的数据库结构，这显然意味着要更改您的代码。这也意味着您可以拥有无​​限的菜单项，而无需向数据库中添加列。

在您的 SelectMenuById() 函数中，我们将拆分查询，为了获取菜单名称，我们只需执行以下操作

SELECT name FROM menu WHERE id = ?

获取菜单链接的查询我们将执行以下操作:

SELECT menu_items.id,
       menu_items.link 
FROM   menu, 
       menu_items 
WHERE  menu.id = ?
   AND menu_items.menu_id = menu.id

结果(ID 为 1):

^fiddle

接下来，我们删除所有的 $menul1、$menul2 等变量，并将它们替换为 $menuItems 变量(或类似的规定)。回到您的 SelectMenuById() 函数，它现在看起来像这样^未测试:

public function SelectMenuById($id)
{
    if(!empty($id))
    {
        $mnu = $this->db->prepare("select * from menu where id = ?");
        $mnu->bindParam(1,$id);
        $mnu->execute();
        $result = $mnu->fetch();

        $this->id = $result['id'];
        $this->mname = $result['name'];

        $mnu = $this->db->prepare("SELECT menu_items.id, menu_items.link FROM menu, menu_items WHERE  menu.id = ? AND menu_items.menu_id = menu.id;");
        $mnu->bindParam(1,$id);
        $mnu->execute();

        while($row = $mnu->fetch())
        {
            $this->menuItems[] = ["id" => $row['id'], "link" => $row['link']];
        }
    }
    else
    {
        header("Location: php/includes/errors/009.php");
        exit();
    }
}

您应该为 $this->menuItems 添加一个 getter 方法，并删除其他变量的 getter。最后，到您的 HTML。我们只是使用 foreach() 循环在页面上获取结果 ^untested。

<div class='box-body table-responsive no-padding'>
  <table class='table table-hover'>
    <tr>
      <th>Number</th>
      <th>Link</th>
      <th></th>
    </tr>
    <?php
        foreach($menuSet->getMenuItems() as $menuItemID => $menuItem):
    ?>
    <tr>
      <td><?php echo $menuItemID; ?></td>
      <td><?php echo $menuItem; ?></td>
      <td>
        <a title='Edit' href='#'><span class='glyphicon glyphicon-pencil'></span></a>
        <a title='Remove' href='#'><span class='glyphicon glyphicon-remove'></span></a>
        <a title='Move Down' href='#'><span class='glyphicon glyphicon-chevron-down'></span></a>
      </td>
    </tr>
    <?php 
        endforeach;
    ?>
  </table>
</div>