optimizing query

[jansky]

hi all sql expert

first of all im using mysql4 so im limited on using stored procedure

is there a way in mysql4 using loop statement

currently i have this kind of sql statement

$sql1 = 'select id, projectid from skills where active='Y'';
$data= executesql($sql1);
for($x =0; $x< count($data); $x++)
{
$sql2 = 'select name from skills2 where id = ' $data['projectid'] .' limit 10');
$data2 = exeutesql($sql2);
 for ($y=0; $y<count($data2);$y++)
{
    $pr = $data2[name] .',';
}
}

is there a way to optimze the code using join statement

thanks

[cahva]

Well something like this should do:

SELECT s.id, s.projectid, s2.name from skills s LEFT JOIN skills2 s2 ON s.projectid = s2.id WHERE s.active='Y';

In second query you had LIMIT 10 in the end. Why? It should only return one row as you use id in the where clause.

BTW, dont use count() function straight in for loop as it will count in every iteration. Its better to first assign the count to a variable and use the variable in the for loop. eg.

$cnt = count($data);
for($x =0; $x < $cnt; $x++)

[jansky]

i use the limit 10 because I need to have this data someting like this

userA - expertise: mysql,php,postgres,datab2
useB - expertise: c, pascal, c#
userC expertise: cooking, basketball

something like that

[cahva]

Yeah..but id is usually identifying field in the table so its unique. If its not unique in that table, you are doing things differently from the rest of us 🙂

[jansky]

sorry, the query that i posted is just a representation of the query that im working on and did not explain very well

anyway

from this query

$sql2 = 'select name from skills2 where id = ' $data['projectid'] .' limit 10'); 

SHOULD BE

$sql2 = 'select name from skills2 where skills_id = ' $data['projectid'] .' limit 10'); 

so basically it is a foreign key not unique key

there are other query on top of that with same algorithm and because i have a data of more than 200thou that .. (the whole script is just bunch of sql with almost same algorithm) ..