I have a search form which searches a site content table to pull back appropriate results.
I want to search the title and content fields and pull back results in order of relevance. Giving highest priority to the title.
Say we had a table (tblContent) of
intID | strTitle | txtContent
1 | Smith John | Lorem Ipsum
2 | Lorem Ipsum | Lorem John Smith Ipsum
3 | John Smith | Lorem Ipsum Lorem Ipsum
4 | Lorem Ipsum | Lorem Ipsum Lorem Ipsum
5 | Lorem Ipsum | Lorem Ipsum Smith John
And you were searching for "John Smith" the results should come back in the order of 3,2,1,5
How is this possible?
Whereas if the telephone book were organized by first name then by last name, you'd find all the Johns together, then within the Johns, all the 'S' last names would be grouped together. So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index.
column order does not matter. This is purely a convenience feature. just to allow you to restructure your database table the way you like after it has been created.
If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.
When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
I managed to get pretty spot on with this:
SELECT *,
( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance
FROM content
WHERE (MATCH(strTitle,txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE) )
ORDER BY relevance DESC
mysql fulltext search is a good thing but it has a limit of minimum 4 characters word to be indexed. Al tough the limit can be changed but changing server variables isn't possible in all scenarios. In such a situation, I recommend the solution suggested in order by case
select
*
from
mytable a
WHERE
(a.title like 'somthing%'
OR a.title like '%somthing%'
OR a.title like 'somthing%')
ORDER BY case
WHEN a.title LIKE 'somthing%' THEN 1
WHEN a.title LIKE '%somthing%' THEN 2
WHEN a.title LIKE '%somthing' THEN 3
ELSE 4 END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With