Help with Search by Category

I’m using aphp web video script, where a User can search by keyword. Here is the code in the Search folder. Is it possible to Search by Category and then by keyword? Could it be just a code modification here:

<?php
if (empty($_GET['keyword'])) {
	header("Location: " . PT_Link('login'));
    exit();
}
$keyword = PT_Secure($_GET['keyword']);

$list = '<div class="text-center no-content-found empty_state"><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="feather feather-video-off"><path d="M16 16v1a2 2 0 0 1-2 2H3a2 2 0 0 1-2-2V7a2 2 0 0 1 2-2h2m5.66 0H14a2 2 0 0 1 2 2v3.34l1 1L23 7v10"></path><line x1="1" y1="1" x2="23" y2="23"></line></svg>' . $lang->no_videos_found_for_now . '</div>';
$list2 = '<div class="text-center no-content-found empty_state"><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="feather feather-video-off"><path d="M16 16v1a2 2 0 0 1-2 2H3a2 2 0 0 1-2-2V7a2 2 0 0 1 2-2h2m5.66 0H14a2 2 0 0 1 2 2v3.34l1 1L23 7v10"></path><line x1="1" y1="1" x2="23" y2="23"></line></svg></div>';
$final = '';

if ($pt->config->total_videos > 1000000) {
    $get_videos = $db->rawQuery("SELECT * FROM " . T_VIDEOS . " WHERE MATCH (title) AGAINST ('$keyword') OR tags LIKE '%$keyword%' ORDER BY id ASC LIMIT 20");
} else {
    $get_videos = $db->rawQuery("SELECT * FROM " . T_VIDEOS . " WHERE title LIKE '%$keyword%' OR tags LIKE '%$keyword%' ORDER BY id ASC LIMIT 20");
}

if (!empty($get_videos)) {
    $len = count($get_videos);
    foreach ($get_videos as $key => $video) {
        $video = PT_GetVideoByID($video, 0, 0, 0);
        $pt->last_video = false;
        if ($key == $len - 1) {
            $pt->last_video = true;
        }
        $final .= PT_LoadPage('search/list', array(
            'ID' => $video->id,
            'USER_DATA' => $video->owner,
            'THUMBNAIL' => $video->thumbnail,
            'URL' => $video->url,
            'TITLE' => $video->title,
            'DESC' => $video->markup_description,
            'VIEWS' => $video->views,
            'VIEWS_NUM' => number_format($video->views),
            'TIME' => $video->time_ago,
            'DURATION' => $video->duration
        ));
    }
}
if (empty($final)) {
	$final = $list;
}

$get_users = $db->rawQuery("SELECT * FROM " . T_USERS . " WHERE ((`username` LIKE '%$keyword%') OR CONCAT( `first_name`,  ' ', `last_name` ) LIKE  '%$keyword%') ORDER BY id ASC LIMIT 50");
if (!empty($get_users)) {
    $len = count($get_users);
    foreach ($get_users as $key => $user) {
        $user = PT_UserData($user, array('data' => true));
        $pt->last_user = false;
        if ($key == $len - 1) {
            $pt->last_user = true;
        }
        $final2 .= PT_LoadPage('search/user-list', array(
            'ID' => $user->id,
            'USER_DATA' => $user,
        ));
    }
}

if (empty($final2)) {
    $final2 = $list2;
}


$pt->videos      = $get_videos;
$pt->users      = $get_users;
$pt->page        = 'search';
$pt->title       = $lang->search . ' | ' . $pt->config->title;
$pt->description = $pt->config->description;
$pt->keyword     = $pt->config->keyword;
$pt->content     = PT_LoadPage('search/content', array(
    'VIDEOS' => $final,
    'USERS' => $final2,
    'KEYWORD' => $keyword
));

Is the category a column in the videos table, or do you have a separate table to link categories and videos, for example so that a video can appear in more than one category?

Either way, it’s just a case of checking to see whether one or more categories have been selected, and modifying the query to suit. Without knowing the database layout it’s impossible to be more specific.

Thanks for your reply/message.
The only place in the db that I see anything about categories is in the ‘videos’ table, the column named category_id

Otherwise, to add/change categories the script developer instructs to go to a php file:

<?php 
$categories = array(
    '1' => 'Film & Animation',
    '2' => 'Cars & Vehicles',
    '3' => 'Music',
    '4' => 'Pets & Animals',
    '5' => 'Sports',
    '6' => 'Travel & Events',
    '7' => 'Gaming',
    '8' => 'People & Blogs',
    '9' => 'Comedy',
    '10' => 'Entertainment',
    '11' => 'News & Politics',
    '12' => 'How-to & Style',
    '13' => 'Non-profits & Activism',
);

So, based on that, can you suggest an example of, or more info regarding “checking to see whether one or more categories have been selected, and modifying the query to suit”, please?

There are two steps involved here:

First, modify your search html code (where you currently enter or choose keywords) to include a way for the searcher to select which category or categories they want to include.

Once you have that working, and can see the selection in your PHP code using var_dump(), that’s the time to start including it in the search query.

If there is a category_id column in your database table, then that will probably store the category. One first thing to do here is to check that there is only ever one category in that column - if there is more than one, perhaps comma-separated, that’s a poor database design and will make the search query more complex. So hopefully there is only one for each video.

Looking at your note above, it seems that the categories are hard-coded into the system, so you can use that array to display the selections in your search page, and to validate the selection in your PHP code. It’s up to you how the selection is shown, that will vary depending on whether you want to allow more than one category.

As for modifying the query, my pseudo-code would be something like

.. before your existing queries are created ..
are any categories selected? 
  yes: 
    build those categories into a WHERE clause, starting with AND as you already have one
  no:
    leave the categories variable empty
.. then ..
build the new categories variable into your existing two queries, just after the existing WHERE clause

This will add an additional condition into your query which should limit the search to only include the selected category/ies of video.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.