Hall of Fame

[ClashCorp]

Right now when I search on my phpVibe website it seems to bias videos based on their video ID. For example if you have two videos one with the video ID of #100 (Video A) and and another with the video ID of #1000 (Video B) and a user searches for the exact title of the Video B then Video A will still outrank it in the search results even if Video A has nothing to do with the users query. This seems to also be the case when looking at the suggested videos list it shows videos with lower ID's at the top and videos with higher ID's at the bottom. You can really see this happening if you do a search and look at the video ID's of each result.

[PHPVibe A.]

There are 2 algorithms in com/com_search.php. one for if key is under 3 letters, and one for fullsearch text, bun none rank by id.

Code Select Expand

if(strlen($key) < 4) {
 $vq = "select ".$options.", ".DB_PREFIX."users.name as owner FROM ".DB_PREFIX."videos LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id 
	WHERE ".DB_PREFIX."videos.pub > 0 and ( ".DB_PREFIX."videos.title like '%".$key."%' or ".DB_PREFIX."videos.description like '%".$key."%' or ".DB_PREFIX."videos.tags like '%".$key."%' ) ".$interval."
	   ORDER BY CASE WHEN ".DB_PREFIX."videos.title like '" .$key. "%' THEN 0
	           WHEN ".DB_PREFIX."videos.title like '%" .$key. "%' THEN 1
	           WHEN ".DB_PREFIX."videos.tags like '" .$key. "%' THEN 2
               WHEN ".DB_PREFIX."videos.tags like '%" .$key. "%' THEN 3		   
               WHEN ".DB_PREFIX."videos.description like '%" .$key. "%' THEN 4
			   WHEN ".DB_PREFIX."videos.tags like '%" .$key. "%' THEN 5
               ELSE 6
          END, title ".this_limit();
 } else {
 /* Use full search */	 
$vq = "select ".$options.", ".DB_PREFIX."users.name as owner,
MATCH (title,description,tags) AGAINST ('".$key."' IN BOOLEAN MODE) AS relevance,
MATCH (title) AGAINST ('".$key."' IN BOOLEAN MODE) AS title_relevance FROM ".DB_PREFIX."videos LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id 
	WHERE MATCH (title,description,tags) AGAINST('".$key."' IN BOOLEAN MODE) AND ".DB_PREFIX."videos.pub > 0 $interval ".this_limit();
 }

[ClashCorp]

I took a look at that file, and tried removing everything in the If-Else statement except for the following code:
and it seems to have fixed my issue. It might just simply be something with my server but I'm not sure. Seems like whenever I try to include the "Use full search" code it displays results based on Video ID (Atleast thats what it does on my server).

Code Select Expand

//Remove url format
$key = str_replace(array("-","+")," ",$key);
 
$options = DB_PREFIX."videos.id,".DB_PREFIX."videos.description,".DB_PREFIX."videos.title, ".DB_PREFIX."videos.date,".DB_PREFIX."videos.user_id,".DB_PREFIX."videos.thumb,".DB_PREFIX."videos.views,".DB_PREFIX."videos.liked,".DB_PREFIX."videos.duration,".DB_PREFIX."videos.nsfw";
 
 
       $vq = "select ".$options.", ".DB_PREFIX."users.name as owner FROM ".DB_PREFIX."videos LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id 
    WHERE ".DB_PREFIX."videos.pub > 0 and ( ".DB_PREFIX."videos.title like '%".$key."%' or ".DB_PREFIX."videos.description like '%".$key."%' or ".DB_PREFIX."videos.tags like '%".$key."%' ) ".$interval."
       ORDER BY CASE WHEN ".DB_PREFIX."videos.title like '" .$key. "%' THEN 0
               WHEN ".DB_PREFIX."videos.title like '%" .$key. "%' THEN 1
               WHEN ".DB_PREFIX."videos.tags like '" .$key. "%' THEN 2
               WHEN ".DB_PREFIX."videos.tags like '%" .$key. "%' THEN 3        
               WHEN ".DB_PREFIX."videos.description like '%" .$key. "%' THEN 4
               WHEN ".DB_PREFIX."videos.tags like '%" .$key. "%' THEN 5
               ELSE 6
          END, title ".this_limit();

Thanks for making this amazing script and keep up the fantastic work!

[PHPVibe A.]

Fulltext search may be problematic in several scenarios, but it still gets best ranking results. Until sql comes with a new selection algorithm, it's still the best overall solution.