subquery

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

subquery

Post by sebs »

How does mysql treats subquerys?can I make something like:

Code: Select all

$sqlquery="SELECT adress from table where (ID=(SELECT ID from table1 where micro REGEXP '[[:<:]]".$name."[[:>:]]'))or($name='')"
$result=mysql_query($sqlquery);
while($data=mysql_fetch_array($result))
{...}
Because it gives me an error when I try this.The error is :
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result.
Clearly the sql query is not correct but why?
The regex is correct,tryed it elsewhere.Also tryed ID IN instead of ID=(select ...
Last edited by sebs on Fri Oct 28, 2005 6:38 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

are you sure your version of MySQL supports subqueries?


You really need to start emitting mysql_error() when an error happens while working with MySQL (at least during development)
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

With mysql_error gives this:
You have an error in your SQL syntax near 'select firme.ID from firme where (firme.micro REGEXP '[[:<:]]a' at line 1
I don't know what could mean.Yes I think my version of mysql supports subqueries.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what version of MySQL are you using?

Code: Select all

SELECT VERSION()
The error you are getting would suggest you are using a version below 4.1 which was when subquery support was added.
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

CLient Api version 3.23.58
Last edited by sebs on Fri Oct 28, 2005 6:43 am, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

That's the Client... not the Server...
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

The version I have is 3.23.58!Is there anyway I could change the subquery to integrated in the main query?
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

I am sure that this is the version.Thank you for taking time to consider my question.The only way is to change th version of mysql?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Ummm... not sure on your table structures but:

Code: Select all

select
    a.adress
from
    table as a,
    table1 as b
where
    a.id = b.id
    and b.micro regexp '[[:<:]]".$name."[[:>:]]'
    or $name = ''
Not sure on what the regexp is for so I didn't touch it. Also, are you using $name as a field name as well as a value?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it would appear you could use an INNER JOIN..
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

I will try what you told me!
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

try this

Code: Select all

$query = mysql_query("SELECT ID from table1 where micro REGEXP '[[:<:]]".$name."[[:>:]]'))or($name='')");
$IDs = array();
while($rs = mysql_fetch_row())
 $IDs[] = $rs[0];

$query = mysql_query("SELECT adress from table where ID IN (".join(',', $IDs).")");
//do whatever with $query result...
edit: oops, too late :( & yeah... use d11's version... that's much better
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

Inner join does not help me so much because it's too slow.I make the same number of comparison as if I the second table had it's fields in the first table.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

sebs wrote:Inner join does not help me so much because it's too slow.I make the same number of comparison as if I the second table had it's fields in the first table.
Inner Joins are generally fast, left joins can be slow but anyway... If you don't want to use a join then it probably won't add much in this instance to just use two queries, the first to get ID, the second uses that ID in the query.
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

Post by sebs »

Thanks guys,your great!It's working how you told me but it's not as fast as I wanted with a subquery.I will see what I will do be faster!
Post Reply