时间戳

首页 > 解决方案 > 如何使用 php/mySQL 查询 3 个表，将所有类别保留在正确的表中？

php - 如何使用 php/mySQL 查询 3 个表，将所有类别保留在正确的表中？

问题描述

我有 3 张桌子。

照片表：

照片表

连接表：

连接表

类别表：

在此处输入图像描述

此代码获取所有照片，但不会对类别进行分组。我只希望每张照片显示一次：

//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";

结果（问题是同一张照片的多个结果）：

    [{
	"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"
},

使用 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";

结果（问题仅出现 1 个类别）：

[{
	"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"
}]

我想要得到什么（有多个类别）：

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

标签： phpmysqlleft-join

解决方案

考虑 MySQL 的GROUP_CONCAT聚合：

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

推荐阅读