如何查询3个表，使用php / mySQL，保留右表中的所有类别？

Question

I have 3 tables.

• photos
• joins
• categories I would like to get a record of each photo with all of it's categories.

photos table:

joins table:

categories table:

This code gets all of the photos, but doesn't group the categories. I only want each photo to display once:

//define table
$tbl = "photos";
$joinsTbl = "joins";
$catsTbl = "categories";

//write query
$query = "SELECT $tbl.photoID, $tbl.photoSRC, $tbl.photoCredit, $joinsTbl.categoryID, $catsTbl.photo_category
          FROM $tbl
          LEFT JOIN $joinsTbl
            INNER JOIN $catsTbl
            ON $joinsTbl.categoryID = $catsTbl.categoryID
          ON $tbl.photoID = $joinsTbl.photoID
          LIMIT 100";

result (where the problem is multiple results for the same photo):

    [{
    "photoID": 1,
    "photoSRC": "0112_copy.jpg",
    "categoryID": null,
    "category": null
}, {
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "categoryID": 2,
    "category": "Deicing"
}, {
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "categoryID": 3,
    "category": "Training"
}, {
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "categoryID": 6,
    "category": "Familiarization"
}, {
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "categoryID": 7,
    "category": "Vehicle"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 3,
    "category": "Training"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 1,
    "category": "Water"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 2,
    "category": "Deicing"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 3,
    "category": "Training"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 4,
    "category": "Instruction"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 5,
    "category": "Spray"
},

Using GROUP BY

    $query = "SELECT $tbl.photoID, $tbl.photoSRC, $tbl.photoCredit, $joinsTbl.categoryID, $catsTbl.photo_category
          FROM $tbl
          LEFT JOIN $joinsTbl
            INNER JOIN $catsTbl
            ON $joinsTbl.categoryID = $catsTbl.categoryID
          ON $tbl.photoID = $joinsTbl.photoID
          GROUP BY $tbl.photoID
          LIMIT 100";

Result (where the problem is only 1 of the categories appears):

[{
    "photoID": 1,
    "photoSRC": "0112_copy.jpg",
    "categoryID": null,
    "category": null
}, {
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "categoryID": 2,
    "category": "Deicing"
}, {
    "photoID": 3,
    "photoSRC": "IMG_2492.jpg",
    "categoryID": 3,
    "category": "Training"
}, {
    "photoID": 4,
    "photoSRC": "IMG_20431.jpg",
    "categoryID": 44,
    "category": "Type I"
}, {
    "photoID": 5,
    "photoSRC": "IMG_3562.jpg",
    "categoryID": null,
    "category": null
}, {
    "photoID": 6,
    "photoSRC": "001pasp5.jpg",
    "categoryID": 2,
    "category": "Deicing"
}]

What I am trying to get (with multiple categories):

{
    "photoID": 2,
    "photoSRC": "IMG_2484.jpg",
    "photoCredit": "Michael Chaput",
    "categoryID": 2,
    "category": {"Deicing", "Training", "Scary", "Fluids"}
}, 

</div>

Answer 1

Consider MySQL's GROUP_CONCAT aggregate:

$query = "SELECT $tbl.photoID, $tbl.photoSRC, $tbl.photoCredit, $joinsTbl.categoryID,  
                 GROUP_CONCAT($catsTbl.photo_category SEPARATOR ',') AS categories
          FROM $tbl
          LEFT JOIN $joinsTbl
          INNER JOIN $catsTbl
             ON $joinsTbl.categoryID = $catsTbl.categoryID
             ON $tbl.photoID = $joinsTbl.photoID
          GROUP BY $tbl.photoID, $tbl.photoSRC, $tbl.photoCredit, $joinsTbl.categoryID
          LIMIT 100"