Page 1 of 1

subquery

Posted: Fri Oct 28, 2005 5:13 am
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 ...

Posted: Fri Oct 28, 2005 5:38 am
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)

Posted: Fri Oct 28, 2005 6:32 am
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.

Posted: Fri Oct 28, 2005 6:38 am
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.

Posted: Fri Oct 28, 2005 6:40 am
by sebs
CLient Api version 3.23.58

Posted: Fri Oct 28, 2005 6:43 am
by feyd
That's the Client... not the Server...

Posted: Fri Oct 28, 2005 6:50 am
by sebs
The version I have is 3.23.58!Is there anyway I could change the subquery to integrated in the main query?

Posted: Fri Oct 28, 2005 6:55 am
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?

Posted: Fri Oct 28, 2005 7:01 am
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?

Posted: Fri Oct 28, 2005 7:03 am
by feyd
it would appear you could use an INNER JOIN..

Posted: Fri Oct 28, 2005 7:05 am
by sebs
I will try what you told me!

Posted: Fri Oct 28, 2005 7:11 am
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

Posted: Fri Oct 28, 2005 7:18 am
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.

Posted: Fri Oct 28, 2005 7:45 am
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.

Posted: Fri Oct 28, 2005 7:48 am
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!