PHP MySQL Searching Help :)

You need to explode the phrase into individual words, check for "noise" such as "the", "and" as well as the hyphen and remove them.

this isn't tested but should be pretty much right.

<?PHP
$search = $_GET[search];

$bits = removeNoise($search);

$first = true;
$where = '';
foreach($bits as $word) 
{
	if (!$first) $where .= ' OR ';
	else $first = false;
	
	$where .= "`page_title` LIKE '%{$word}%' 
			OR `stripped_cache` LIKE '%{.$word}%'";
}
$sql = "SELECT * 
			FROM `filesearch` 
			WHERE {$where}";

$result = mysql_query();

$output = array();
while($row = mysql_fetch_array($result))
{
	$output[$row['id']]['display'] = $row['page_title'] . '<br />';
	$output[$row['id']]['score'] = getScore($row['page_title'], $bits);
}

// http://php.net/manual/en/function.uasort.php
uasort($output, 'cmp');

foreach($output as $row) echo $row['display'];


/**
 * @return integer
 * @param array $a
 * @param array $b
 * @desc used by the uasort
*/
function cmp($a, $b) {
    if ($a['score'] == $b['score']) {
        return 0;
    }
    return ($a['score'] < $b['score']) ? -1 : 1;
}	



/**
 * @return integer
 * @param string $title
 * @param array $words
 * @desc 	loop through the words and generate a score on how well it fits.
			1 match with a 1 word title would score higher than 1 match with a 5 word title
			but you need to determine the scoring rules

*/
function getScore($title, $words)
{
	return $score;	
}

/**
 * @return array
 * @param unknown $search
 * @desc 	remove any items from the array that are "noise"
		 	also need to remove hyphens etc
*/
function removeNoise($search)
{
	$noise = array('and','the','if');
	$search = str_replace($noise, '', $search);
	$bits = explode(' ', $search);
	return $bits;
}
?>

PHP:

 

That depends on the size of your database but I suspect that the users download speed will be a bigger bottle neck.

using like slows things down so changing to = will speed things up

$where .= "`page_title` = '{$word}' 
OR `stripped_cache` = '{.$word}'";

PHP:

and then you need to check your indexes.

If its slow on long phrases consider limiting the size of the array holding $bits.

 

You may need to turn that search into a reverse search by having

/**
 * @return integer
 * @param array $a
 * @param array $b
 * @desc used by the uasort
*/
function cmp($a, $b) {
    if ($a['score'] == $b['score']) {
        return 0;
    }
    return ($a['score'] < $b['score']) ? 1 : -1;
}   

PHP:

I've just changed the return values.