PHP，MYSQL嵌套查询 [英] PHP, MYSQL Nested Queries

问题描述

我正在使用php和Mysql查询数据库.我正在尝试创建以下内容:

I am using php and Mysql to query a database. What i am trying to do is create the following:

Project Name 1
List todo
List todo
List todo 

Project Name 2
List todo
List todo
List todo 

我有以下mysql表:

I have the following mysql tables:

Project
project.projectId
project.projectName

Todo
todo.todoId
todo.todoEntry
todo.todoProjectid

我可以查询表并浏览整个项目列表，然后加入待办事项.但是，我想创建一个标题(项目名称)，然后在下面执行循环.

I can query the table and pull through an entire list of projects and left join the todo. BUT, i want to create a header (Project name) and then underneath do the loop.

我可以基于父项对每个待办事项执行嵌套的sql查询，但这听起来对我来说效率不高.有人建议改善吗?

I could do a nested sql query for each todo based on the parent but that doesnt sound efficient enough to me. Anyone suggest an improvement?

推荐答案

我使Gordons查询更加简单.现在，它完全可以满足您的要求.

I made Gordons query a little bit simpler. It now exactly meets your requirements.

SELECT
    `name`
FROM 
    (
        (SELECT 'project' as `type`, `name`, `id` as `projectid` FROM `Project`)
        UNION ALL
        (SELECT 'todo' as `type`, `name`, `projectid` FROM `Todo`)
    ) as `combined`
ORDER BY
    `project_id`, `type`

---

PHP仅获取所有行的列表:

$q = $db->query("SELECT `name` FROM ((SELECT 'project' as `type`, `name`, `id` as `project_id` FROM `projects`) union all (SELECT 'todo' as `type`, `name`, `project_id` FROM `todos`)) as `combined` ORDER BY `project_id`, `type`");

while($row = $q->fetch_object()) {
    echo $row->name . '<br />';
}

---

PHP通过复杂"查询获取嵌套列表:

$q = $db->query("SELECT `name`, `type` FROM ((SELECT 'project' as `type`, `name`, `id` as `project_id` FROM `projects`) union all (SELECT 'todo' as `type`, `name`, `project_id` FROM `todos`)) as `combined` ORDER BY `project_id`, `type`");

echo '<ul>';

$needToBeClosed = false;

while($row = $q->fetch_object()) {
    if($row->type == 'project' AND $needToBeClosed) {
        echo '</ul></li>';
        $needToBeClosed = false;
    }

    echo '<li>' . $row->name;

    if($row->type == 'project') {
        echo '<ul>';
        $needToBeClosed = true;
    } else {
        echo '</li>';
    }
}

if($needToBeClosed) {
    echo '</ul></li>';
}

echo '</ul>';

---

但是正如您所看到的，您尝试在查询中做的更多.需要更多的PHP以简单的方式使用它.因此，您需要在SQL和PHP之间找到平衡，以获取最佳代码.我通常不会采用上述方法，而只是执行多个查询，如下所示:

---

But as you can see, the more you try to do in your query. The more PHP is need to use it in a simple way. So you need to find a balance between SQL and PHP to get the nicest code. I would normally not go for the approach above, but just perform multiple queries like this:

PHP获取没有复杂"查询的嵌套列表:

$projects = $db->query('SELECT * FROM `projects`');

echo '<ul>';

while($project = $projects->fetch_object()) {
    echo '<li>' . $project->name . '<ul>';
    $todos = $db->query('SELECT * FROM `todos` WHERE `project_id` = ' . $project->id);

    while($todo = $todos->fetch_object()) {
        echo '<li>' . $todo->name . '</li>';
    }

    echo '</ul></li>';
}

echo '</ul>';

---

您仍然需要根据自己的需要(表名等)修改查询.

这篇关于PHP，MYSQL嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！