查询与另一个表相关的信息

Question

I have two tables - Utilizadores and Competencias.

NOTE: Utilizadores = Users And Competencias = Skills

Each user can have 1 or more Skills.

I need to print them in a dropdown menu, where, for each skill, it shows the user that has that skill.

So, SKILLS->USER THAT HAS THE SKILL (The visual is all done, but it doesn't work properly - Explained next)

Here is the first query:

$query2 = mysql_query("SELECT DISTINCT competencias, id_user FROM ce");
$ut = array();
while ($result = mysql_fetch_array($query2)) $ut[] = $result;
foreach ($ut as $u){
    $id_user=$u[1];
}

(Nevermind the number 2 on the query2.) This query searches in the table "Skills" the id of the user that contains that specific skill. Again, A user can have more than just 1 skill.

Second Query:

$query3 = mysql_query("SELECT `id`, `nome` FROM utilizadores WHERE id='$id_user'");
$utt = array();
while ($result2 = mysql_fetch_array($query3)) $utt[] = $result2;
foreach ($utt as $utilizador){
    $nomeuser=$utilizador[1];
}

This Second Query searched on the table "Users" for the names of the users

Anf finaly, the third part:

foreach ($ut as $hu){
echo "<li class='has-sub'><a><span>" .$hu[0]. "</span></a>";
    echo "<ul><a href='php/curriculo.php?nomeut=$nomeuser'>". $nomeuser. "</a></ul>";
}

Now the problem I'm Having: When the mouse overs the skill present on the dropdown menu, it shows a sub-dropdown showing only the name of the user that contains the skill. But it always shows me the same name.

In the database, Several Skills belong to Several Users, but, on the sub-dropdown, it shows the name of ONE person only, instead of the ones corresponding to their skill.

Let's say:
John has a skill "Sharepoint"
Smith has a skill "Whatever"

When i pass the mouse on "Sharepoint" ->John
When i pass the mouse on "Whatever" -> John - When it really corresponds to "Smith".

I am sorry for the realy long problem, and for my awful english.

Any help is appreciated
Thanks :)

Answer 1

Since it isn't completely clear to me what info you want from your database, I'll describe you some options. You can process the results with you PHP code. If you need more, just comment and I'll update my answer.

Let say that you got your tables populated like this:

users
user_id | username
1       | John
2       | Matt
3       | Rob

skills
user_id | skill
1       | PHP
1       | SQL
1       | HTML
2       | PHP
4       | HTML

When using a left join, all the rows of the left table will be returned. If there is no match in the right table, it will return NULL. This query

SELECT u.username, s.skill 
FROM users AS u 
LEFT JOIN skills as s
ON u.user_id=s.user_id

Will return:

John    PHP
John    SQL
John    HTML
Matt    PHP
Rob     NULL

Using a right join, will return all the rows from the right table, giving a NULL if there is no match in the left table

SELECT u.username, s.skill
FROM users AS u
RIGHT JOIN skills AS s 
ON u.user_id = s.user_id

Will return:

John    PHP
John    SQL
John    HTML
Matt    PHP
NULL    HTML

Using the inner join, all rows that match in both tables will return. So this query

SELECT u.username, s.skill
FROM users AS u
RIGHT JOIN skills AS s 
ON u.user_id = s.user_id

will return

John    PHP
John    SQL
John    HTML
Matt    PHP