php - 让 JOIN 工作的问题

Question

我有以下查询

$stmt2 = $con->prepare("SELECT `id`, `category_id`, `topic_id`, `post_creator`, `post_content`, `post_date` FROM forum_posts WHERE `category_id`=? AND `topic_id`=?");

我现在正在尝试加入我的用户表，以便我可以将 forum_posts.post_creator 与 users.id 匹配并从中获取用户名。

我正在尝试这个，但查询一直失败..

$stmt2 = $con->prepare("SELECT forum_posts.id, forum_posts.category_id, forum_posts.topic_id, forum_posts.post_creator, forum_posts.post_content, forum_postspost_date FROM forum_posts
        WHERE forum_posts.category_id=? AND forum_posts.topic_id=?
        INNER JOIN users
        ON forum_posts.post_creator = users.id");
       if ( !$stmt2 || $con->error ) {
        // Check Errors for prepare
         die('Stmt2 SELECT prepare() failed: ' . htmlspecialchars($con->error));
        }

            $stmt2->bind_param("ii", $cid, $tid);
            if(!$stmt2->execute()) {
                die('Stmt2 SELECT execute() failed: ' . htmlspecialchars($stmt2->error));
            }
            $stmt2->store_result();
            $stmt2->bind_result($post_id, $post_category_id, $post_topic_id, $post_creator, $post_content, $post_date, $posts_username);

我收到此错误，但无法弄清楚该部分有什么问题。

Stmt2 SELECT prepare() failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN users ON forum_posts.post_creator = users.id' at line 3

我做错了什么？

Answer 1

inner join 是 from 子句的一部分，而不是 where 子句的一部分:

SELECT forum_posts.id, forum_posts.category_id, forum_posts.topic_id, forum_posts.post_creator, forum_posts.post_content, forum_postspost_date 
FROM forum_posts
        INNER JOIN users
        ON forum_posts.post_creator = users.id        
WHERE forum_posts.category_id=? AND forum_posts.topic_id=?