MySQL Search (from tutorial)

[tijmenamsing]

Hello,

 

I'm making a website which includes an articles page. I want users to be able to search for particular articles.

For this search function I used the Simple SQL Search tutorial from phpfreaks (http://www.phpfreaks.com/tutorial/simple-sql-search).

It's working quite well but i'd like to expand it a little. The most important thing i want changed is what happens when users enters a string which holds multiple words. The function from the tutorial perceives these words as one string and searches for that string as a whole. For example when a user enters 'winter 2012' and an article holds the string 'winter of 2012', the function returns no matches. This is, ofcourse, not the way i'd like it to function.

 

I've managed to seperate the words and search for them individually by using explode and foreach, but now i'm getting duplicate results when an article holds mulitple words from the users' entry string. My idea to fix this was to make an array with the article id's of the articles that have matched, and then to exclude them from the SQL query for the next word from the search string by using 'NOT IN $array' in the where clause.

Unfortunately i haven't managed to succeed with this so far and that's why i ask for your help..

 

 

This my search function:

 

function zoekopdracht() {
   require('dbcon.php');
   $zoekopdr = trim($_GET['zzoek']);
   $zoekopdr = strip_tags($zoekopdr); 
   if (strlen($zoekopdr) < 3) {
      echo "De zoekopdr moet minimaal 3 karakters bevatten.";
   } else {
      $zoekopdr = explode(" ", $zoekopdr);
      foreach($zoekopdr as $zoekterm) {
         $zoekopdrDB = mysql_real_escape_string($zoekterm);
         $searchSQL = "SELECT * FROM n_artikelen WHERE ";
         $types = array();
         $types[] = isset($_GET['zartikel'])?" volledig_artikel LIKE '%{$zoekopdrDB}%'":'';
         $types[] = isset($_GET['ztitel'])?" titel LIKE '%{$zoekopdrDB}%'":'';
         $types[] = isset($_GET['zjaar'])?" YEAR(publicatie) LIKE '%{$zoekopdrDB}%'":'';      
         $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)     
         if (count($types) < 1) {
            $types[] = "volledig_artikel LIKE '%{$zoekopdrDB}%'";  // use the artikel + titel as a default search if none are checked
            $types[] = "titel LIKE '%{$zoekopdrDB}%'";
         }
         $searchSQL .= implode(" OR ", $types) . " ORDER BY publicatie LIMIT 0,10";
         $searchResult = mysql_query($searchSQL) or die(mysql_error());         
         if (mysql_num_rows($searchResult) < 1) {
            echo "De zoekopdracht '{$zoekopdr}' heeft geen resultaten.";   
         } else {
            while ($row = mysql_fetch_array($searchResult, MYSQL_NUM)) { 
               artikelelement($row);   // function that outputs the result of $row[]
            }
         } 
      }
   }   
}

 

 

Additionally, if this problem is fixed, i'd love to see a way to sort the results on closest match (article which matches two words > article which matches one word).

 

 

Thanks!

[jcbones]

By searching a column by every word separated by a %, you will get results where all the words are in the column, but not necessarily in a string.  You could force it to a string if the user puts the search in quotes.

Example

<?php
if($_SERVER['REQUEST_METHOD'] == 'POST') { //if a post request is made.
  if(!empty($_POST['search'])) { //if the search is NOT empty.
   if(strpos($_POST['search'],'"') !== false) { //if the search contains a quote "
   preg_match_all('~"([^"]+)"~',$_POST['search'],$matches); //match the quote.
   $quoted_string = $matches[1][0]; //get the match from the array.
   $quote = "column LIKE '%" . mysql_real_escape_string($quoted_string) . "%'"; //set it to a variable inside a proper formatted query column selection.
   $_POST['search'] = str_replace('"' . $quoted_string . '"','',$_POST['search']); //remove the quote from the search.
}
$parts = explode(' ',$_POST['search']);//Split the string on a space.
$parts = array_filter($parts); //now filter the array to get rid of empty indexes.
$parts = array_map('mysql_real_escape_string',$parts);  //escape SQL data.
//YOu can use if/else statements to finalize the query string, I used ternary operator.
                                       //if the quote is not empty, use it---if the quote and parts are not empty(both) then separate them with OR---if parts is not empty, use them.
$sql = "SELECT * FROM table WHERE " . (!empty($quote) ? $quote : NULL) . (!empty($parts) && !empty($quote) ? ' OR ' : NULL) . (!empty($parts) ? "column LIKE '%" . implode('%',$parts) . "%'" : NULL);
   }
echo $sql; //print a proper sql formatted string to the screen.
}

     

 

[tijmenamsing]

Thanks for you reply, i got it working now!

Instead of your idea to search for the string as a whole when a user enters quotes, i added a checkbox which does almost the same thing.

I also added a filter so the user can select in which column the string should be searched for.

 

code :

<?php
if(!empty($_GET['zoekopdr'])) {
	$zoekopdr = trim($_GET['zoekopdr']);
	if ((!isset($_GET['zArtikel']) || $_GET['zArtikel'] == "off") && (!isset($_GET['zTitel']) || $_GET['zTitel'] == "off") && (!isset($_GET['zJaar']) || $_GET['zJaar'] == "")) { // if no checkbox is selected
		echo "Geen zoekgebied opgegeven.";
	} elseif (strlen($zoekopdr) < 3) { // if string length < 3
		echo "De zoekopdr moet minimaal 3 karakters bevatten.";
	} else { 
		require_once('dbcon.php');
		if (isset($_GET['zExact']) && $_GET['zExact'] == "on") { // if user wants to search for the complete string
			$zoekq = " LIKE '%" . mysql_real_escape_string($zoekopdr) . "%' ";
		}
		else { // if user wants to search for seperated words
			$parts = explode(' ',$zoekopdr);
			$parts = array_filter($parts); // filter the array to get rid of empty indexes.
			$parts = array_map('mysql_real_escape_string',$parts);  // escape SQL data.
			$zoekq = " LIKE '%" . implode('%',$parts) . "%' ";
		}	
		$query = "SELECT * FROM n_artikelen WHERE ";
		if (isset($_GET['zJaar']) && $_GET['zJaar'] != "") { // if user only wants results from the selected year
			$query .= " YEAR(publicatie) = '" . $_GET['zJaar'] . "'";
			$x = true;
		} 			
		if (isset($_GET['zArtikel']) && $_GET['zArtikel'] == "on") { // if user wants to search in column1
			if ($x == true) { 
				$query .= " AND ";
			}
			$query .= " volledig_artikel $zoekq";
			$y = true;
		}
		if (isset($_GET['zTitel']) && $_GET['zTitel'] == "on") { // if user wants to search in column2
			if ($x == true && $y == false) { 
				$query .= " AND ";
			}
			if ($y == true) { 
				$query .= " OR ";
			}
			$query .= " titel $zoekq";
		} 
		$result = mysql_query($query) or die(mysql_error());	      
		if (mysql_num_rows($result) < 1) { // if there are no results
			echo "De zoekopdracht '{$zoekopdr}' heeft geen resultaten.";	
		} else { // else print the results
			while ($row = mysql_fetch_array($result, MYSQL_NUM)) { 
				artikelelement($row);	
			}
		} 
	}
}
?>

 

Any ideas on how to sort on closest match?