About Subquery

[groover]

I want to do a subquery in php.

i say:

SELECT user FROM table1 WHERE user NOT IN ( SELECT user FROM table2 )

Mysql_error() says that i have an error in the subquery,
can anyone kindly tell me what does it happen?

Thanks!

[fenway]

First, what version of MySQL are you using? You need 4.1+. Second, what was the exact error message (I'll guess error in syntax).

[groover]

it is something like:

you have an error in "SELECT user FROM table2"......

[fenway]

But which version of MySQL are you using -- that error message is usually syntax-related, which means the parser has no idea what to do.

[groover]

It says:

#1064 - You have an error in your SQL syntax near 'select user from table2 ) LIMIT 0, 30 ' at line 1

And I just checked.
MySQL version is 3.23.58

This means I can never use subquery right?

[fenway]

Correct -- but you can (almost) always achieve the same result with a temporary table, or by storing the result of the previous query in PHP. For example, you could get all the users from table2, join the results with commas, and pass them into your table1 query and achieve the same effect.

Hope that helps.

[groover]

What do you mean by "join the results with commas"??

thanks again for your answer!

[fenway]

Since IN() takes a comma-separated list of values, you need to retrieve the first query's results as an array, and create such a list -- PHP has a function called join (or implode) that allows you to do just that.

[groover]

Well, I still don't fully understand your explanation.

Can you please give me some example of how I can do it in PHP please?

Like a bit of coding or things like that.

Thanks again

[fenway]

Here you go (untested):

[code]<?php
$result = mysql_query("SELECT DISTINCT user FROM table2");
$arr = array();
while( $row = mysql_fetch_row($result) ) { $arr[] = $row[0]; }
$users = implode( "','", $arr );

$result2 = mysql_query("SELECT user FROM table1 WHERE user NOT IN ($users)");
?> [/code]

Hope that helps.

[groover]

Really, thank you so much for solving my problem!