php - SQL LEFT JOIN 不复制相同的行值

Question

我想知道是否有人可以帮助我解决如下问题:我想拉一次属于那个 posts.text 的 posts.text 和 uid，但是当我执行下面的代码时，它会这样做:例如。有 4 个 uid 属于帖子，所以我得到 posts.text 四次而不是一次。

$query = mysqli_query($con,
                  "SELECT posts.text, relationships.uidb
                  FROM posts
                  LEFT JOIN relationships
                  ON posts.uid=relationships.uida
                  LIMIT 10");

if(mysqli_num_rows($query) > 0){
while($row = mysqli_fetch_assoc($query)){
    echo $row['text']." ".$row['uidb']."<br>";
}
}

如果有任何帮助，我将不胜感激。谢谢是提前。彼得

更新:期望的输出是这样的:

postsArray[0]->text = //post text
postsArray[1]->text = //another post text
postsArray[0]->uids[0] = //approved uid for first post
postsArray[0]->uids[1] = //another approved uid for first post

现在它输出这个:文本 10案文 15正文 12

我想要这个:正文 10、15、12

Answer 1

一种方法是使用 Mysql 的 GROUP_CONCAT，它为每个组提供逗号分隔值列表，即 (p.uid)

$query = mysqli_query($con,
    "SELECT p.text, GROUP_CONCAT(r.uidb SEPARATOR ', ') uidbs
     FROM posts p
     LEFT JOIN relationships r
     ON p.uid=r.uida
     GROUP BY p.uid
     LIMIT 10");

if (mysqli_num_rows($query) > 0) {
    while ($row = mysqli_fetch_assoc($query)) {
        echo $row['text'].' '.$row['uidbs'];
      /*$uidbs=  explode($row['uidbs'],',');
        foreach ($uidbs as $key => $val) {
            echo $val.' ';
        }*/
        echo '</br>';
    }
}

---

• GROUP_CONCAT

According to docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.