MYSQL Full Text Search by Relevance

Question

I am trying to arrange a full-text search by relevance. Here is my code, it works if you remove ORDER BY, but does not sort by relevance. I tried this and it actually makes it find nothing at all ... Any ideas?

$query_for_result=mysql_query(" SELECT * FROM Assets WHERE MATCH ('Badge','First Name','Last Name','Service Tag','Asset Tag') AGAINST ('".$query."*' IN BOOLEAN MODE) and 'deleted' = '0' ORDER BY relevance DESC"); 

edit *

 <input type="text" name="query" /> <input type="hidden" value="Search" name="submit" /> <input type="submit" name="submit" value="Search" /> </form> <h4>Search by: Badge, First or Last Name, Service Tag, Asset Tag, Printer Queue or Printer IP.</h4> <br> </center> <?php if(isset($_GET['submit'])){ $db_tb_name=Assets; $db_tb_atr_name='First Name'; $query=mysql_real_escape_string($_GET['query']); $query_for_result=mysql_query(" SELECT * FROM Assets WHERE MATCH ('Badge','First Name','Last Name','Service Tag','Asset Tag') AGAINST ('".$query."*' IN BOOLEAN MODE) and 'deleted' = '0' ORDER BY relevance DESC"); 

then in code

 while($data_fetch=mysql_fetch_array($query_for_result)) { print '<h3>'; print "<a href=\"modify.php?id=" . $data_fetch['id'] . "\">" . $data_fetch['First Name'] . ' ' . $data_fetch['Last Name'] . "</a>"; print '<br/><b>Badge:</b> '. $data_fetch['Badge']; print '<br/> <b>Service Tag:</b> '. $data_fetch['Service Tag']; print ' <b>Asset Tag:</b> '. $data_fetch['Asset Tag']; print '<br/> <b>Status:</b> '. $data_fetch['Status']; print '<br/><b>Employee Status: </b>'; if( $data_fetch['Employee Status'] == 'Active' ){ print '<font color="#32CD32">' . $data_fetch['Employee Status'] . '</font>'; } elseif( $data_fetch['Employee Status'] == 'Terminated' ){ print '<font color="red">' . $data_fetch['Employee Status'] . '</font>';} print '<br/> <b>Last Modified:</b> '. $data_fetch['Last Modified']; print "<span> </span>"; print '</h3>'; print '<br/><p>' ; } 

UPDATE This worked for me. Finally I earned using

 $query_for_result=mysql_query("SELECT *, MATCH ('Badge','First Name','Last Name','Service Tag','Asset Tag') AGAINST ('".$query."'IN BOOLEAN MODE) AS Relevance FROM Assets WHERE 1 AND MATCH ('Badge','First Name','Last Name','Service Tag','Asset Tag') AGAINST ('".$query."' IN BOOLEAN MODE) and 'deleted' = '0' ORDER BY Relevance DESC "); 

Answer 1

From MySQL Full Text Search Query Documentation :

They do not automatically sort rows in decreasing order of relevance. You can see this from the previous query result: the row with the highest relevance is the one that contains "MySQL" twice, but the last, not the first, is specified.

This explains why it is not sorted by relevance without ORDER BY . Now, in order to be able to order relevance , you need to define it:

 SELECT *, MATCH (`Badge`,`First Name`,`Last Name`,`Service Tag`,`Asset Tag`) as relevance WHERE MATCH AGAINST ('".$query."*' IN BOOLEAN MODE) and `deleted` = '0' ORDER BY relevance DESC