[RESOLVED] isolating all values from a query loop

ali_p

Hi, I am trying to use the results of one query to determine how my second query is run. Basically i would like to say select * from table b where is is not equal to any of the results from table a. Table a and table b sharing a particular code that i don't not want duplicated in my display.

My problem is breaking up the results from table a and being able to compare the individual codes with the new query. I can bring them out of table a and present them all as one ( using a while loop )but I'm not too sure how i can do the comparison. Any suggestions or pointers will be greatly appreciated.

Thanks

laserlight

You may be able to use a subquery or a join instead, but it depends on the details of what you want to select. For example:

SELECT * FROM b WHERE column NOT IN (SELECT column FROM a);

ali_p

Thanks. I tried sub querying it before and I got a lost in loops and it was repeating the data.
I was hoping this would be an easier way to get a result.

ali_p

Hi,

I gave the sub query another go , but its not quite working out for me still.
Below is my query;


$q3 = "SELECT prog_name, prog_code 
	  FROM programmes 
	  WHERE prog_code 
	  NOT IN (SELECT prog_code FROM contactxprogrammes WHERE contact_code = 5000)	   

	  "; 
   $rslt3 = mysql_query($q3); 

 while($row3 = mysql_fetch_array($rslt3)){ 

  echo "<p>";
  echo "<input type=\"checkbox\" name=\"programmes[]\" value=\"".$row3['prog_code']."\">".$row3['prog_name'].""; 
  echo "</p>";
  }

I'm just getting a mysql warning.

laserlight

What is the error message and how does it not work?

ali_p

Hi,
Ok, the error was because there was no '' around my sub query. This has now been changed to ;


  $q3 = "SELECT prog_name, prog_code 
	  FROM programmes 
	  WHERE parent_prog = 'N/A' 
	  AND prog_code
	  NOT IN ('SELECT prog_code FROM contactxprogrammes WHERE contact_code = 3001')	
	  ORDER BY prog_name    

	  "; 
   $rslt3 = mysql_query($q3);   

 while($row3 = mysql_fetch_array($rslt3)){ 

  echo "<p>";
  echo "<input type=\"checkbox\" name=\"programmes[]\" value=\"".$row3['prog_code']."\">".$row3['prog_name'].""; 
  echo "</p>";
  }

However the query is not working as all entries are being pulled out of the first table , the sub query is being ignored.

laserlight

Eh, you should not enclose the subquery in quotes. It is a query, not a string.

What version of MySQL are you using?

ali_p

When I remove the '' this is the warning that I get;

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

Thanks

leatherback

Ideally you check the individual queries seperately first; so try the subquery, and verify that it is correct. Then run the main query -wouthout the subquery- and make sure that runs. Then combine them.

the error indicates that the query you are using is incorrect.

Note that -afaik- subqueries are a recent feature so older versions of MySQL will not support them

ali_p

I'm using Mysql version 5.0.45

The query runs perfectly without the sub query and I know that there should be results. I just can't figure why its not working.

leatherback

odd...

If both queries run fine, then they should work fine together. All I can see that looks odd are the quotes around the subquery, which I presume you removed upon lasers suggestion, and this table name which looks odd: contactxprogrammes

ali_p

Yeah I have removed the quotes and this is where i get the mysql warning. With the quotes i get only the first query run.

Both queries will run fine independently, so thats why I'm unsure of the NOT IN piece of sql.

laserlight

Both queries will run fine independently, so thats why I'm unsure of the NOT IN piece of sql.

It is correct. Again, what is the warning?

ali_p

Hi, No it is still not working. Again, this is the warning that i get;

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

As i said both queries will run and produce results independently.

Thanks

leatherback

What is the error the mysql_query is producing.

Run like this:
$rslt3 = mysql_query($q3) or die(mysql_error());

ali_p

Hi, I have implemented change and the error I'm getting is ;

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 prog_code FROM contactxprogrammes WHERE contact_code = 3

I have double checked my code and table field names etc but I'm still unsure of what's causing this error. Its quite baffling now.

ali_p

Hi, I have uploaded my file on to my live server and now i am getting another sql error, which is this;

Operand should contain 1 column(s)

ali_p

Hi ,

This appears to be working for me now. There must have been some incompatibility issues with my local machine causing it to error out. Works perfectly on the live server.

Thank you both for all your help, I really appreciate it.