I am creating a site search feature for my e-shop and am using the following code to order my query:
ORDER BY((case when name RLIKE $wholeword then 3 else 0 end) + (case when name RLIKE $partialword then 1 else 0)) DESC
This selects both complete and partial matches from the database and orders them giving complete matches priority over partial matches. However, I have converted to using prepared statements which results in me having the following query:
ORDER BY((case when name RLIKE ? then 3 else 0 end) + (case when name RLIKE ? then 1 else 0)) DESC
Of course, in this query both '$wholeword' and '$partialword' are replaced by '?' and so look the same when calculating the relevance. Meaning that complete matches are no longer given priority over partial matches. I need a way to separate the two queries when ordering.
Note: the query still works but no longer provides different weighting to $wholeword and $partialword when sorting.
Does anyone have any ideas? any help would be greatly appreciated!
Nico
edit: emphasized ORDER BY part of the query (removed the rest)