php嵌套查询mysql,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

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.

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?

解决方案

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 to get just a list of all rows:

$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 . '
';

}

PHP to get nested lists with 'complicated' query:

$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 '

• ';

$needToBeClosed = false;

while($row = $q->fetch_object()) {

if($row->type == 'project' AND $needToBeClosed) {

echo '

';

$needToBeClosed = false;

}

echo '

' . $row->name;

if($row->type == 'project') {

echo '

• ';

$needToBeClosed = true;

} else {

echo '

';

}

}

if($needToBeClosed) {

echo '';

}

echo '';

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 to get nested list without 'complicated' queries:

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

echo '

• ';

while($project = $projects->fetch_object()) {

echo '

' . $project->name . '

• ';

$todos = $db->query('SELECT * FROM `todos` WHERE `project_id` = ' . $project->id);

while($todo = $todos->fetch_object()) {

echo '

' . $todo->name . '';

}

echo '

';

}

echo '

';

You still need to modify the queries for your own needs (table names and such).