php - 检查用户是否为帖子添加书签的更好方法

Question

我正在为网站做一个小书签功能，用户可以在其中为帖子添加书签。当用户为帖子添加书签时，它会添加到表 postMarks 中。

我目前的做法是从表 posts 中打印主要帖子，然后从表 postMarks 中打印 select count()每个 postId = id 和 userId = currentUser。有没有更好、更有效的方法来执行此操作，而不是运行两个单独的语句。我觉得我这样做的方式效率很低。

我所有的代码都在下面。由于这仍未实现，我可以进行更改。

帖子

"id"    "postTitle"
"1"     "Title 1"
"2"     "Title 2"
"3"     "Title 3"
"4"     "Title 4"
"5"     "Title 5"
"6"     "Title 6"
"7"     "Title 7"
"8"     "Title 8"
"9"     "Title 9"
"10"    "Title 10"

邮戳

"id"    "postId"    "postTitle" "userId"    "addDate"
"1"     "1"         "Title 1"   "1"         "2014-04-22 09:57:35"
"2"     "2"         "Title 2"   "1"         "2014-04-22 09:58:10"
"3"     "3"         "Title 3"   "1"         "2014-04-22 09:58:12"
"4"     "4"         "Title 4"   "1"         "2014-04-22 09:58:25"
"5"     "5"         "Title 5"   "1"         "2014-04-22 09:58:37"
"6"     "1"         "Title 1"   "2"         "2014-04-22 09:59:10"
"7"     "2"         "Title 2"   "2"         "2014-04-22 09:59:20"
"8"     "1"         "Title 1"   "3"         "2014-04-22 09:59:33"
"9"     "2"         "Title 2"   "3"         "2014-04-22 09:59:45"
"10"    "3"         "Title 3"   "3"         "2014-04-22 09:59:53"

我的代码

$userId = 1;

$pageData = 'select id, postTitle from posts LIMIT 10';

$stmt = $conn->prepare($pageData);

$stmt->execute();

$stmt->bindColumn(1,$a);
$stmt->bindColumn(2,$b);

$posts = $stmt->rowCount();

if($posts >= 1) {

    while($rows = $stmt->fetch(PDO::FETCH_BOUND)) {
        echo $a.' '.$b;

        // Bookmarks
        $bookMarks = "select count(postId) from postMarks where postId = '".$a."' and userId = '".$userId."'";

        $stmtx = $conn->prepare($bookMarks);

        $stmtx->execute();

        $stmtx->bindColumn(1,$a);

        $stmtx->fetch();

        if($a == 1) {
            echo ' Bookmarked <br>';
        } else {
            echo ' Not Bookmarked <br>';
        }           
        // Bookmarks detect ends
    }

} else {
    echo 'Nothing to display ';
}

结果

1 Title 1 Bookmarked
2 Title 2 Bookmarked
3 Title 3 Bookmarked
4 Title 4 Bookmarked
5 Title 5 Bookmarked
6 Title 6 Not Bookmarked
7 Title 7 Not Bookmarked
8 Title 8 Not Bookmarked
9 Title 9 Not Bookmarked
10 Title 10 Not Bookmarked 

Answer 1

我用连接更新了你的 sql 查询。如果当前用户为该页面添加了书签，您现在应该能够得到一个 bool 值。

$pageData = 'SELECT
                    id,
                    postTitle,
                    IF( count(distinct postMarks.userId) > 0, true, false) as bookmarkByUser
             FROM 
                    postsLEFT JOIN postMarks ON posts.id=postMarks.postId AND userId =".$userId."
             GROUP BY
                    post.idLIMIT 10';