Jump to content

Archived

This topic is now archived and is closed to further replies.

groover

About Subquery

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.