Jump to content


Photo

About Subquery


  • Please log in to reply
10 replies to this topic

#1 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 February 2006 - 09:34 AM

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!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 February 2006 - 05:49 PM

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).
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 04 February 2006 - 10:15 AM

it is something like:

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 February 2006 - 07:33 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 February 2006 - 08:06 AM

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?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 February 2006 - 09:18 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 February 2006 - 04:15 PM

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

thanks again for your answer!

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 February 2006 - 06:05 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 06 February 2006 - 09:58 AM

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

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 February 2006 - 07:48 PM

Here you go (untested):

<?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)");
?>

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 groover

groover
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 07 February 2006 - 07:00 AM

Really, thank you so much for solving my problem!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users