Order by most relevant result

[veledrom]

Hi,

How do i order results for most relevant one?

Select id, title from data where (titleLIKE '%PHP developer%')

id title
---- -------------------------------
1 ASP developer
2 WEB developer
3 PHP
4 PHP developer

In this case, it should be displayed in this other 4, 3, 2, 1 OR 4, 3, 1, 2

Thanks

[nav33n]

Are you sure your query returns ASP developer/WEB developer/PHP when you use LIKE '%PHP developer%' :S This, infact should return only "PHP developer".

[verruckt24]

What is the definition of relevant ? If you use the LIKE clause, you will get only those records in which the filed contains the content of the LIKE clause. So if you have all records containing the content you want, I don't think you can order them by any means.

[veledrom]

Let's say a texbox has "PHP, ASP, developer, programmer, web developer, Java"

What SQL statement could be be for me to use to return results?

Thanks

[nav33n]

Explode the textbox value. Then use the exploded values to
1. build your query this way.

where title like '%PHP%' or title like '%ASP%' or title like '%developer%' or title like '%programmer%' or title like '%web developer%' or title like '%Java%'

2. Or you can have different queries for every exploded value.

select * from table where title like %PHP%';
select * from table where title like '%ASP%';
....etc...

I can't think of anything else. Maybe someone else has a better idea.

[manojjena1982]

Hi Vele,

You can try this one--
select id, title from data where title LIKE '%PHP developer%' union select * from data

The above query will return the exact string with all other rows in the database.
select id, title from data where title LIKE '%PHP developer%' union select * from data group by title

The above query will return the exact string with all other rows in sorted order in the database.

[mwasif]

You should use MySQL FULLTEXT searching capability.

Look here for examples http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

[veledrom]

Although, i have used naw33n's solution, manojjena1982's solution seems usefull to. mwasif's solution needs some time to be absorbed.

Thanks