好吧,这听起来很愚蠢,但在最好的方法上有一些困难;我正试图获取一个帐户关注的所有用户;然后,一旦我从下表中获取了我关注的所有用户,就获取了users表的用户信息。这就是我到目前为止所拥有的,但它真的很乱,也不起作用。
<?
$usr_id = '34';
$sql = "SELECT * FROM following WHERE usr_id = '$usr_id'";
$result = mysql_query($sql);
// $following is the users that we are following.
while ($row = mysql_fetch_assoc($result)) {
$following = $row['following'];
$src = "SELECT * FROM usr_users WHERE username = '$following'";
$getfollowinginfo = mysql_query($src);
while ($user = mysql_fetch_assoc($getfollowinginfo)) {
echo $user['email'];
}}
?>
正如其他人所提到的,JOIN
在这里更合适。基于您的示例,这里有一个简单的SQL Fiddle,展示了它的工作原理。
最重要的部分是瞄准Following
表,然后瞄准JOIN
到Users
表,如下所示:
SELECT u.id, u.name
FROM Following f
JOIN Users u on u.id=f.following
WHERE f.userId = 3 -- Can be any identifier for the user.
对于小型或简单的数据集,这是完全可以的,但有些人可能建议您删除WHERE
子句,转而扩展JOIN条件,如下所示:
SELECT u.id, u.name
FROM Following f
JOIN Users u on u.id=f.following
AND f.userId = 3 -- Can be any identifier for the user.
要将其集成到您的代码中,就像这样简单;
<?
$usr_id = '34';
$sql = "SELECT u.id, u.email FROM following f JOIN usr_users u on u.id=f.following AND f.usr_id = '$usr_id'";
$result = mysql_query($sql);
while ($user = mysql_fetch_assoc($result )) {
echo $user['email'];
}
?>