查询数据库后打印出部分唯一的数据 [英] Printing out partially unique data after querying a db
问题描述
我在弄清楚如何像我需要的那样使用表格数据时遇到了麻烦……我的表格包含一个类别名称列(对于许多子类别具有相同的值),另一列包含子类别名称.
I am having trouble finguring out how to use the table data like I need to... my table consists of a category name column (which has the same value for many subcategories) and the other column contains subcategory names.
例如:
col1 col2
------------------
apples fruits
oranges fruits
pears fruits
honda cars
volvo cars
audi cars
我正在尝试编写一个 select 语句,该语句从数据库中选择所有数据并仅打印一次类别名称,同时打印出该特定类别名称包含的所有子类别.
I am trying to write a select statement that selects all the data from the database and prints out the category name once ONLY, while printing out all subcategories that, that particular category name includes.
类似于:
水果:
- 苹果
- 橙子
- 梨
汽车:
- 本田
- 沃尔沃
- 奥迪
推荐答案
SELECT
col2,
GROUP_CONCAT(col1 SEPARATOR ',') as stuff
FROM table1
GROUP BY col2
如果你想在名字前面加上'-',你可以使用CONCAT('-',col1)
而不是col1
.
If you want the '-' in front of the names you can use CONCAT('-',col1)
instead of col1
.
这段php代码会将结果转换成一维数组
This php code will transform the result into a 1 dimensional array
$conn = new mysqli('host','user','pass','db_name');
if ($conn->connect_errno) {
printf("Connect failed: %s\n", $conn->connect_error);
exit();
}
if (!($rs = $conn->query("
SELECT
col2,
GROUP_CONCAT(col1 SEPARATOR ',') as stuff
FROM table1
GROUP BY col2
"))) {
printf("Error: %s\n", $conn->error);
exit();
}
$result = array();
while ($row = $rs->fetch_object()){
$result[] = $row->col2;
if(!empty($row->stuff)) $result = array_merge($result,explode(',',$row->stuff));
}
这篇关于查询数据库后打印出部分唯一的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!