Counting mutual friends using LEFT JOIN

By the question I understood that you want all mutual friends between two users.

SELECT u.usuario, u.pnome, u.snome, u.foto, COUNT(c.id) as comum
FROM amizades a
INNER JOIN usuarios u
ON (u.usuario = a.amigo1 AND a.amigo1 <> '$userPagina') OR (u.usuario = a.amigo2 AND a.amigo2 <> '$userPagina')
LEFT JOIN (SELECT id FROM amizades WHERE (amigo1 = u.usuario AND amigo1 <> '$userLogado' AND estatus = 2) OR (amigo2 = u.usuario AND amigo2 <> '$userLogado' AND estatus = 2)) c ON (u.usuario = A.amigo1 OR u.usuario = A.amigo2)
WHERE (a.amigo1 = '$userPagina' OR a.amigo2 = '$userPagina') AND a.estatus = 2

Notice that within the sub-query that is located after the LEFT JOIN you are using u. usuario, which exists only outside of this query. This is called a correlation variable and in SQL you can only use it if you put the keyword sideways after the LEFT JOIN and before the subquery. This only happens if your sub-query is within the clause FROM.

Unfortunately MySQL does not yet implement the side keyword, so if you make a sub-query within the FROM clause you cannot use any correlation variables, i.e. no variables defined outside the sub-query itself. But since this does not apply to the WHERE clause, you can normally use a sub-query with correlation variable in this clause.

I will show a slightly different solution, easier to understand. As you already have the query that returns all the Friends of a person, just find all the Friends of the logged in user, all the Friends of the page user and see which ones are in the two tables. This would be very easy with the use of INTERSECT, however MySQL does not have this implementation. So I made an intersection of the two sets with an IN in the clause where:

SELECT COUNT(conta.usuario) FROM (
  SELECT u.usuario, u.pnome, u.snome, u.foto
  FROM amizades a
  INNER JOIN usuarios u
  ON (u.usuario = a.amigo1 AND a.amigo1 <> 'Alex') OR (u.usuario = a.amigo2 AND a.amigo2 <> 'Alex')
  WHERE (a.amigo1 = 'Alex' OR a.amigo2 = 'Alex') AND a.estatus = 2 AND (u.usuario, u.pnome, u.snome, u.foto) IN (
    SELECT u2.usuario, u2.pnome, u2.snome, u2.foto
    FROM amizades a2
    INNER JOIN usuarios u2
    ON (u2.usuario = a2.amigo1 AND a2.amigo1 <> 'Igor') OR (u2.usuario = a2.amigo2 AND a2.amigo2 <> 'Igor')
    WHERE (a2.amigo1 = 'Igor' OR a2.amigo2 = 'Igor') AND a2.estatus = 2
  )
) conta;

Notice that one query takes all of Alex's friends and the other takes all of Igor's friends. Then you Group in order to catch all the friends from Alex who are (IN) in Igor's friends table, getting all the friends in common. After that you use the COUNT to know how many friends there are in this table of users who are friends of Alex and Igor.

Here then is the query you want, featuring all of Alex's friends and how many mutual friends he has with you:

SELECT amc.usuario, amc.pnome, amc.snome, amc.foto, COUNT(a.id) as amigos_em_comum
FROM
(
  SELECT am.usuario as usuario, am.pnome, am.snome, am.foto, u.usuario as amigo
  FROM 
  (
    SELECT u.usuario as usuario, u.pnome as pnome, u.snome as snome, u.foto as foto
    FROM amizades a 
    INNER JOIN usuarios u 
    ON (u.usuario = a.amigo1 AND a.amigo1 <> 'Alex') OR (u.usuario = a.amigo2 AND a.amigo2 <> 'Alex')
    WHERE (a.amigo1 = 'Alex' OR a.amigo2 = 'Alex') AND a.estatus = 2
  ) as am, amizades as a, usuarios as u 
  WHERE (am.usuario = a.amigo1 OR am.usuario = a.amigo2) AND (u.usuario = a.amigo1 OR u.usuario = a.amigo2) AND am.usuario <> u.usuario AND u.usuario <> 'Igor'
  ORDER BY am.usuario
) as amc, amizades as a
WHERE (amc.amigo = a.amigo1 OR amc.amigo = a.amigo2) AND (a.amigo1 = 'Igor' OR a.amigo2 = 'Igor') AND amc.usuario <> 'Igor'
GROUP BY amc.usuario, amc.pnome, amc.snome, amc.foto
ORDER BY amc.usuario

Hope I helped!