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

cidc1ykv 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(338)

我有三张table。
照片
连接
类别我想得到它的所有类别的每一张照片的记录。
照片表：

联接表：

类别表：

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

//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"
},

使用分组依据

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

结果（问题仅出现其中一个类别）：

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

mysql php left-join

来源：https://stackoverflow.com/questions/50934573/how-can-i-query-3-tables-using-php-mysql-keeping-all-the-categories-in-the-rig

2条答案

按热度按时间

dkqlctbz1#

多亏了冻糕，以下是对我有用的东西：

$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
      LIMIT 100";

唯一不同的是，我只按一列进行分组。以下是全部要点：https://gist.github.com/brucenorton/63881fac229c76e757225472d62bfda7

赞(0）回复(0）举报 2021-06-20

i1icjdpr2#

考虑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"

赞(0）回复(0）举报 2021-06-20

我来回答

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

2条答案

相关问题

热门标签

最新问答