Getting data from sql database and then use it for select query via php

Question

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 :)

$sql = "SELECT PROJECT_ID FROM Projects_Names WHERE NAME_ID in (1,2)"; — user1607528
– user1607528, Commented Dec 17, 2013 at 20:01

Mike Brant · Accepted Answer · 2013-12-17 20:25:23Z

1

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).

answered Dec 17, 2013 at 20:25

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2511119 Over a year ago

Thanks Mike, this works great. But the output of the project IDs isnt my problem. The Output in the example is 1 and 2 (these are the Project_IDs where 1 and 2 are working together, so "1" and "2" as numbers are both used as Name_ID and Project_ID) So Output is 1 and 2. Now I wanna look for 1 and 2 in my Projects Table and extract Project_Name from Projects via the Project_ID. I need to somehow store the output and use it again to query against the Project table I assume...not sure though.

Mike Brant Over a year ago

@user2511119 You would just join the project table to this query. Something like SELECT p.project_name AS project, COUNT(DISTINCT pn.name_id) AS name_count` FROM project_names AS pn INNER JOIN projects AS p ON pn.project_id = p.project_id WHERE ...`

Eisa Adil · Accepted Answer · 2013-12-17 20:15:41Z

0

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)");

edited Dec 17, 2013 at 20:15

answered Dec 17, 2013 at 20:02

Eisa Adil

1,74311 silver badges16 bronze badges

3 Comments

user2511119 Over a year ago

If I use OR wouldnt this tell the engine to give me all projects where either 1 or 2 are involved? I want to find out in which projects they have been working togeher.

user2511119 Over a year ago

Thanks for the input. If I use $res = mysql_query("SELECT PROJECT_ID FROM Projects_Names WHERE NAME_ID in (1,2)"); The output is: 4 Projects found. 1 1 2 2 If I use it with mysqli_query($con it will give me "Warning: mysqli_query() expects at least 2 parameters [...] error. It still doesnt give me the names for project 1 and 2 corresponding in table Projects...I need to connect the output to the names in a diffrent table. 1 = Project Winter 2 = Project Sun Thats what I want to see as output :)

Eisa Adil Over a year ago

The first parameter has to be $connection. Look it up.

Collectives™ on Stack Overflow

Getting data from sql database and then use it for select query via php

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related