从早上开始,我一直在努力解决这个问题:)
我有两张桌子。table1=发布table2=用户。现在我需要从两个表中提取数据。搜索了如何使用INNER JOIN,找到了一些代码。我在phpmyadmin中运行查询,它运行得很好,这是我的sql查询
SELECT post.topic_id, post.category_id, post.topic_id, post.post_creator, post.post_date, post.post_content, users.username, users.gender, users.id
FROM post INNER JOIN users
ON post.post_creator=users.id
WHERE post.post_creator=users.id and post.topic_id=19
ORDER BY post.post_date DESC
但是当我在php中使用这个sql查询时,它会给我错误
您的SQL语法有错误;在第1行的"INNER JOIN users ON post.post_creator=users.id ORDER BY post.post_date ASC"附近,查看与MySQL服务器版本相对应的手册,以获得正确的语法
下面是我的php代码
<?php
include_once './forum_Scripts/connect_to_MySql.php';
$cid = $_GET['cid'];
if(isset($_SESSION['uid'])){
$logged = " | <a href ='create_topic.php?cid=".$cid."'>Click here to create a new topic</a> ";
}else{
$logged = " | Please log in to create topics in this forum.";
}
$tid = $_GET['tid'];
$sql = "SELECT * FROM topics WHERE category_id='".$cid."' AND id='".$tid."' LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 1){
echo "<table width='100%'> ";
if(isset($_SESSION['uid'])){
echo "<tr><td colspan='2'><input type='submit' value='Add Reply' onClick='"window.location = 'post_reply.php?cid=".$cid."&tid=".$tid."''" /> | <a href = 'http://amaforum.net63.net/'>Return to Forum Index</a><hr />";
}else{
echo "<tr><td colspan='2'><p>Please log in to add your reply</p><hr /></td>";}
while ($row = mysql_fetch_assoc($res)){
$sql2 = "SELECT post.topic_id, post.category_id, post.topic_id, post.post_creator, post.post_date, post.post_content, users.username, users.gender, users.id"
. "FROM post INNER JOIN users ON post.post_creator=users.id ORDER BY post.post_date ASC" ;
$res2 = mysql_query($sql2) or die(mysql_error());
while ($row2 = mysql_fetch_assoc($res2))
{
echo "<tr><td valign='top' style='border:2px solid #000000'><div style='min-height: 125px;'>".$row['topic_title']."<br />
by ".$row2['post_creator']." - ".$row2['post_date']."<hr />".$row2['post_content']."</div></td>"
. "<td width='200' valign='top' style='border: 1px solid #000000;'>"
. "<input id='".d_text."' type='".text."' name='".the_creator."' value='".$row2['post_creator']."' >"
. "echo '$user_info' "
. "</td></tr>"
. "<tr><td colspan='2'><hr /></td></tr> ";
}
$old_views = $row['topic_views'];
$new_views = $old_views + 1;
$sql3 = "UPDATE topics SET topic_views='".$new_views."' WHERE category_id='".$cid."' AND id='".$tid."' LIMIT 1 ";
$res3 = mysql_query($sql3) or die (mysql_error());
}
echo "</table>";
}else{
echo "<p>This topic does not exist</p>";
}
mysql_close();
?>
我能让它发挥作用,我真的需要你们的帮助。。
提前感谢
这应该可以解决您的问题(我刚刚添加了连接sql查询的两行时缺少的空间):
<?php
include_once './forum_Scripts/connect_to_MySql.php';
$cid = $_GET['cid'];
if(isset($_SESSION['uid'])){
$logged = " | <a href ='create_topic.php?cid=".$cid."'>Click here to create a new topic</a> ";
}else{
$logged = " | Please log in to create topics in this forum.";
}
$tid = $_GET['tid'];
$sql = "SELECT * FROM topics WHERE category_id='".$cid."' AND id='".$tid."' LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 1){
echo "<table width='100%'> ";
if(isset($_SESSION['uid'])){
echo "<tr><td colspan='2'><input type='submit' value='Add Reply' onClick='"window.location = 'post_reply.php?cid=".$cid."&tid=".$tid."''" /> | <a href = 'http://amaforum.net63.net/'>Return to Forum Index</a><hr />";
}else{
echo "<tr><td colspan='2'><p>Please log in to add your reply</p><hr /></td>";}
while ($row = mysql_fetch_assoc($res)){
$sql2 = "SELECT post.topic_id, post.category_id, post.topic_id, post.post_creator, post.post_date, post.post_content, users.username, users.gender, users.id "
. "FROM post INNER JOIN users ON post.post_creator=users.id ORDER BY post.post_date ASC" ;
$res2 = mysql_query($sql2) or die(mysql_error());
while ($row2 = mysql_fetch_assoc($res2))
{
echo "<tr><td valign='top' style='border:2px solid #000000'><div style='min-height: 125px;'>".$row['topic_title']."<br />
by ".$row2['post_creator']." - ".$row2['post_date']."<hr />".$row2['post_content']."</div></td>"
. "<td width='200' valign='top' style='border: 1px solid #000000;'>"
. "<input id='".d_text."' type='".text."' name='".the_creator."' value='".$row2['post_creator']."' >"
. "echo '$user_info' "
. "</td></tr>"
. "<tr><td colspan='2'><hr /></td></tr> ";
}
$old_views = $row['topic_views'];
$new_views = $old_views + 1;
$sql3 = "UPDATE topics SET topic_views='".$new_views."' WHERE category_id='".$cid."' AND id='".$tid."' LIMIT 1 ";
$res3 = mysql_query($sql3) or die (mysql_error());
}
echo "</table>";
}else{
echo "<p>This topic does not exist</p>";
}
mysql_close();
?>
为了避免将来出现这样的问题,最好将代码中正在执行的sql记录在某个地方,甚至在测试时将其回显到网页上。这样,您将非常快速地调试这样的问题。