CODEWITHSUNDEEP
phpmysql
Dean Hodges
Dean Hodges

Reputation: 23

PHP, MYSQL Nested Queries

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?

Upvotes: 2

Views: 17217

Answers (2)

seymar
seymar

Reputation: 4063

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 . '<br />';
}

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

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

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In this case, you have the same columns coming out from the query. Some are from Project and some are from Todo. The challenge is to order them in the right way:

select which, name
from ((select 'Project' as which, name, 1 as ordering, projectid, NULL as id
       from  Project p
      ) union all
      (select 'List' as which, entry, 2 as ordering, projectid, id
       from Todo t
      )
     ) t
order by projectid,
         ordering,
         id;

Upvotes: 0

Related Questions