How to optimize this query

[php_guest]

I am not very experienced with MySql and I would like to optimized the below queries. Please could you tell me what method would you suggest (join, union or other?). Is possible to make for all that only 1 query?

 

TABLES:

friends

who | whom | acceptedstatus

 

companies

companyid | companyname | url | companylogo

 

employees

id | companyid | userid

 

I have a page where I need to display to user all companies where user friends are employed. I have now the below code, but I believe it could be better optimized:

 

//$selfid is user id from session
$result=mysql_query("SELECT whom FROM friends WHERE who='$selfid'");
while($row=mysql_fetch_assoc($result){
  $result2=mysql_query("SELECT companyid FROM employees WHERE userid='$row[whom]'");
  while($row2=mysql_fetch_assoc($result2){
    $result3=mysql_query("SELECT companyname, companylogo, url FROM companies WHERE companyid='$row2[companyid]'");
    $row3=mysql_fetch_assoc($result3);
    $companyname[]=$row3[companyname];
    $compnylogo[]=$row3[companylogo];
    $companyurl[]=$row3[url];
  }
}

[zeodragonzord]

Try this...

 

SELECT *.companies FROM friends
INNER JOIN employees ON friends.whom = employees.userid
INNER JOIN companies ON employees.companyid = companies.companyid;

[php_guest]

Thank you! I found also two another options. But now I am confused what is the best option.

a)

SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who='$selfid' AND userid=whom AND companies.companyid=employees.companyid 

b)

SELECT companyname,companylogo,url  
FROM companies 
WHERE companyid = (SELECT companyid  
   FROM employees 
   WHERE userid = (SELECT whom 
     FROM friends 
     WHERE who ='$selfid')

 

or c)

SELECT companyname, companylogo, url FROM companies
INNER JOIN employees ON employees.companyid =  companies.companuid -- which have employess
INNER JOIN friends ON friends.whom = employees AND friends.who='$selfid'

[ignace]

A and C are equal, B is possible but not really the optimized solution you are looking for.

[php_guest]

I have now another but similar problem. The situation is very similar but I need to get information from two tables. Is also for this any similar solution or do I need now to do actually a new query for each user?

 

table employees

id | userid | companyid | position

 

table users

userid | firstname | lastname | age | email | password ...

 

$employees=mysql_query("SELECT userid, [color=red]position[/color] FROM employees WHERE companyid='$companyid' LIMIT $paginationLimit ");
while($rowemployees=mysql_fetch_assoc($employees){
  $userid=$rowemployees[userid];
  $position[]=$rowemployees[position];
  $employeeDetails=mysql_query("SELECT * FROM users WHERE userid='$userid'");
  $rowEmployeeDetails=mysql_fetch_assoc($employeeDetails)
    $firstname[]=$rowEmployeeDetails[firstname];
    $lastname[]=$rowEmployeeDetails[lastname];
    $age[]=$rowEmployeeDetails[age];
    $position[]=$rowEmployeeDetails[position];
} 

 

Thanks again!

[Ken2k7]

Thank you! I found also two another options. But now I am confused what is the best option.

a)

SELECT companyname,companylogo,url FROM friends,employees,companies
WHERE who='$selfid' AND userid=whom AND companies.companyid=employees.companyid 

b)

SELECT companyname,companylogo,url  
FROM companies 
WHERE companyid = (SELECT companyid  
   FROM employees 
   WHERE userid = (SELECT whom 
     FROM friends 
     WHERE who ='$selfid')

 

or c)

SELECT companyname, companylogo, url FROM companies
INNER JOIN employees ON employees.companyid =  companies.companuid -- which have employess
INNER JOIN friends ON friends.whom = employees AND friends.who='$selfid'

C.

 

IIRC, A is slower than C. And B has syntax errors.

[fenway]

A and C are identical.