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?
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!