从php获取sqlite pdo join查询中的数组

Question

I would like to get a multidimensional array from my sqlite tables, with join. I have two tables:

TABLES:

- projects -----------        - images ------------------
- id                 -        - id                      -
- title              -        - filename                -
- images (has many)  -        - project_id (belongs to) -
----------------------        ---------------------------

and I want the following array

PROJECT_ARRAY
{
   id:     1
   title:  My Project
   images: IMAGES_ARRAY
   {
        image1.jpg
        image2.jpg
        image3.jpg
   }
}

how do I join my tables with sql query?

this does not work:

SELECT   project.title,        image.filename
FROM     project JOIN image
ON       image.project_id = project.id;

Answer 1

You cannot get a array with a subarray from one query.

Furthermore, your table names are plural, but your are not using the plural names in your query.

This query should get you going:

SELECT   p.id, p.title, i.filename
FROM     projects p 
JOIN     images i ON i.project_id = p.id;

This should get you a result set like this:

id: 1, title: My Project, filename: image1.jpg
id: 1, title: My Project, filename: image2.jpg
id: 1, title: My Project, filename: image3.jpg