sitemaster16 Posted November 22, 2008 Share Posted November 22, 2008 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> '; } ?> Link to comment https://forums.phpfreaks.com/topic/133763-help-with-ordering-results-from-a-database-query-by-relevance/ Share on other sites More sharing options...
redarrow Posted November 22, 2008 Share Posted November 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/133763-help-with-ordering-results-from-a-database-query-by-relevance/#findComment-696111 Share on other sites More sharing options...
redarrow Posted November 22, 2008 Share Posted November 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/133763-help-with-ordering-results-from-a-database-query-by-relevance/#findComment-696115 Share on other sites More sharing options...
sitemaster16 Posted November 22, 2008 Author Share Posted November 22, 2008 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... Link to comment https://forums.phpfreaks.com/topic/133763-help-with-ordering-results-from-a-database-query-by-relevance/#findComment-696250 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.