将mysql结果按类别分组，并在每个类别下按组显示 [英] Group mysql results by category and display them into groups under each category

问题描述

我正在尝试创建一个简单的css菜单，该菜单从mysql表中获取数据.

I am trying to create a simple css menu that gets the data from a mysql table.

我的想法是拥有这样的菜单

My idea is to have menu like this

    Category 1
    - link 1
    - link 2
    - link 3
    Category 2
    - link 1
    - link 2
    - ect...

每个链接都有一个名为类别"的字段.因此，我想按类别对菜单中的链接进行分组和显示.

every link has a field named "category". So I want to group and display the links in the menu per category.

我有类似mysql分组

I have mysql grouping like

$sql = "SELECT * FROM content group by category";
$result = mysql_query($sql); 

然后我有这样的html

and then I have the html like this

<ul class="menu">
    <li id="category1" class="files">
         <a href="#category1">Category 1</a>
         <ul class="sub-menu">
             <li><a href="#">link 1</li>
             <li><a href="#">link 2</li>
             <li><a href="#">link 3</li>
         </ul>
     </li>
    <li id="category2" class="files">
         <a href="#category2">Category 2</a>
         <ul class="sub-menu">
             <li><a href="#">link 1</li>
             <li><a href="#">link 2</li>
             <li><a href="#">link 3</li>
         </ul>
     </li>
 </ul>

db表看起来像这样

CREATE TABLE IF NOT EXISTS `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `menu_name` text,
  `menu_name_en` text,
  `menu_url` varchar(255) NOT NULL DEFAULT '',
  `header_name` text,
  `header_name_en` enum('MEDIA','GENERAL') NOT NULL DEFAULT 'MEDIA',
  `text` longtext NOT NULL,
  `text_en` text,
  `category` enum('Category 1', 'Category 2') NOT NULL DEFAULT 'Category 1',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `visible` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

INSERT INTO content (id, menu_name, menu_name_en, menu_url, header_name, header_name_en, text, text_en, category, date, visible) VALUES (26, 'test name', '', 'test_url', 'test name', '', '<p>test text</p>', '<p>text text</p>', 'MEDIA', '2014-02-23', '1');

因此，我很难将结果放入循环中并按类别创建html.

So, I am having troubles putting the results in the loop and creating the html by category.

我在这里阅读了许多内容相似的帖子，但无法达到我想要的结果. 任何帮助都感激不尽.谢谢！

I read many posts here with similar content but couldn't achieve the result I wanted. Any help will be much appreciated. Thanks!

推荐答案

我会改用ORDER BY category.然后，您可以像这样迭代结果集

I would use ORDER BY category instead. You can then iterate the result set like

$old = null;
foreach ($st as $s) {
  if $old != $s['id']
    echo 'Main category';
    $old = $s['id'];
  echo 'subcategory'

更新

到目前为止，针对该问题本身，存在三种可能的解决方案.

Update

There exist three possible solutions until now in this thread to the problem itself.

SELECT * FROM content group by category
foreach
  SELECT * FROM content WHERE category=$cat['category']

如果只希望一次获取每个父类别，则应改用DISTINCT.如果不使用任何聚合函数，则不应使用GROUP BY. GROUP BY与SELECT *的组合仅限于(大多数情况下)MySQL.在这种情况下，您无法在ASNI SQL中选择任意列.

If one does only want to get each parent category once, one should use DISTINCT instead. One should not use GROUP BY without using any aggregation function. Combining GROUP BY with SELECT * is limited to (mostly) MySQL. You cannot select arbitrary columns in this case in ASNI SQL.

SELECT DISTINCT category FROM content ORDER BY category
foreach
  SELECT * FROM content WHERE category=$cat['category']

这是使用DISTINCT而不是GROUP BY的更正版本.

This is the corrected version with DISTINCT instead of GROUP BY.

它仍然缺少嵌套查询调用.对于5个父类别，这会在循环中导致5个查询.对于10个父类别，内部已经有10个查询.人们应该避免这种增长.

It still lacks of nested query calls. For 5 parent categories, this leads to 5 queries in the loop. For 10 parent categories, there are already 10 queries inside. One should avoid this kind of growing in general.

SELECT * FROM content ORDER BY category, menu_name

与上面的代码一起使用.

usable with the code above.

由于不同的原因，这优于显示的其他选项:

This is preferable to the other options shown due to different reasons:

• 您只需要一个数据库查询即可一次收集所有数据.数据库花了大部分时间(在简单的查询上)来解析提供的SQL语句，而只花了很少的时间来实际收集您所请求的数据.如果您提供大量的SQL代码，则必须花费大量时间来解析它.如果您提供的代码更少，那么要做的事就更少了.
• 对于数据库而言，一次获取数据，对数据进行一次排序然后将其返回给您更容易，而不是收集一部分，对一部分进行排序，返回一部分并重新开始.

存在一个迄今尚未阐明的进一步解决方案.可以使用准备好的语句，一次准备SQL，然后用不同的ID运行它.这样仍然可以查询循环中的所有类别，但可以避免每次都解析SQL代码的必要性.

There exists an until now unstated further solution. One can use prepared statements, prepare the SQL once and run it with different ids. This would still query all categories inside the loop, but would avoid the necessity to parse SQL code every time.

实际上，我不知道这比我的解决方案好还是坏(或两者之间).

Actually I do not know if this is better or worse (or sth. in between) than my solution.

这篇关于将mysql结果按类别分组，并在每个类别下按组显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！