I have this allocation table:
PROJECT_ID | NAME_ID
1 1
1 2
2 1
2 2
My php Code looks as following:
<html>
<body>
<?php
mysql_connect("xxx","xxx","xxx")
or die ("Connection failed");
mysql_select_db("xxx")
or die ("Connection failed");
$res = mysql_query("SELECT PROJECT_ID FROM Projects_Names WHERE NAME_ID=1 AND 2");
$num = mysql_num_rows($res);
echo "$num Projects found<br />";
while($dsatz = mysql_fetch_assoc($res))
{
echo $dsatz["PROJECT_ID"] . "<br />";
}
?>
This will then give me the following output:
2 Projects found
1
2
So far so good. But what I basically want is to find out the names of the projects where employee 1 and employee 2 are both involved in. I have two more tables. A Projects table with the project Name, ID etc... and a Employee table with Name, ID etc...
I basically now want to say:
"look in the Projects table for the IDs that were just given as output (1,2) and give me the names of these projects"
I hope someone will give me some input on how to solve this, I just started sql + php yesterday, so theres much to learn :)
That's wrong syntax. You should either write it separately like this:
$res = mysqli_query($con, "SELECT PROJECT_ID FROM Projects_Names WHERE NAME_ID=1 AND NAME_ID = 2");
or use in
$res = mysqli_query($con, "SELECT PROJECT_ID FROM Projects_Names WHERE NAME_ID in (1,2)");
Your best bet is to use a combination of GROUP BY with COUNT and HAVING to get what you want
SELECT project_id, COUNT(DISTINCT name_id) AS `name_count`
FROM projects_names
WHERE name_id IN (?,?,...)
GROUP BY project_id
HAVING `name_count` = n
Here you would list all name_id values you were interested in and n would equal the number of name_id values search for. So if you are looking for name_id 1 and 2, you would use n = 2. This means find all projects where those to id are working, but there could be additional name_id's involved as well (those wouldn't be included in the count as they were filtered out).