我有一个表单，用户在其中选择可用的技能:
<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>
基于这些选择，我想对一个多对多表运行一个查询，并显示包含所选技能的可用作业。
这是我到目前为止的查询:
<?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>";
?>
所发生的情况是，只有最后选择的技能在查询中运行。我想显示所有的"hits"
我想我需要一个循环和一个数组，但我不确定如何做到这一点
当你像你一样提交一个复选框时，它在PHP端变成一个数组。您需要在where子句中使用这个数组。使用函数implode将数组转换为字符串，并使用操作符"in"。where子句是:
$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
")
这样，查询将返回所有检查过的技能。
要返回至少选择了一项技能的作业的所有技能，您需要将查询逻辑更改为如下内容:
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;
请注意我更改了查询。不要复制粘贴到代码中。调整它，以确保您不会错过任何内容