Mysql 连接多个表中的行

Question

我在这里问是因为我需要搜索什么英语单词来搜索我想做的事情。我可以通过多个查询来解决这个问题，然后使用 for 和 foreach 循环连接数组，但如果有更聪明的方法，为什么要这样做呢。

我的查询现在看起来像:

SELECT p.* , pi.path, pv.videoid
FROM products p
JOIN productimage pi ON (p.id = pi.product)
JOIN productvideo pv ON (p.id = pv.product)
WHERE p.id=:id

我的 table 看起来像

产品:

|---id---|---name----|---desc---|---price---|---active----|
|---1----|---somet---|---dsa----|---456-----|---1/0-------|
|---2----|---somet2--|---ddsasa-|---44556---|---1/0-------|

产品视频:

|----id----|---product----|----videoid-------|
|----4-----|---1----------|-----youtubeid----|
|----4-----|---1----------|--secondyoutubeid-|

与图像的视频相同，我想对文件进行相同的处理

这样的结果很容易

//result of $stmt->fetchAll(PDO::FETCH_ASSOC);
$arr = array(
    0 => array(
        "id" => "1",
        "name" => "hello world",
        "category" => "3",
        "videoid" => array("oneyoutubeid", "secondid", "thirdid"), //videos that is for this product
        "path" => array("imagepathofproduct", "secondimageofproduct"),
        "active" => "1"
    ),
    1 => array(
        "id" => "2",
        "name" => "hello world product 2",
        "category" => "4",
        "videoid" => array("oneyoutubeid", "secondid", "thirdid"), //videos that is for this product
        "path" => array("imagepathofproduct", "secondimageofproduct"),
        "active" => "1"
    ),
);
to use it like this: return $stmt->fetchAll(PDO::FETCH_ASSOC); and start foreach for displaying data.

非常感谢

Answer 1

通过 concat 解决，我将其分解为数组。 但仍在等待更多智能解决方案。

解决方案:

SELECT p.* , GROUP_CONCAT(DISTINCT path ORDER BY pi.id) AS images, GROUP_CONCAT(DISTINCT videoid ORDER BY pi.id) AS videos FROM products p JOIN productimage pi ON (p.id = pi.product) JOIN productvideo pv ON (p.id = pv.product) WHERE p.id=1

首页它会对某人有所帮助