Into a MYSQL query, I am trying to insert a WHERE clause (the one that appears in the PHP comment below). Whenever I place this clause in the MYSQL query statement, it always fails (I've tried both before the LEFT JOIN and before the ORDER BY clauses).
Any ideas on how I can get my statement to work?
<?php
$result = mysql_query("SELECT *
FROM Items
LEFT JOIN Topics on Topics.TopicID = Items.FK_TopicID
ORDER BY TopicSort, TopicName ASC, ItemSort, ItemTitle");
/* WHERE FK_UserID=$_SESSION[user_id] */
$topicname = false;
while($row = mysql_fetch_array($result)) {
if (!$row['TopicID']) {
$row['TopicName'] = 'Sort Me';
}
if ($topicname != $row['TopicName']) {
e开发者_JS百科cho '<ul><li>' . $row['TopicName'] . '</li><ul>';
$topicname = $row['TopicName'];
}
echo '';
echo '<li>' . $row['ItemTitle'] . '</li>';
echo '';
}
if ($topicname != $row['TopicName']) {
echo '</ul>';
$topicname = $row['TopicName'];
}
?>
SQL stands for Structured Query Language -- you can't put a WHERE clause wherever you want in the query. The WHERE clause has to be after the FROM/JOIN clauses, and before the GROUP BY, HAVING, and ORDER BY clauses. IE:
SELECT *
FROM ITEMS i
LEFT JOIN TOPICS t ON t.TopicID = i.FK_TopicID
WHERE FK_UserID = $_SESSION[user_id]
ORDER BY TopicSort, TopicName ASC, ItemSort, ItemTitle
A better option, to insulate yourself from SQL injection attacks, is to use sprintf:
$sql = sprintf("SELECT *
FROM ITEMS i
LEFT JOIN TOPICS t ON t.TopicID = i.FK_TopicID
WHERE FK_UserID = %u
ORDER BY TopicSort, TopicName ASC, ItemSort, ItemTitle",
mysql_real_escape_string($_SESSION[user_id]));
$result = mysql_query($sql);
Put the WHERE clause before the ORDER BY clause. http://dev.mysql.com/doc/refman/5.0/en/join.html
"SELECT * FROM Items LEFT JOIN Topics on Topics.TopicID = Items.FK_TopicID WHERE whateverTableTheFK_UserIDColumnIs.FK_UserID='".$_SESSION['user_id']."' ORDER BY TopicSort, TopicName ASC, ItemSort, ItemTitle"
精彩评论