用于从多对多关系返回值的SQL

Question

I am stuck while attempting to retrieve data via PHP from a MySQL database when there are multiple keywords in the SQL statement. My expertise is mostly in the frontend so its been quite a struggle to get this going.

I have a screen where i want to show several skills(these are keywords), all retrieved from a single table in the DB. When a user selects on one or more skills and clicks on a link, i want to return the mapped projects for the selected skills, all via the right backend querying(i.e. another option i had was to return all data to the frontend and use JS to filter as desired, but somehow i don't currently find this optimal for my current scenario)

I have a MySQL table called web_projects which lists several projects with columns like project_id, project_name etc etc

Another table called web_skills lists several skills with columns like skill_id, skill_name etc etc

Since there is a many-to-many relationship (each project can have several skills and each skill can be included in several projects) I have a third table to manage this relationship. This table web_project_skills has 3 columns id, project_id, skill_id.

The visual view, with the mapping:

[please don't mind the data types, its just a quick table created and will be modified later]

I post data from my application via:

http://localhost/am/get.php?q=getprojectsforskill&skill=HTML5

and retrieve the string:

$q = trim($_GET["skill"]);

The following SQL(it is a researched effort) helps me get values for this request and it works fine:

$st = "SELECT web_projects.* 
           FROM web_projects 
            JOIN web_project_skills 
            ON web_projects.project_id=web_project_skills.project_id 
            WHERE web_project_skills.skill_id = 
                (SELECT web_skills.skill_id 
                FROM web_skills 
                WHERE web_skills.skill_name = '".$q."' 
                )";

Now my post method has changed and I get a space separated string, like:

http://localhost/am/get.php?q=getprojectsforskill&skill=HTML5%2CCSS3

My query now includes:

$q = trim($_GET["skill"]);
$pieces = explode(",", $q);

$subQuery = "SELECT web_skills.skill_id 
        FROM web_skills 
        WHERE web_skills.skill_name = '$pieces[0]'";

if(count($pieces)>1){
    for($i=1;$i<count($pieces);$i++){
        $subQuery.=" OR web_skills.skill_name = '$pieces[$i]' ";
    }
}   
$st = "SELECT web_projects.* 
           FROM web_projects 
            JOIN web_project_skills 
            ON web_projects.project_id=web_project_skills.project_id 
            WHERE web_project_skills.skill_id = ($subQuery)";

For a request with a single value it works fine since there is no addition to the SQL statement via the for loop.

But for request with more than one i get errors. Example URL(same as above):

http://localhost/am/get.php?q=getprojectsforskill&skill=HTML5%2CCSS3

The errors I get with the 3 statement changes I attempted in the subQuery variable:

 $subQuery.=" OR web_skills.skill_name = '$pieces[$i]' ";

Above statement returns an error: An Error occured! SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

$subQuery.=" UNION WHERE web_skills.skill_name='$pieces[$i]' ";

Above statement returns an error: An Error occured! SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE web_skills.skill_name='CSS3' )' at line 7

 $subQuery.=" AND web_skills.skill_name = '$pieces[$i]' ";

Above statement returns an empty array

Answer 1

You don't need a sub-query for this, you can simply just add another join.

SELECT web_projects.* 
           FROM web_projects 
            JOIN web_project_skills 
            ON web_projects.project_id=web_project_skills.project_id 
            JOIN web_skills 
            ON web_skills.skill_id=web_project_skills.skill_id 
            WHERE skill_name = '".$q."'

That should return all projects that are associated with a given skill.