PHP MySQL在单个查询上查询多个类别

I have an application that categorizes links for a tools page. On this page I want to have multiple categories and each tool under those categories. As I am going to have 13 categories (with more as the tool gets used) I don't want to run a query for each category. I would prefer to run a single query to grab all the data.

我有一个应用程序,为工具页面的链接分类。在这个页面上,我希望在这些类别下有多个类别和每个工具。因为我将有13个类别(随着工具的使用更多)我不想为每个类别运行查询。我宁愿运行单个查询来获取所有数据。

Right now I have the page setup so that it displays each category with a drop down to view the tools. What I need to do is have it so that only the tools for that category show up. I'm having a problem working out how to go through all of that data from a single query.

现在我有页面设置,以便它显示每个类别的下拉菜单以查看工具。我需要做的就是让它只显示该类别的工具。我在解决如何从单个查询中查看所有数据时遇到问题。

Currently I am using a while statement to run through the results as an array.

目前我正在使用while语句将结果作为数组运行。

while($tools = $result->fetch_assoc()){
    if($tools['id'] = 1){
        echo $tools['name'];
    }
}

This is just a basic mockup of what I currently plan on doing. I can't help thinking however there is a better way to go about this. What is the best way to grab a big chunk of data from MySQL and then break it out into categories with PHP? If I need to run more than a single query that is fine, but I prefer not running a query for each category.

这只是我目前计划做的基本模型。我不禁想到有更好的方法来解决这个问题。从MySQL获取大量数据然后通过PHP将其分解为类别的最佳方法是什么?如果我需要运行多个单独的查询,但我不想为每个类别运行查询。

1 个解决方案

#1

If you don't have performance problems you can use GROUP_CONCAT function like this,

如果没有性能问题,可以像这样使用GROUP_CONCAT函数,

SELECT id,GROUP_CONCAT(name)
FROM tools
GROUP BY id

you may read more abut GROUP_CONCAT from mysql official web site here

你可以在这里从mysql官方网站上阅读更多关于GROUP_CONCAT的内容

#1