help with ordering results from a database query by relevance...

[sitemaster16]

Hi guyz... i have this code... and i got it to order and display searched for keywords and display all the results even if they only match one of the words searched for... but the problem is when 2 entrys in the database both have the searched for keywords then they are both assigned the same % of relevance...

 

so the question is how would i increase the relevance count if the entry has more occurrences of the searched words?

 

Help on this would really be appreciated...]

 

Thanks ahead of time...

 

<?php

require_once 'config.php';


if (isset($_GET['search'])) {
$search = $_GET['search'];
$queryString = "search=$search";
} else {
$queryString = '';
}

if (isset($_GET['search']) && $_GET['search'] == '') {
$errorMessage = '<div class="error">No Search Term Provided</div>';
}


if (isset($_GET['page']) && (int)$_GET['page'] >= 0) {
$page = (int)$_GET['page'];
} else {
$page = 0;
}



if (isset($search) && $search !== '') {


//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search

// for paging
// how many rows to show per page
$rowsPerPage = 10;


// seperate all keywords in the search phrases
$searchChunks = explode(' ', $search);

// make cases for sql query
function getCases($searchChunks)
{

$cases = '';
$chunkCount = count($searchChunks) - 1;

for($i = 0; $i < count($searchChunks); $i++){

	if ($i == $chunkCount) {
		$cases .= "(CASE WHEN cntry_name LIKE '%$searchChunks[$i]%' THEN 1 ELSE 0 END)";
	} else {
		$cases .= "(CASE WHEN cntry_name LIKE '%$searchChunks[$i]%' THEN 1 ELSE 0 END)+";
	}
}
return $cases;
}


// make LIKES for sql query
function getLikes($searchChunks)
{

$likes = '';
$chunkCount = count($searchChunks) - 1;

for($i = 0; $i < count($searchChunks); $i++){

	if ($i == 0) {
		$likes .= "cntry_name LIKE '%$searchChunks[$i]%'";
	} else {
		$likes .= " OR cntry_name LIKE '%$searchChunks[$i]%'";
	}
}
return $likes;
}


// assign $cases to get the multiple case function
$cases = getCases($searchChunks);
$likes = getlikes($searchChunks);

// get all the data in the db
$sql = "SELECT *, ($cases) AS relevance
	FROM tbl_countrys
	WHERE $likes
	ORDER BY relevance DESC";

$result = dbQuery(getPagingQuery($sql, $rowsPerPage));


$pagingLink = getPagingLink($sql, $rowsPerPage, $queryString);

echo $errorMessage.'<form method="get" action="search.php">
<p>Search for Something</p><input type="text" value="'.$search.'" name="search" size=25 maxlength=25>
<input type="Submit" value="Search Now">
</form>
';

if (dbNumRows(dbQuery($sql)) == '0') {
echo 'No Results for <b>'.$search.'</b>';
} else {
echo 'Result for <b>'.$search.'</b><br /><br />';
}

//grab all the content
while($row = dbFetchAssoc($result)) {
   //the format is $variable = $r["nameofmysqlcolumn"];
   //modify these to match your mysql table columns

   $title=$row["cntry_name"];
   /*
   $message=$r["message"];
   $who=$r["who"];
   $date=$r["date"];
   $time=$r["time"];
   $id=$r["id"];
   */


   //display the row <br> $message <br> $who <br> $date | $time <br>
   echo "$title<br />";
}
echo '<br /><br /><br />'.$pagingLink;


} else {
echo $errorMessage.'<form method="get" action="search.php">
<p>Search for Something</p><input type="text" name="search" size=25 maxlength=25>
<input type="Submit" value="Search Now">
</form>
';
}
?>

[redarrow]

mysql regex i guess

 

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

 

DISTINCT might help aswell

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

[redarrow]

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

 

read this mate you want fulltext search defently

 

this page is very intresting to relevence to words......

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

 

 

[sitemaster16]

yeah i think i know what to do...

 

i will post the result when i finish it...

 

thanks... i think i will use the DISTINCT...