finding records not in a category
Posted: Fri Jul 16, 2010 4:45 pm
hi, i'm trying to find jokes not listed in any category, the mysql statement :
works in mysql but running it from my php script below doesnt. i'm perplexed. from the code below, i keep getting this error message:
--------------------------------------------
Error retrieving jokes from database!
Error: Not unique table/alias: 'jokecategory'
or i receive:
Error retrieving jokes from database!
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 'LEFT JOIN jokecategory ON joke.id = jokecategory.categoryid WHERE jokecategory.' at line 1
----------------------------------------
i have been struggling with this so any help would be appreciated. thx.
Code: Select all
SELECT joke.ID, jokecategory.Jokeid, joke.joketext
FROM joke LEFT JOIN jokecategory ON joke.id = jokecategory.categoryid
WHERE jokecategory.jokeid Is Null;--------------------------------------------
Error retrieving jokes from database!
Error: Not unique table/alias: 'jokecategory'
or i receive:
Error retrieving jokes from database!
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 'LEFT JOIN jokecategory ON joke.id = jokecategory.categoryid WHERE jokecategory.' at line 1
----------------------------------------
i have been struggling with this so any help would be appreciated. thx.
Code: Select all
<?php
$dbcnx = @mysql_connect('localhost', 'root', 'mypass');
if (!$dbcnx) {
exit('<p>Unable to connect to the ' .
'database server at this time.</p>');
}
if (!@mysql_select_db('ijdb')) {
exit('<p>Unable to locate the joke ' .
'database at this time.</p>');
}
// The basic SELECT statement
$select = 'SELECT DISTINCT id, joketext';
$from = ' FROM joke';
$where = ' WHERE 1=1';
$aid = $_POST['aid'];
if ($aid != '') { // An author is selected
$where .= " AND authorid='$aid'";
}
$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
$from .= ', jokecategory';
$where .= " AND id=jokeid AND categoryid='$cid'";
}
$cid = $_POST['cid'];
if ($cid = 'None') { // the word 'none' is selected from the $cid dropdownlist - no category is selected
$from .= ', jokecategory';
$where .= " LEFT JOIN jokecategory ON joke.id = jokecategory.categoryid
WHERE jokecategory.jokeid Is Null";
}
$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
$where .= " AND joketext LIKE '%$searchtext%'";
}
?>
<table>
<tr><th>Joke Text</th><th>Options</th></tr>
<?php
$jokes = @mysql_query($select . $from . $where);
if (!$jokes) {
echo '</table>';
exit('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($joke = mysql_fetch_array($jokes)) {
echo "<tr valign='top'>\n";
$id = $joke['id'];
$joketext = htmlspecialchars($joke['joketext']);
echo "<td>$joketext</td>\n";
echo "<td><a href='editjoke.php?id=$id'>Edit</a> | " .
"<a href='deletejoke.php?id=$id'>Delete</a></td>\n";
echo "</tr>\n";
}
?>