Creating a multidimentional array result from a single mysql query

Question

I have researched about this for days now and I can't seem to find the right query for me.

Tables

1. Projects

id
projectname
date

2. Notes

noteid
note
projectid

Wanted Result:

array =>
    project1 =>
        Notes1
        Notes2
        Notes3

    project2 =>
        Notes1
        Notes2
    project3 =>
    ….........

I am struggling to create a single query that will populate the result i wanted - Select all fields from projects and create another array for each id to hold all results that match project.id on notes.projectid.

wesleywmd · Accepted Answer · 2013-09-19 13:30:47Z

You will need to use what ever language your using to organize the array for you.

The SQL query you want to use is:

SELECT projects.*, notes.noteid, notes.note
FROM projects LEFT JOIN notes ON
    projects.id = notes.projectid
LIMIT 250;

Use a limit so you don't end up with a huge dataset. You can set the limit to whatever suits your needs or omit it if you are sure you don't need to wory about dataset size.

This will return a dataset like this:

+----------+-------------+----------+----------+----------+
| id       | projectname | date     | noteid   | note     |
+----------+-------------+----------+----------+----------+
| 1        | Project1    | 1/1/2000 | 1        | Notes1   |
| 1        | Project1    | 1/2/2000 | 2        | Notes2   |
| 1        | Project1    | 1/3/2000 | 3        | Notes3   |
| 2        | Project2    | 1/4/2000 | 4        | Notes1   |
| 2        | Project2    | 1/5/2000 | 5        | Notes2   |
| 3        | Project3    | 1/6/2000 | NULL     | NULL     |
+----------+-------------+----------+----------+----------+

I used a LEFT JOIN for the instances that a project doesn't have any notes yet. You can see what the results would look like for Project3. Then its up to your programming language to organize your dataset. Without knowing the limitations for your language, I don't want to theorize too much about how to organize this.

Collectives™ on Stack Overflow

Creating a multidimentional array result from a single mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related