[RESOLVED] Limiting results from multiple table select

revez2002

Using the statement below, I am trying to limit the results so that if an item has already been added to the CHECKLIST_MANAGER table, it will not be selected as a record from the CHECKLIST_ITEMS table. However, when I execute the statement displayed, no search results are returned, only a message stating, "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /public_html/content/admin/add_checklist_item.php on line 84".

I have been searching the forums and mySQL's site, but I can't find anything that provides a clue as to what I am missing or overlooking.

revez2002

$sql = mysql_query("SELECT 
CHECKLIST_ITEMS.checklist_item_ID,
CHECKLIST_ITEMS.checklist_item_name,
CHECKLIST_ITEMS.created_by,
CHECKLIST_ITEMS.date_created
FROM CHECKLIST_ITEMS, CHECKLIST_MANAGER 
(WHERE CHECKLIST_MANAGER.checklist_name_ID = '$checklist_name_ID'
AND CHECKLIST_ITEMS.checklist_item_ID != CHECKLIST_MANAGER.checklist_item_ID)"); 
$sql_results = mysql_num_rows($sql);

toplay

After each MySQL call you should check for any possible errors. You're not and there's a syntax error in your query. You proceed to call mysql_num_rows() and that's why you get the message you get.

If you had error checking after the query you would get the real error that's causing the query to fail.

Anyway, it's probably this part: (WHERE

change that to: WHERE (

🙂

revez2002

Hi toplay,

Thank you very much for the insight. I made the change to the position of the opening parenthese, and this eliminated the error that I was receiving. I also added error checking to the statement, as per your recommendation:

...or die('SQL query failed because:'.mysql_error());

Nevertheless, I am still not obtaining the results that I am looking for. Instead of generating a result that should contain 3 records, a list of 63 records is produced, and all 10 of the records that are in the CHECKLIST_ITEMS table are displayed. I analyzed the records produced, and seven of these records appear on the list 6 times (in no particular order), and the other three are listed 7 times.

cornercuttinjoe

i had a similar query like this, and i think i know what you are wanting to say with it. the reason it is returning so many results is because of the CHECKLIST_ITEMS.checklist_item_ID != CHECKLIST_MANAGER.checklist_item_ID part. what mysql is doing here (i think, im new to it) is it will take one record and compare it to a different record, not necessarily check so that this record doesnt match all of the records. its looking at things individually, not as a whole.

if im right, you should look into the NOT IN syntax. this is a rough guess, and im not sure if it would work, but you could try something like this.

$sql = mysql_query("SELECT 
checklist_item_ID,
checklist_item_name,
created_by,
date_created
FROM CHECKLIST_ITEMS WHERE checklist_item_ID NOT IN  

(SELECT checklist_item_ID FROM CHECKLIST_MANAGER
WHERE checklist_name_ID = '$checklist_name_ID')"); 
$sql_results = mysql_num_rows($sql);

try that. hope it helps, although im not sure. i still kind of wince at the fact that im giving advice, considering i am very new to mysql, but i think this could help.

good luck.

revez2002

Thanks for the suggestions joe,

However, after making the chnages you suggested with the NOT IN statement and executing the script, I was still unable to get the proper result. I also tried a similar subquery using WHERE NOT EXISTS, but this too did not generate the proper result.

I guess I am heading back to the manual and forums to see what else I can find.

revez2002

After reviewing the documentation on NOT IN and NOT EXISTS, I have re-written the statement as follows:

$sql = mysql_query("SELECT 
CHECKLIST_ITEMS.checklist_item_ID, 
CHECKLIST_ITEMS.checklist_item_name, 
CHECKLIST_ITEMS.created_by, 
CHECKLIST_ITEMS.date_created 
FROM CHECKLIST_ITEMS 
WHERE CHECKLIST_ITEMS.checklist_item_ID NOT IN (
SELECT CHECKLIST_MANAGER.checklist_item_ID FROM CHECKLIST_MANAGER 
WHERE CHECKLIST_MANAGER.checklist_name_ID = '$checklist_name_ID')") or die("Error:".mysql_error()); 
$sql_results = mysql_num_rows($sql);

However, I still am not able to get the proper results, but instead I receive the following error, "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 'SELECT CHECKLIST_MANAGER.checklist_item_ID FROM CHECKLI...".

Apparently the NOT EXISTS subquery will not work with mySQL versions below 4.1x, and our server is running mySQL version 4.0.24. Therefore, I tried to make the query work using NOT IN.

revez2002

Roger_Ramjet

Try changing it to


 $sql = "SELECT 
CHECKLIST_ITEMS.checklist_item_ID,
CHECKLIST_ITEMS.checklist_item_name,
CHECKLIST_ITEMS.created_by,
CHECKLIST_ITEMS.date_created 
FROM CHECKLIST_ITEMS LEFT JOIN  CHECKLIST_MANAGER USING (checklist_item_ID) WHERE CHECKLIST_MANAGER.checklist_item_ID IS  NULL ";

This is exactly the situation that LEFT JOIN is ideal for Join Syntax. Make sure that the CHECKLIST_MANAGER.checklist_item_ID column is set to NOT NULL though.

revez2002

SUCCESS!!!

While reviewing some of the other postings in the Database forum here in phpBuilder, I came across a suggestion that included a LEFT JOIN and NULL in the SQL statement. Based on that suggestion, I modified my statement as follows:

$sql = mysql_query("SELECT 
CHECKLIST_ITEMS.checklist_item_ID, 
CHECKLIST_ITEMS.checklist_item_name, 
CHECKLIST_ITEMS.created_by, 
CHECKLIST_ITEMS.date_created 
FROM CHECKLIST_ITEMS 
LEFT JOIN CHECKLIST_MANAGER 
ON CHECKLIST_ITEMS.checklist_item_ID=CHECKLIST_MANAGER.checklist_item_ID 
WHERE CHECKLIST_MANAGER.checklist_item_ID IS NULL");
$sql_results = mysql_num_rows($sql);

When I executed the script that contained this statement, no errors occurred, and the information that was displayed was correct based on the information in the respective tables. Hope this helps someone else.

revez2002 🙂

Roger_Ramjet

Snap!