finding records not in a category

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

finding records not in a category

Post by cjkeane »

hi, i'm trying to find jokes not listed in any category, the mysql statement :

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;
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

<?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";
}
?>
Last edited by Benjamin on Fri Jul 16, 2010 6:20 pm, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: finding records not in a category

Post by liljester »

out of curiosity, have you printed the query out that is actually being sent to mysql_query (not what it should be, but whats actually in the vars)?
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: finding records not in a category

Post by cjkeane »

i havent printed out the vars. i did trythe sql statement in a mysql command prompt and it does work, but not sure why it doesnt in my php coding in the file i posted.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: finding records not in a category

Post by liljester »

i guess what i mean is what do you get when you do this:

Code: Select all

print $select . $from . $where;
make sure mysql is getting the query you think it is.
Post Reply