php - while 循环中的多选查询

Question

用户表中有 963 行
视频表中有 872 行

用户表(fbid,name)中是列名
在视频表(fbid等)中是列名称。

使用下面的查询，我通过从视频表提供 fbid 来从用户表中获取名称。下面的查询仅返回 791 行，但它应该返回 872 行。

<?php
$counter = 1;
$q = "SELECT * FROM videos GROUP BY fbid ORDER BY score DESC, id ASC";
$r = mysqli_query($conn,$q);
if(mysqli_num_rows($r)>0): 
    while($row = mysqli_fetch_assoc($r)):
        $fbid=$row['fbid'];

        $q1 = "SELECT name FROM users WHERE fbid=".$fbid."";
        $r1 = mysqli_query($conn,$q1);
        while($row1 = mysqli_fetch_assoc($r1)):
            $name=$row1['name'];
            ?>

            <?php
            $counter++;
        endwhile;
    endwhile;
endif;
?>

Answer 1

SELECT v.* 
     , u.name
  FROM videos v
  JOIN users u
    ON u.fbid = v.fbid
  ORDER 
    BY v.score DESC
     , v.id ASC;