SQL query error!?!? HELP!!!!!!!!

[cricher]

I have written this code:
$sql = "SELECT SQL_CALC_FOUND_ROWS ".
"p.id, p.subject, p.body, p.date_posted, " .
"p.date_updated, u.name as author, u.id as author_id, " .
"u.signature as sig, c.count as postcount, " .
"p.forum_id as forum_id, f.forum_moderator as mod, " .
"p.update_id, u2.name as updated_by " .
"FROM forum_forum f " .
"JOIN forum_posts p " .
"ON f.id = p.forum_id " .
"JOIN forum_users u " .
"ON u.id = p.author_id " .
"LEFT JOIN forum_users u2 " .
"ON u2.id = p.update_id " .
"LEFT JOIN forum_postcount c " .
"ON u.id = c.user_id " .
"WHERE (p.topic_id = $topicid OR p.id = $topicid) " .
"ORDER BY p.topic_id, p.date_posted ".
"LIMIT $start,$limit";

and it comes up with this error:
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 'mod FROM forum_forum f LEFT JOIN forum_posts p ON f.id = p.forum_id AND ' at line 3

What is wrong with this. I have no ideas.
If you need any other info to solve this just ask.

[tsinka]

Hi,

the error message means that something is wrong just in front of the FROM keyworkd in the query. But the query you posted doesn't contain such an error. Please some more code (copy'n'paste the code) including the query execution itself.

Thomas

[cricher]

$sql = "SELECT SQL_CALC_FOUND_ROWS ".
"p.id, p.subject, p.body, p.date_posted, " .
"p.date_updated, u.name as author, u.id as author_id, " .
"u.signature as sig, c.count as postcount, " .
"p.forum_id as forum_id, f.forum_moderator as mod, " .
"p.update_id, u2.name as updated_by " .
"FROM forum_forum f " .
"JOIN forum_posts p " .
"ON f.id = p.forum_id " .
"JOIN forum_users u " .
"ON u.id = p.author_id " .
"LEFT JOIN forum_users u2 " .
"ON u2.id = p.update_id " .
"LEFT JOIN forum_postcount c " .
"ON u.id = c.user_id " .
"WHERE (p.topic_id = $topicid OR p.id = $topicid) " .
"ORDER BY p.topic_id, p.date_posted ".
"LIMIT $start,$limit";
$result = mysql_query($sql)
or die(mysql_error() . "<br>" . $sql);
$pagelinks = paginate($limit);
if (mysql_num_rows($result) == 0) {
$msg = "There are currently no posts. Would you " .
"like to be the first person to create a thread?";
$title = "No Posts...";
$dest = "compose.php?forumid=" . $forumid;
$sev = "Info";
$message = msgBox($msg,$title,$dest,$sev);
echo $message;

I think this is it.

[Houdini]

Look at your error right here in red

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 'mod FROM

mod or MOD is a reserved word in MySQL see RESERVED WORDS

as mod, " .

try

as moderator, " .

[tsinka]

There's a difference between the code you posted and the error message:

FROM forum_forum f LEFT JOIN forum_posts p

In the code you posted it is

FROM forum_forum f JOIN forum_posts p

Looks like the script causing that error message differs from the script that you posted.

Thomas

[cricher]

Here is the whole code. i couldnt find anything else. before you ask the code is for a bulletin board. everything else works fine except this file.

<?php
function trimBody($theText, $lmt=100, $s_chr="@@@", $s_cnt=1) {
$pos = 0;
$trimmed = FALSE;
for ($i = 1; $i <= $s_cnt; $i++) {
if ($tmp = strpos($theText, $s_chr, $pos)) {
$pos = $tmp;
$trimmed = TRUE;
} else {
$pos = strlen($theText);
$trimmed = FALSE;
break;
}
}
$theText = substr($theText, 0, $pos);

if (strlen($theText) > $lmt) {
$theText = substr($theText, 0, $lmt);
$theText = substr($theText, 0, strrpos($theText,' '));
$trimmed = TRUE;
}
if ($trimmed) $theText .= '...';
return $theText;
}

function msgBox($m, $t, $d="index.php", $s="Info") {
$theMsg = "<div id=\"requestConfirm" . $s . "\">";
$theMsg .= "<h2>" . $t . "</h2>\n";
$theMsg .= "<p>" . $m . "</p>";
$theMsg .= "<p><a href=\"" . $d . "\" ";
$theMsg .= "class=\"buttonlink\">";
$theMsg .= "Yes</a>";
$theMsg .= "<a href=\"index.php\" class=\"buttonlink\">";
$theMsg .= "No</a></p>";
$theMsg .= "</div>";
return $theMsg;
}

function getForum($id) {
$sql = "SELECT forum_name as name, forum_desc as description, " .
"forum_moderator as mod ".
"FROM forum_forum ".
"WHERE id = " . $id;
$result = mysql_query($sql)
or die(mysql_error() . "<br>" . $sql);
$row = mysql_fetch_array($result);
return $row;
}

function getForumID($topicid) {
$sql = "SELECT forum_id FROM forum_posts WHERE id=$topicid";
$result = mysql_query($sql)
or die(mysql_error() . "<br>" . $sql);
$row = mysql_fetch_array($result);
return $row['forum_id'];
}

function breadcrumb($id, $getfrom="F") {
$sep = "<span class=\"bcsep\">";
$sep .= " &middot; ";
$sep .= "</span>";
if ($getfrom == "P") {
$sql = "SELECT forum_id, subject FROM forum_posts " .
"WHERE id = " . $id;
$result = mysql_query($sql)
or die(mysql_error() . "<br>" . $sql);
$row = mysql_fetch_array($result);
$id = $row['forum_id'];
$topic = $row['subject'];
}
$row = getForum($id);
$bc = "<a href=\"index.php\">Home</a>$sep";
switch ($getfrom) {
case "P":
$bc .= "<a href=\"viewforum.php?f=$id\">".$row['name'] .
"</a>$sep" . $topic;
break;

case "F":
  $bc .= $row['name'];
  break;

}
return "<h4 class=\"breadcrumb\">" . $bc . "</h4>";
}

function showTopic($topicid, $showfull=TRUE) {
global $conn;
global $userid;
global $limit;

echo breadcrumb($topicid, "P");
if (isset($GET['page'])) {
$page = $GET['page'];
} else {
$page = 1;
}
if ($limit == "") $limit = 25;
$start = ($page - 1) * $limit;
if (isset($SESSION['user_id'])) {
echo topicReplyBar($topicid, getForumID($topicid), "right");
}
$sql = "SELECT SQL_CALC_FOUND_ROWS ".
"p.id, p.subject, p.body, p.date_posted, " .
"p.date_updated, u.name as author, u.id as author_id, " .
"u.signature as sig, c.count as postcount, " .
"p.forum_id as forum_id, f.forum_moderator as mod, " .
"p.update_id, u2.name as updated_by " .
"FROM forum_forum f " .
"JOIN forum_posts p " .
"ON f.id = p.forum_id " .
"JOIN forum_users u " .
"ON u.id = p.author_id " .
"LEFT JOIN forum_users u2 " .
"ON u2.id = p.update_id " .
"LEFT JOIN forum_postcount c " .
"ON u.id = c.user_id " .
"WHERE (p.topic_id = $topicid OR p.id = $topicid) " .
"ORDER BY p.topic_id, p.date_posted ".
"LIMIT $start,$limit";
$result = mysql_query($sql)
or die(mysql_error() . "<br>" . $sql);
$pagelinks = paginate($limit);
if (mysql_num_rows($result) == 0) {
$msg = "There are currently no posts. Would you " .
"like to be the first person to create a thread?";
$title = "No Posts...";
$dest = "compose.php?forumid=" . $forumid;
$sev = "Info";
$message = msgBox($msg,$title,$dest,$sev);
echo $message;
} else {
echo "<table class=\"forumtable\" cellspacing=\"0\" ";
echo "cellpadding=\"2\"><tr>";
echo "<th class=\"author\">Author</th>";
echo "<th class=\"post\">Post</th>";
echo "</tr>";
$rowclass = "";
while ($row = mysql_fetch_array($result)) {
$lastupdate = "";
$editlink = "";
$dellink = "";
$replylink = "&nbsp;";
$pcount = "";
$pdate = "";
$sig = "";
if ($showfull) {
$body = $row['body'];
if (isset($SESSION['user_id'])) {
$replylink = "<a href=\"compose.php?forumid=" .
$row['forum_id'] . "&topicid=$topicid&reid=" . $row['id'] .
"\" class=\"buttonlink\">REPLY</a>&nbsp;";
} else {
$replylink = "";
}
if ($row['update_id'] > 0) {
$lastupdate = "<p class=\"smallNote\">Last updated: " .
$row['date_updated'] . " by " .
$row['updated_by'] . "</p>";
}
if (($userid == $row['author_id']) or
($userid == $row['mod']) or
($SESSION['access_lvl'] > 2)) {
$editlink = "<a href=\"compose.php?a=edit&post=".$row['id'].
"\" class=\"buttonlink\">EDIT</a>&nbsp;";
$dellink = "<a href=\"transact-affirm.php?action=deletepost&".
"id=" . $row['id'] .
"\" class=\"buttonlink\">DELETE</a>&nbsp;";
}
$pcount = "<br><span class=\"textsmall\">Posts: " .
($row['postcount']==""?"0":$row['postcount']) . "</span>";
$pdate = $row['date_posted'];
$sig = ($row['sig'] != ""?"<p class=\"sig\">".
bbcode(nl2br($row['sig'])):"")."</p>";
} else {
$body = trimBody($body);
}
$rowclass = ($rowclass == "row1"?"row2":"row1");
echo "<tr class=\"$rowclass\">";
echo "<td class=\"author\">" . $row['author'];
echo $pcount;
echo "</td><td class=\"post\"><p>";
if (isset($SESSION['user_id'])
and ($SESSION['last_login'] < $row['date_posted'])) {
echo NEWPOST . " ";
}
if (isset($GET['page'])) {
$pagelink = "&page=" . $_GET['page'];
} else {
$pagelink = "";
}
echo "<a name=\"post" . $row['id'] .
"\" href=\"viewtopic.php?t=" . $topicid .$pagelink ."#post".
$row['id'] . "\">".POSTLINK."</a>";
if (isset($row['subject'])) {
echo " <strong>" . $row['subject'] . "</strong>";
}
echo "</p><p>" . bbcode(nl2br(htmlspecialchars($body))) . "</p>";
echo $sig;
echo $lastupdate;
echo "</td></tr>";
echo "<tr class=\"$rowclass\"><td class=\"authorfooter\">";
echo $pdate . "</td><td class=\"threadfooter\">";
echo $replylink;
echo $editlink;
echo $dellink;
echo "</td></tr>\n";
}
echo "</table>";
echo $pagelinks;
echo "<p>".NEWPOST." = New Post&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
echo POSTLINK." = Post link (use to bookmark)</p>";
}
}

function isParent($page) {
$currentpage = $_SERVER['PHP_SELF'];
if (strpos($currentpage, $page) === false) {
return FALSE;
} else {
return TRUE;
}
}

function topicReplyBar($topicid,$forumid,$pos="right") {
$html = "<p class=\"buttonBar" . $pos . "\">";
if ($topicid > 0) {
$html .= "<a href=\"compose.php?forumid=$forumid" .
"&topicid=$topicid&reid=$topicid\" " .
"class=\"buttonlink\">Reply to Thread</a>";
}
if ($forumid > 0) {
$html .= "<a href=\"compose.php?forumid=$forumid\" " .
"class=\"buttonlink\">New Thread</a>";
}
$html .= "</p>";
return $html;
}

function userOptionList($level) {
$sql = "SELECT id, name, access_lvl " .
"FROM forum_users " .
"WHERE access_lvl=" . $level . " " .
"ORDER BY name";
$result = mysql_query($sql)
or die(mysql_error());

continued

[cricher]

continued

while ($row = mysql_fetch_array($result)) {
echo "<option value=\"". $row['id'] . "\">" .
htmlspecialchars($row['name']) . "</option>";
}
}

function paginate($limit=10) {
global $admin;

$sql = "SELECT FOUND_ROWS();";
$result = mysql_query($sql)
or die(mysql_error());
$row = mysql_fetch_array($result);
$numrows = $row[0];
$pagelinks = "<div class=\"pagelinks\">";
if ($numrows > $limit) {
if(isset($GET['page'])){
$page = $GET['page'];
} else {
$page = 1;
}
$currpage = $SERVER['PHP_SELF'] . "?" . $SERVER['QUERY_STRING'];
$currpage = str_replace("&page=".$page,"",$currpage);

if($page == 1){
  $pagelinks .= "<span class=\"pageprevdead\">&lt; PREV</span>";
}else{
  $pageprev = $page - 1;
  $pagelinks .= "<a class=\"pageprevlink\" href=\"" . $currpage .
                "&page=" . $pageprev . "\">&lt; PREV</a>";
}

$numofpages = ceil($numrows / $limit);
$range = $admin['pageRange']['value'];
if ($range == "" or $range == 0) $range = 7;
$lrange = max(1,$page-(($range-1)/2));
$rrange = min($numofpages,$page+(($range-1)/2));
if (($rrange - $lrange) < ($range - 1)) {
  if ($lrange == 1) {
    $rrange = min($lrange + ($range-1), $numofpages);
  } else {
    $lrange = max($rrange - ($range-1), 0);
  }
}

if ($lrange > 1) {
  $pagelinks .= "..";
} else {
  $pagelinks .= "&nbsp;&nbsp;";
}
for($i = 1; $i <= $numofpages; $i++){
  if ($i == $page) {
    $pagelinks .= "<span class=\"pagenumdead\">$i</span>";
  } else {
    if ($lrange <= $i and $i <= $rrange) {
      $pagelinks .= "<a class=\"pagenumlink\" " .
                    "href=\"" . $currpage . "&page=" . $i . 
                    "\">" . $i . "</a>";
    }
  }
}
if ($rrange < $numofpages) {
  $pagelinks .= "..";
} else {
  $pagelinks .= "&nbsp;&nbsp;";
}

if(($numrows - ($limit * $page)) > 0){
  $pagenext = $page + 1;
  $pagelinks .= "<a class=\"pagenextlink\" href=\"" . $currpage .
                "&page=" . $pagenext . "\">NEXT &gt;</a>";
} else {
  $pagelinks .= "<span class=\"pagenextdead\">NEXT &gt;</span>";
}

} else {
$pagelinks .= "<span class=\"pageprevdead\">&lt; " .
"PREV</span>&nbsp;&nbsp;";
$pagelinks .= "<span class=\"pagenextdead\"> " .
"NEXT &gt;</span>&nbsp;&nbsp;";
}
$pagelinks .= "</div>";
return $pagelinks;
}

function bbcode($data) {
$sql = "SELECT * FROM forum_bbcode";
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
$bbcode['tpl'][] =
"/" . html_entity_decode($row['template'],ENT_QUOTES). "/i";
$bbcode['rep'][] =
html_entity_decode($row['replacement'],ENT_QUOTES);
}
$data1 = preg_replace($bbcode['tpl'],$bbcode['rep'],$data);
$count = 1;
while (($data1 != $data) and ($count < 4)) {
$count++;
$data = $data1;
$data1 = preg_replace($bbcode['tpl'],$bbcode['rep'],$data);
}
}

return $data;
}
?>

Thats it

[cricher]

the rest of the code is sound and works.

[Houdini]

The line that is causeing the problem is still right here

"p.forum_id as forum_id, f.forum_moderator as mod, " .

you can not use a reserved MySQL word. If you look at this link MySQL RESERVED WORDS you will see that MOD is one of them (even if it is lowercase) change that word to something else and your error will disappear (at least that one).

Also when posting code in the forums it helps to use the BB Code that is available, reading you code hurts my eyes and makes it hard to find errors, highlighted code posted in these forums properly will reveal errors with a glance. To know how to use these codes click the link in my signature about how to use BB Codes and then you can get better results posting in the forums here.

[cricher]

<?
echo "thanx";
echo "is this what you mean";
?>

[Houdini]

Yep

[cricher]

Thanx by the way that last quote helped loads, i changed all the 'mod' in that script to 'edittor', but this still didnt work. so i then checked everything else. then i saw it in the index.php file. i was including the file that i thought was to blaim in the index.php file. so it is all running suburbly. but thank you for all your help. it is working excellently now. 😃

[Houdini]

Glad to hear it, now you can watch the soccer game I suppose if you want.