php mysql和GROUP

php mysql和GROUP_CONCAT(php mysql and GROUP_CONCAT)

我有3张桌子：

module_names（module_id，module_name）

1 | test_module 2 | test_module2 3 | test_module3

module_groups（group_id，group_name）

1 | 组 2 | 第2组 3 | 组3 4 | 组4

modules_to_groups（module_id，group_id）

2 | 2 1 | 2 3 | 4

当我在查询中使用GROUP_CONCAT时，我得到了正确的结果

group2 -> test_module , test_module2 group4 -> test_module3

但我想要：

group1 -> (no module added yet) group2 -> test_module , test_module2 group3 -> (no module added yet) group4 -> test_module3

我使用的整个功能：

function ListModuleGroups() { global $admin,$prefix, $db, $admin_file; OpenTable(); echo '<div><b>Groups and Modules :</b></div>'; $result = $db->sql_query("SELECT m.module_id, m.module_name, g.group_name, g.group_id, GROUP_CONCAT(m.module_name) from " . $prefix . "_mmmodule_groups g, " . $prefix . "_mmmodule_names m, " . $prefix . "_mmmodule_to_group m2g where m.module_id = m2g.module_id and m2g.group_id = g.group_id and g.group_id = g.group_id group by g.group_id"); while($row = $db->sql_fetchrow($result)) { $group_name = $row['group_name']; $module_name = $row['GROUP_CONCAT(m.module_name)']; echo "<div>".htmlentities($group_name). '--' . htmlentities($module_name)." </div>"; } CloseTable(); }

I'm having 3 tables:

module_names (module_id,module_name)

1 | test_module 2 | test_module2 3 | test_module3

module_groups (group_id,group_name)

1 | group 2 | group2 3 | group3 4 | group4

modules_to_groups (module_id,group_id)

2|2 1|2 3|4

When i use GROUP_CONCAT in my query i getting the correct results

group2 -> test_module , test_module2 group4 -> test_module3

But i want:

group1 -> (no module added yet) group2 -> test_module , test_module2 group3 -> (no module added yet) group4 -> test_module3

The whole function i use:

function ListModuleGroups() { global $admin,$prefix, $db, $admin_file; OpenTable(); echo '<div><b>Groups and Modules :</b></div>'; $result = $db->sql_query("SELECT m.module_id, m.module_name, g.group_name, g.group_id, GROUP_CONCAT(m.module_name) from " . $prefix . "_mmmodule_groups g, " . $prefix . "_mmmodule_names m, " . $prefix . "_mmmodule_to_group m2g where m.module_id = m2g.module_id and m2g.group_id = g.group_id and g.group_id = g.group_id group by g.group_id"); while($row = $db->sql_fetchrow($result)) { $group_name = $row['group_name']; $module_name = $row['GROUP_CONCAT(m.module_name)']; echo "<div>".htmlentities($group_name). '--' . htmlentities($module_name)." </div>"; } CloseTable(); }

最满意答案

首先，你的SQL中似乎有一个奇怪的条件 - g.group_id = g.group_id。 也许您可以尝试删除它。

其次，你应该使用LEFT JOIN来读取所有组。 LEFT JOIN选择左侧表格中的所有记录，只选择表格中右侧的匹配记录。 请参阅MySQL文档以进一步阅读。

下面的代码可能有助于获得所需的resutls：

function ListModuleGroups() { global $admin,$prefix, $db, $admin_file; OpenTable(); echo '<div><b>Groups and Modules :</b></div>'; $result = $db->sql_query("SELECT g.group_name, GROUP_CONCAT(m.module_name) modules from " . $prefix . "_mmmodule_groups g LEFT JOIN " . $prefix . "_mmmodule_to_group m2g ON g.group_id = m2g.group_id LEFT JOIN " . $prefix . "_mmmodule_names m ON m2g.module_id = m.module_id GROUP BY g.group_id"); while($row = $db->sql_fetchrow($result)) { $group_name = $row['group_name']; if (!empty($row['modules'])) { $module_name = $row['modules']; } else { $module_name = '(no module added yet)'; } echo "<div>".htmlentities($group_name). '--' . htmlentities($module_name)." </div>"; } CloseTable(); }

希望能帮助到你！

first there seems to be a weird condition in your SQL - g.group_id = g.group_id. Perhaps you may try to remove it.

Second, you should use LEFT JOIN for reading all groups. LEFT JOIN selects all records from the table on the left and only matching records from the table to the right. Please refer to MySQL docs for further reading.

Below code might help get desired resutls:

function ListModuleGroups() { global $admin,$prefix, $db, $admin_file; OpenTable(); echo '<div><b>Groups and Modules :</b></div>'; $result = $db->sql_query("SELECT g.group_name, GROUP_CONCAT(m.module_name) modules from " . $prefix . "_mmmodule_groups g LEFT JOIN " . $prefix . "_mmmodule_to_group m2g ON g.group_id = m2g.group_id LEFT JOIN " . $prefix . "_mmmodule_names m ON m2g.module_id = m.module_id GROUP BY g.group_id"); while($row = $db->sql_fetchrow($result)) { $group_name = $row['group_name']; if (!empty($row['modules'])) { $module_name = $row['modules']; } else { $module_name = '(no module added yet)'; } echo "<div>".htmlentities($group_name). '--' . htmlentities($module_name)." </div>"; } CloseTable(); }

Hope it helps!