Hall of Fame

[Icey]

As we already talked on Linked in, I have the almighty problem with search queries. I want to update it, but can't really seems to find where exactly the search submit from tpl_header is sending the search terms so that the query send it to the database.

Also, I will say this here too ( off topic I know ) : The player isn't working for direct .flv and .wmv files. Only .mp4 as far as I am aware.

[Icey]

Made a..partial fix! The code is in _search.php and I made it to search only in description and..now seems everything is working, but as soon as I try to make it to also search in title, so "title and description", I am.. back to my first problem. Not working at all.

Code Select Expand

// Remove URL format
$key = str_replace(array("-","+")," ",$key);
$titleKey = str_replace(" ", "_",$key);
 
// Full Search - Title and Description
$vq = "SELECT ".DB_PREFIX."videos.id, ".DB_PREFIX."description.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,
        ".DB_PREFIX."users.name AS owner, ".DB_PREFIX."users.group_id,
        ".DB_PREFIX."channels.cat_name
        FROM ".DB_PREFIX."videos
        LEFT JOIN ".DB_PREFIX."users ON ".DB_PREFIX."videos.user_id = ".DB_PREFIX."users.id
        LEFT JOIN ".DB_PREFIX."description ON ".DB_PREFIX."videos.id = ".DB_PREFIX."description.vid
        LEFT JOIN ".DB_PREFIX."channels ON ".DB_PREFIX."videos.cat_id = ".DB_PREFIX."channels.id
        WHERE ".DB_PREFIX."videos.pub > 0 AND ".DB_PREFIX."videos.date < NOW()
        AND ( ".DB_PREFIX."videos.title LIKE '%".$titleKey."%' OR ".DB_PREFIX."description.description LIKE '%".$key."%' OR ".DB_PREFIX."channels.cat_name LIKE '%".$key."%' )
        ORDER BY ".DB_PREFIX."videos.title LIKE '".$titleKey."%' DESC,
                 ".DB_PREFIX."videos.title LIKE '%".$titleKey."%' DESC,
                 ".DB_PREFIX."videos.title ASC ".this_limit();

Any .. ideas Marius?

[Marius P.]

Try this via your sql tool (phpmyadmin?):

Code Select Expand

ALTER vibe_description
ADD FULLTEXT(description);

change vibe_ to your prefix.

[Icey]

Try this via your sql tool (phpmyadmin?):

Code Select Expand

ALTER vibe_description
ADD FULLTEXT(description);

change vibe_ to your prefix.

This worked with above code, but ..very slow. 5-7 seconds for a search query.

I changed the way the search is doing his query and now is very smooth ( almost instant ). Is not searching for the exact phrase, but is doing its job.. for now. You can check on my website if you want.

Here is the code for anyone wanting it :

Code Select Expand

// Remove URL format
$key = str_replace(array("-","+")," ",$key);
$titleKey = str_replace(" ", "_",$key);
 
// Separate the keywords
$keywords = explode(" ", $key);
 
// Query to search in both video descriptions and titles
$like_conditions = [];
foreach ($keywords as $keyword) {
    $like_conditions[] = DB_PREFIX . "description.description LIKE '%" . $keyword . "%'";
    $like_conditions[] = DB_PREFIX . "videos.title LIKE '%" . $keyword . "%'";
}
 
//The final query
$vq = "SELECT " . DB_PREFIX . "description.vid AS id, " . DB_PREFIX . "description.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
        FROM " . DB_PREFIX . "description
        LEFT JOIN " . DB_PREFIX . "videos ON " . DB_PREFIX . "description.vid = " . DB_PREFIX . "videos.id
        WHERE " . DB_PREFIX . "videos.pub > 0 AND " . DB_PREFIX . "videos.date < NOW()
        AND (" . implode(" OR ", $like_conditions) . ")
        ORDER BY " . DB_PREFIX . "videos.title LIKE '" . $titleKey . "%' DESC,
                 " . DB_PREFIX . "videos.title LIKE '%" . $titleKey . "%' DESC,
                 " . DB_PREFIX . "videos.title ASC " . this_limit();

I'll be making a new post now regarding another "issue"