dpacmittal Posted April 23, 2010 Share Posted April 23, 2010 My client has a video site and he wishes to display lyrics when someone watches a music video. He bought a database for that. Now, most videos' titles contain artist name and song title. I take it as argument and search it in database and present the lyrics. It works for 60% of the songs and doesn't works for the others. For eg; I searched 'Beatles let it be' and it returned wrong lyrics. My query is: SELECT *, MATCH(title,artist,album) AGAINST ('%$query%') as Relevance FROM lyrics WHERE MATCH(title,artist,album) AGAINST ('%$query%' IN BOOLEAN MODE) HAVING Relevance > 6 ORDER BY Relevance DESC limit 1 $query is the video title. PS: This is the first time I am working with Fulltext so my query might be noobish. EDIT: I just noticed that I am using album in the query too, is it possible thats what causing the inaccuracy? Link to comment https://forums.phpfreaks.com/topic/199519-fulltext-searches-on-2-columns/ Share on other sites More sharing options...
Ken2k7 Posted April 23, 2010 Share Posted April 23, 2010 What's with the % signs? Link to comment https://forums.phpfreaks.com/topic/199519-fulltext-searches-on-2-columns/#findComment-1047195 Share on other sites More sharing options...
dpacmittal Posted April 23, 2010 Author Share Posted April 23, 2010 What's with the % signs? Wildcard? Doesn't it work on fulltext? Actually I took help from a comment in mysql documentation. I saw it there, so I put it. Link to comment https://forums.phpfreaks.com/topic/199519-fulltext-searches-on-2-columns/#findComment-1047198 Share on other sites More sharing options...
Ken2k7 Posted April 23, 2010 Share Posted April 23, 2010 I doubt it, but even if it does work, why use it? MATCH... AGAINST just searches if the given text is in the field. It's sort of already doing a wildcard search. It's like: search if "mit" is in the string "dpacmittal". Link to comment https://forums.phpfreaks.com/topic/199519-fulltext-searches-on-2-columns/#findComment-1047225 Share on other sites More sharing options...
dpacmittal Posted April 23, 2010 Author Share Posted April 23, 2010 Okay so I removed the '%' from the query as well as the album field. It still doesn't return the required lyrics when searching for some songs.. Whats wrong? I've manually checked and they are there in the database. Link to comment https://forums.phpfreaks.com/topic/199519-fulltext-searches-on-2-columns/#findComment-1047227 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.