We have a MySQL InnoDB table in our database where we want to perform searches on a single field (text).
Upon search, the search results must be outputted to the client sorted (descending) by relevance, based on how many occurrences the search term have in each recordset of the field in question. The user will then be able to change the sorting to sort by date (descending).
The search must be able to use the LIKE parameter, and calculation of number of occurrences must also be counted towards the LIKE parameter.
The search must be available to client in the following three ways; all words, some words, exact match, meaning that there will be needed three different types of SQLs, one for each search method.
Changing the table to MyISAM is not an option, and the technique developed must be easy to clone to other table searches.
The website where the search should be available, is PHP (Symfony, Doctrine platform). Code must comply to PHP.