我想知道当我有两个单独的表时应该如何设计SQL查询,但是应该一起工作...likes
表
列-idlikes,idusers,like_whatuser_follow
表
列-idfollow,关注者,关注
为优化/性能设计查询的最佳方法是什么?可以合并查询吗?或者我应该这样分隔每个查询:
$iduser = 1 //I am user 1 in database.
$get = $db -> prepare("SELECT * FROM user_follow WHERE follower = ?")
$get -> execute(array($iduser));
while($row = $get->fetch(PDO::FETCH_ASSOC)){
$following = $row['following']; // get id of user that i'm following
$likes = $dbh->prepare("SELECT * FROM likes WHERE idusers = ?");
$likes->execute(array($following));
// do what I need to..
}
更新
$following = $dbh->prepare("SELECT L.* FROM likes L JOIN user_follow F on L.iduser = F.iduser_following WHERE F.iduser_follower = ?");
$following->execute(array($iduser));
while($row_following = $following->fetch(PDO::FETCH_ASSOC))
{
$id1 = $row_following['L.iduser'];
echo $id1;
}
最佳答案
这将在一个查询中完成
SELECT L.*
FROM likes L
JOIN user_follow F ON L.idusers = F.following
WHERE F.follower = ?
关于命名的注意事项:在sql中,每一行包含一个值,因此列名通常为单数。因此
likes.idusers
通常为likes.iduser
我还要弄清楚iduser是否为FK,因此在user_follow表中,我将
follower
命名为iduser_follower
,将following
命名为isuser_following
关于mysql - 具有两个单独表的SQL查询设计? MySQL PHP PDO性能,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/14657292/