我很难弄清楚如何从不同的数据库表连接两个表和SELECT不同的行。
我想添加我的users表以匹配id与post_creator，然后从users表中获得username，其中id和post_creator数字匹配。
如果我的users表的id是10那是Jim的用户名。如果post_creator的值是10，我希望找到Jim并回显出来。
现在这是杀死代码，我不知道我做错了什么。
while ($row = $stmt->fetch()) {
//added in topic title variable
$topic_title;
// foreach($stmt as $row) {
//Prepared SELECT stmt to get forum posts
$stmt2 = $con->prepare("SELECT forum_posts.id, forum_posts.category_id, forum_posts.topic_id, forum_posts.post_creator, forum_posts.post_content, forum_posts.post_date users.id
FROM forum_posts LEFT JOIN users WHERE forum_posts.category_id=? AND forum_posts.topic_id=? AND forum_posts.post_creator=? AND users.id=?");
if($stmt2===false) {
die();
} else {
//var_dump($stmt2);
$stmt2->bind_param("ii", $cid, $tid);
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($post_id, $post_category_id, $post_topic_id, $post_creator, $post_content, $post_date);
if (!$stmt2) {
throw new Exception($con->error);
}
}
$num_rows2 = $stmt2->num_rows;
if($num_rows2) {
$count2=0;
while($stmt2->fetch()) {
$count2++;
$post_id;
$post_category_id;
$post_topic_id;
$post_creator;
$post_content;
$post_date = fixDate($post_date);
//$post_date;
echo "<tr><td valign='top' style='border: 1px solid #000000;'>
<div style='min-height: 125px;'>".$topic_title. "Post ".$count2."<br />
by ".$post_creator." - " .$post_date. "<hr />" . $post_content ."</div></td>
<td width='200' valign='top' align='center' style='border: 1px solid #000000;'>User Info Here!</td></tr>
<tr><td colspan='2'><hr /></td></tr>";
}
您可以连接表…例如(连接用户、类别和主题)
SELECT
forum_posts.id post_id,
forum_posts.category_id post_category_id,
forum_posts.topic_id post_topic_id,
forum_posts.post_creator,
forum_posts.post_content,
forum_posts.post_date,
forum_post_categories.category_name,
forum_post_topics.topic_name,
users.id user_id,
users.username user_username
FROM
forum_posts
LEFT JOIN
users
ON users.id = forum_posts.post_creator
RIGHT JOIN
forum_post_categories
ON forum_post_categories.id = forum_posts.category_id
RIGHT JOIN
forum_post_topics
ON forum_post_topics.id = forum_posts.topic_id
WHERE
forum_posts.category_id=? AND
forum_posts.topic_id=? AND
forum_posts.post_creator=? AND
users.id=?
select u.*,fp.* from users u inner join forum_posts fp u.id=fp.post_creator
where fp.category_id=? AND fp.topic_id=? AND fp.post_creator=? AND u.id=?
更新查询:
select u.user_name,fp.post_creator from
users u inner join forum_posts fp u.id=fp.post_creator
检查这个更新后的，它会给你user_name和post_creator
尝试删除此代码部分
if($stmt2===false) {
die();
} else { }
只是做:
$stmt2 = $con->prepare("SELECT forum_posts.id, forum_posts.category_id, forum_posts.topic_id, forum_posts.post_creator, forum_posts.post_content, forum_posts.post_date users.id
FROM forum_posts LEFT JOIN users WHERE forum_posts.category_id=? AND forum_posts.topic_id=? AND forum_posts.post_creator=? AND users.id=?");
$stmt2->bind_param("ii", $cid, $tid);
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($post_id, $post_category_id, $post_topic_id, $post_creator, $post_content, $post_date);
if (!$stmt2) {
throw new Exception($con->error);
}