I do. I managed to just now get it worked out! The problem started with a cartesian join. I also had an error in my location syntax. See below. Is there any way within this code I can now show only those where all skills are matched? IE:
skills: html unix
2 results:
2 matches in candidate 172 (html, unix)
1 match in candidate 173 (Unix)
Display the candidate with 2 matches OR echo 'No exact matches"
CODE:
if($_POST['state'] != '') {
$sql = "SELECT State, fullstatename FROM states WHERE State='" . $_POST['state'] . "'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$state = $row['State'];
$fullstatename = $row['fullstatename'];
}//end while
}//end if
//set up SELECT statement
$aSelect = array();
if($_POST['skills'] != '') {
$aSelect[] = 'resume.Section_Value';
$aSelect[] = 'resume.Candidate_ID';
}
if($_POST['firstname'] != '') {
$aSelect[] = 'candidate.First_Name';
}
if($_POST['lastname'] != '') {
$aSelect[] = 'candidate.Last_Name';
}
if($_POST['city'] != '' || $_POST['state'] != '') {
$aSelect[] = 'candidate.Location';
}
$aSelect[] = 'candidate.Candidate_ID';
$aSelect[] = 'candidate.Last_Name';
$aSelect[] = 'candidate.First_Name';
//set up FROM portion of sql statement
$aFrom = array();
if($_POST['skills'] != '') {
$aFrom[] = 'resume';
}
if($_POST['firstname'] || $_POST['lastname'] || $_POST['city'] || $_POST['state'] != '') {
if($_POST['skills'] != ''){
$aFrom[] = 'INNER JOIN candidate';
} else {
$aFrom[] = 'candidate';
}
}
//set up ON portion of SQL statement
/*$aOn = array();
if($_POST['skills'] != ''){
$aOn[] = "candidate.Candidate_ID = resume.Candidate_ID";
}*/
//set up WHERE portion of sql statement
$aWhere = array();
if($_POST['skills'] != '') {
$chars_temp = explode(" ", $_POST['skills']);
//print_r($chars_temp);
//print_r($chars);
$chars = array();
foreach($chars_temp as $key => $value) {
//only include if array is not empty
//(ie. catches empty spaces in text field entry)
if (!empty($value)) {
$chars[] = strtolower($value);
}
}
$chars = implode(" ",$chars);
//pull keywords from Section_ID 1
$aWhere[] = "resume.Section_ID = '1'";
//match Section_Value against keywords
$aWhere[] = "MATCH (resume.Section_Value) AGAINST ('" . $chars . "')";
//match Candidate_ID between tables 'candidate' and 'resume'
$aWhere[] = 'candidate.Candidate_ID = resume.Candidate_ID';
}
if($_POST['firstname'] != '') {
$aWhere[] = "candidate.First_Name LIKE '" . $_POST['firstname'] . "%'";
}
if($_POST['lastname'] != '') {
$aWhere[] = "candidate.Last_Name LIKE '" . $_POST['lastname'] . "%'";
}
if($_POST['city'] != '' && $_POST['state'] != '') {
//$aWhere[] = "candidate.Location LIKE '%" . $_POST['city'] . ", " . $state . "%' OR
$aWhere[] = "candidate.Location LIKE '%" . $_POST['city'] . "%' AND (candidate.Location = '".$state."' OR candidate.Location = '".$fullstatename."')";
}
else {
if($_POST['city'] != '') {
$aWhere[] = "candidate.Location LIKE '%" . $_POST['city'] . "%'";
}
if($_POST['state'] != '') {
$aWhere[] = "candidate.Location IN ('".$state."', '".$fullstatename."')";
}
}
//build SELECT clause
$sSelect = 'SELECT ' . implode(', ', $aSelect);
// build FROM clause
if($_POST['skills'] != '') {
//eliminate imploding on , to give proper structure with INNER JOIN in statement
$sFrom = 'FROM ' . implode(' ', $aFrom);
} else {
$sFrom = 'FROM ' . implode(', ', $aFrom);
}
// build ON clause
/*if($_POST['skills'] != ''){
$sOn = 'ON ' . implode(' ', $aOn);
}*/
// build where clause
$sWhere = 'WHERE ' . implode(' AND ', $aWhere);
// Select, Where and From clause done - add it to the SQL skeleton
$sql = "" . $sSelect . " " . $sFrom . " " . $sWhere . " GROUP BY resume.Section_Value";
echo $sql . "<P>";
$result = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($result);
echo $num_rows;
if($num_rows == 0) {
echo "<strong>No results to display.</strong> <BR>
Try expanding your search be eliminating some fields in your search.";
} else {
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Candidate ID: </td>
<td>Last Name</td>
<td>First Name</td>
<?php
//if skills field is not empty, display the skills match results
if($_POST['skills'] != '') {
?>
<td>Skills</td>
<?php
}
?>
</tr>
<?php
function alternate_2colors($color_1, $color_2, $cpt) {
$colors = ($cpt % 2) ? $color_1 : $color_2;
return $colors;
}
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$nb += 1;
$colors = alternate_2colors('#C0C0C0', '#FFFFFF', $nb);
/*$sql_skills = "SELECT Candidate_ID, Section_ID, Section_Value
FROM resume
WHERE Candidate_ID = '". $row['Candidate_ID'] ."'
AND Section_ID = '1'";
$result_skills = mysql_query($sql_skills) or die(mysql_error());
$row_skills = mysql_fetch_array($result_skills);*/
//explode the skills located in database
$string = trim($row['Section_Value']);
$string = str_replace("\r", '', $string);
$string = str_replace("\n", ' ', $string);
$string = str_replace("\r\n", ' ', $string);
$skills_temp = explode(' ', $string);
//print_r($skills_temp);
$skills = array();
foreach ($skills_temp as $key => $value)
{
if (!empty($value)) {$skills[] = strtolower($value);};
}
//print_r($skills);
//get skills from fields entered in form
$chars_temp = explode(" ", $_POST['skills']);
//print_r($chars_temp);
//print_r($chars);
$chars = array();
foreach($chars_temp as $value) {
$chars2 = strtolower($value);
$chars[] = $chars2;
}
//print_r($chars);
//intersect arrays created from database and form field SKILLS
$arresult = array_intersect($skills, $chars);
//print_r($arresult);
//give only unique occurences
$arresult = array_unique($arresult);
//count up occurences of skills
$count2 = array_count_values($arresult);
//sum up occurences of skills
$skillscount = array_sum($count2);
//print_r($skillscount);
