基于所选复选框的SELECT查询

Question

I have a form, where users select available skills:

<form name="myForm" action="jssearch.php" method="post">

 <input type="checkbox" name="chk1[]" value="1">Helpdesk Support
 <input type="checkbox" name="chk1[]" value="2">DB Admin<br>
 <input type="checkbox" name="chk1[]" value="3">C++ Programming
 <input type="checkbox" name="chk1[]" value="5">HTML<br>
 <input type="checkbox" name="chk1[]" value="6">PHP<br>
 <input type="checkbox" name="chk1[]" value="7">Memory Dump Analysis<br>
 <input type="checkbox" name="chk1[]" value="8">SQL<br><br>

 <input type="submit" name="Update" value="Search">

</form> 

Based on these selections, I want to run a query against a many-to-many table and display the available jobs that contain the skills selected.

This is my query so far:

<?php

session_start();
mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("jobsearch") or die(mysql_error());

$variable=$_POST['chk1'];
foreach ($variable as $variablename)
{
    $query = mysql_query(
       "SELECT jobs.jobid AS job_id, jobs.jobtitle AS
        job_title,jobs.salary AS salary_desc, GROUP_CONCAT(skills.Desc) AS skills_desc    
        FROM jobskillsjoin
        INNER JOIN jobs ON jobs.jobid = jobskillsjoin.JobID
        INNER JOIN skills ON skills.skill_id = jobskillsjoin.SkillID
        WHERE skills.skill_id = '".$variablename."'
        GROUP BY jobs.jobid
        ")
    or die(mysql_error());
}

echo "<table border='1'>
<tr>
<th>Job ID</th>
<th>Job Title</th>
<th>Skills required</th>
<th>Salary Offered</th> 
</tr>";

while($row = mysql_fetch_array($query))
{
    echo "<tr>";
    echo "<td>" . $row['job_id'] . "</td>";
    echo "<td>" . $row['job_title'] . "</td>";
    echo "<td>" . $row['skills_desc'] . "</td>";
    echo "<td>" . $row['salary_desc'] . "</td>"; 
    echo "</tr>";
}
echo "</table>";


?>

What happens though is that only the last skill selected is run through the query. I want to display all the "hits" though.

I think I need a loop and an array but I'm not sure how to do it.

Answer 1

When you submit a checkbox like you did, it become an array in PHP side. You need to use this array in your where clause. Use the function implode to transform the array in a string and use the operator "in". So your where clause will be:

$query = mysql_query("SELECT jobs.jobid AS job_id, jobs.jobtitle AS
        job_title,jobs.salary AS salary_desc, GROUP_CONCAT(skills.Desc) AS skills_desc    
    FROM jobskillsjoin
    INNER JOIN jobs ON jobs.jobid = jobskillsjoin.JobID
    INNER JOIN skills ON skills.skill_id = jobskillsjoin.SkillID
    WHERE skills.skill_id in (". implode(",",$_POST['chk1']) .")
    GROUP BY jobs.jobid
    ")

This way, the query will return all skills checked.

To return all skills of a job which has at least one skill selected you need to change the query logic to something like:

SELECT j.jobid AS job_id, j.jobtitle AS
            job_title, GROUP_CONCAT(skills_Desc) AS skills_desc    
        FROM jobskillsjoin
        INNER JOIN jobs j ON j.jobid = jobskillsjoin.JobID
        INNER JOIN skills ON skills.skill_id = jobskillsjoin.SkillID
where exists(select 1 from jobskillsjoin where jobid = j.jobid and SkillID in (1,2))
        GROUP BY j.jobid;

Please, note I change the query. Do not copy and paste to your code. Adapt it to make sure you will not miss anything.