Duplicates returned: mysql or code error?

kilbey1 · Aug 17, 2004

I've set up a dynamic query which results in statements like this:

SELECT resume.Section_Value, candidate.Location FROM resume, candidate WHERE resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html unix') AND candidate.Location LIKE '%CA%' OR 'California'

And I return duplicates from the same record:

html unix network php Over 10 years of HTML experience. 2 years networking administration.

html unix network php Over 10 years of HTML experience. 2 years networking administration.

I am not sure if this is an error with my sql syntax, or with my code. Can anybody assist?

Code:

//set up SELECT statement
				$aSelect = array(); 
				if($_POST['skills'] != '') {
				  $aSelect[] = 'resume.Section_Value'; 
				}
				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';

			//set up FROM portion of sql statement
			$aFrom = array(); 
			if($_POST['skills'] != '') { 
			  $aFrom[] = 'resume';
			} 
			if($_POST['firstname'] || $_POST['lastname']  || $_POST['city'] || $_POST['state'] != '') { 
			  $aFrom[] = 'candidate'; 
			}

			//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);
			  $aWhere[] = "resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('" . $chars . "')"; 
			}
			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
			  '". $city . ", " . $fullstatename ."'";
			}
			else {
				if($_POST['city'] != '') {
				  $aWhere[] = "candidate.Location LIKE  '%" . $_POST['city'] . "%'"; 
				}
				if($_POST['state'] != '') {
			  		$aWhere[] = "candidate.Location LIKE  '%" . $state . "%' OR '". $fullstatename ."'"; 
				}
			}

			//build SELECT clause
			$sSelect = 'SELECT ' . implode(', ', $aSelect);

			// build FROM clause
			$sFrom = 'FROM ' . implode(', ', $aFrom);						

			// build where clause
			$sWhere = 'WHERE ' . implode(' AND ', $aWhere);

			// Select, Where and From clause done - add it to the SQL skeleton
			//$sql2 = $sql . " " . $sWhere;
			//$sql = "" . $sSelect . " " . $sFrom . " ". $sWhere;

			if($_POST['skills'] != '') {
				//$sql =. " AND resume.Section_ID = '1'";
				$sql = "" . $sSelect . " " . $sFrom . " " . $sWhere;
			}  else {
				$sql = "" . $sSelect . " " . $sFrom . " ". $sWhere;
			}

			echo $sql . "<P>";

			$result = mysql_query($sql) or die(mysql_error());

			//$num_rows = mysql_num_rows($result);
			while($row = mysql_fetch_array($result)){
				echo $row['Section_Value'] . "<P>";
			}
			//echo $num_rows;

bastien · Aug 17, 2004

try

select distinct

ultraslacker · Aug 17, 2004

And there should be something relating the two tables. Do you not have candidate_id in resume?

kilbey1 · Aug 17, 2004

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

Duplicates returned: mysql or code error?

Kkilbey1

Bbastien

Uultraslacker

Kkilbey1