Help with adding subcategory name to the Videos table

PHP
1

The web script’s video upload Form, that I’m trying to modify, allows the uploader to enter title, description and tags into the Form, so they can be used to search for the video. And the Form also allows the uploader to select a subcategory. All this get stored in the Videos table. However, the subcategory id is what gets stored there, not it’s corresponding subcategory name.

I’m trying to modify so that when the selected subcategory id gets stored in the Videos table, so does it’s corresponding subcategory name (all subcategory id’s and named are listed in another table.)

Looking at the script’s file named submit-video.php here: https://pastebin.com/V4rQUH09
lines 212 & 273

Can you tell me if you think this file is where I would modify code to have the script add the appropriate subcategory name to the Videos table when the uploaded video info gets added?

I look forward to any assistance.

2

Is there a particular reason you want to break the database normalization?

2 Likes
3

The only reason I could think of for doing this would be if the subcategory name changes from time to time, and there is a need for the video to show the subcategory name that it was uploaded under, not the current name.

4

Many thanks for the replies.
I’d ultimately like to have the subcategory name as a searchable keyword.
I see this:

$get_videos = $db->where("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%' OR short_id LIKE '%$keyword%') AND privacy = 0 ".$category.$date)->where('approved',1)->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

but am not sure how to get the corresponding subcategory name to work here, being that the subcategory name is not in the T_VIDEOS table, but listed in the T_LANGS table. Is this where a JOIN should go?

I thought the code in the pastebin link, https://pastebin.com/V4rQUH09 line 212:

        $sub_category = 0;
 
        if (!empty($_POST['sub_category_id'])) {
            $is_found = $db->where('type',PT_Secure($_POST['category_id']))->where('lang_key',PT_Secure($_POST['sub_category_id']))->getValue(T_LANGS,'COUNT(*)');
            if ($is_found > 0) {
                $sub_category = PT_Secure($_POST['sub_category_id']);
            }
        }

where it references the T_LANGS might be where to modify in order to get the subcategory name to use as a searchable keyword.

Any guidance is welcomed.

5

You don’t need to add it to the insert - the data is already there by extension of the foreign key.

Assuming you’ve added the sub-category table to the join earlier in that block of code, you should be able to add the sub-category name to the where clause like you have the rest of the field names.

6

Much thanks again.
Regarding “added the sub-category table to the join earlier in that block of code”, if by sub-category table you mean where I said ‘subcategory name is …listed in the T_LANGS table’, no, that is not earlier in that block of code. Any guidance with what that might look like in that block of code is welcomed.

7

What does the rest of the SELECT query look like? You show
$get_videos = $db->where etc but where are queried fields and table(s) defined?
Are you able to do joined queries in this setup?

You mentioned that the user can select a subcategory and the subcategory id is saved to T_VIDEOS. If you can’t do a joined query, and you can’t add a new field to T_VIDEOS called sub_category_name so you can save both the ID and the NAME then maybe when a search is made you can query T_LANGS table for a sub_category LIKE the search term. If a subcategory $sub_id is returned you can add this to your main search query.

$subsearch = (!empty($sub_id) ? " OR sub_category LIKE '%$sub_id%'" : '');
$get_videos = $db->where("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%' OR short_id LIKE '%$keyword%' $subsearch) AND privacy = 0 ".$category.$date)->where('approved',1)->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

Hey, it’s an idea.

8

Thanks for your reply and idea.
I could " add a new field to T_VIDEOS called sub_category_name".
Regarding “you can query T_LANGS table for a sub_category LIKE the search term”, I don’t see that in your example:

$subsearch = (!empty($sub_id) ? " OR sub_category LIKE '%$sub_id%'" : '');
$get_videos = $db->where("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%' OR short_id LIKE '%$keyword%' $subsearch) AND privacy = 0 ".$category.$date)->where('approved',1)->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

This script also has this file search.php:

<?php 
$data = array('status' => 400);
if (!empty($_POST['search_value'])) {
	$search_value = PT_Secure($_POST['search_value']);
	$search_result = $db->rawQuery("SELECT * FROM " . T_VIDEOS . " WHERE (title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%') AND privacy = 0 LIMIT 10");
	if (!empty($search_result)) {
		$html = '';
		foreach ($search_result as $key => $search) {
			$search = PT_GetVideoByID($search, 0, 0, 0);
			$html .= "<div class='search-result'><a href='$search->url'>$search->title</a></div>";
		}
		$data = array('status' => 200, 'html' => $html);
	}
} 
?>

any additional assistance with what might be the best solution is appreciated.

9

Not sure if I coded this correctly. I wanted to account for if more than 1 subcategory is returned This might need some tweaking.

<?php 
$data = array('status' => 400);
if (!empty($_POST['search_value'])) {
	$search_value = PT_Secure($_POST['search_value']); 
	
	$subsearch_result = $db->rawQuery("SELECT id AS sub_id FROM " . T_LANGS . " WHERE (sub_category LIKE '%$search_value%'");
	$sub_ids = array();
	if(!empty($subsearch_result)) {
		foreach ($subsearch_result as $k => $sub_id) {
			$sub_ids[] = $sub_id;
		}
	}
	
	if(count($sub_ids) == 1){
		$subsearch = " OR sub_category LIKE '%".$sub_ids[0]."%'";
	}elseif(count($sub_ids) > 1){
		$subsearch = " OR sub_category IN (".implode(',',$sub_ids).")";
	}else{
		$subsearch = '';
	}
	
	$search_result = $db->rawQuery("SELECT * FROM " . T_VIDEOS . " WHERE (title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%' $subsearch) AND privacy = 0 LIMIT 10");
	if (!empty($search_result)) {
		$html = '';
		foreach ($search_result as $key => $search) {
			$search = PT_GetVideoByID($search, 0, 0, 0);
			$html .= "<div class='search-result'><a href='$search->url'>$search->title</a></div>";
		}
		$data = array('status' => 200, 'html' => $html);
	}
} 
?>

EDIT: I am not sure if you wish the subcategory to be restrictive so main query must match subcategory. If so you’d switch OR to AND. Also I guess you wouldn’t search for LIKE when dealing with these subcategory ID’s s the conditions should be.

if(count($sub_ids) == 1){
	$subsearch = " OR sub_category = '".$sub_ids[0]."'";
}elseif(count($sub_ids) > 1){
	$subsearch = " OR sub_category IN (".implode(',',$sub_ids).")";
}else{
	$subsearch = '';
}

Or Restrictive

if(count($sub_ids) == 1){
	$subsearch = " AND sub_category = '".$sub_ids[0]."'";
}elseif(count($sub_ids) > 1){
	$subsearch = " AND sub_category IN (".implode(',',$sub_ids).")";
}else{
	$subsearch = '';
}

ALSO note if making it “restrictive” you would place the $subsearch variable outside the parentheses where the privacy condition is placed.

$search_result = $db->rawQuery("SELECT * FROM " . T_VIDEOS . " WHERE (title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%') $subsearch AND privacy = 0 LIMIT 10");
10

That approach is overkill. Just add the table as a join in the raw query…

Do a LEFT OUTER JOIN on the table with the sub category names. Use the sub-category field from videos to join with the primary key on the second table. You want to do a left outer join because the video may or may not have a sub category attached to it based on the insert code provided earlier.

Reformatted to make it easier to read, but you can put it on one line if you’d like…I might have the exact field names incorrect but this should give you the basic idea.

$search_result = $db->rawQuery("SELECT V.* 
                                  FROM " . T_VIDEOS . " V 
                                  LEFT OUTER JOIN " . T_LANGS . " L ON V.sub_category = L.id
                                 WHERE (V.title LIKE '%$search_value%' OR 
                                        V.tags LIKE '%$search_value%' OR 
                                        V.description LIKE '%$search_value%' OR
                                        L.sub_category LIKE '%$search_value%' ) 
                                   AND privacy = 0 
                                 LIMIT 10");`
1 Like
11

I had based my answer on that he couldn’t do a joined query in his current setup. #7 My bad.

12

Many thanks for all the replies/assistance. Greatly appreciated.
Yes, I believe “Do a LEFT OUTER JOIN…etc. is correct” with the correct field names.
However, the script is not yet finding the sub_category in a search. I believe this one last search file needs to be modified https://pastebin.com/z4M2brpu on line 73.

which is:

$get_videos = $db->where("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%') AND privacy = 0 ".$category.$date)->where('approved',1)->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

I would imagine I need to add in ‘sub_category LIKE %$keyword%’ and somehow tie it into T_LANGS ?

Any additional guidance is welcomed.

13

It looks like code is missing somewhere there.

Where is $db defined? I see a rawQuery on line 65 but it’s commented out and also only in one if statement. This means it’s defined somewhere else to point to just the videos table. You’ll need to add the left outer join there, and then yes, add the sub category to the where clause. But it needs to be associated to the sub category table for the where to work.

You will need to find where $db is defined.

Edit:

Are you using laravel? This kinda looks like the syntax and if so, then you’ll need to look for something like

$db = DB::table(T_VIDEOS)

and change it to something like

$db = DB::table(T_VIDEOS)
            ->leftJoin(T_LANGS,  T_VIDEOS . 'sub_category', '=',  T_LANGS . '.id')

NOT TESTED but should give you the basic gist of what to look for/change

14

Thanks again.
The script is not laravel. The script has a tables.php file with, along with many others, has this listed:
define('T_VIDEOS', 'videos');
and
define('T_LANGS', 'langs');

Seeing a non-laravel example of line 73 with sub_category and T_LANGS included, that would be very helpful, thanks again

15

Hmmm. Ah. This is the mysqli class syntax. ugh.

I found this link for you but to be honest, I find that as clear as mud: https://php-mysqli.readthedocs.io/en/latest/join-method.html

Play with that to figure out which way is the right way. I think users would equal to T_VIDEOS and T_LANGS would equal products, but I’m not sure (like at all).

I’d tell you to switch over to the raw query instead, but that doesn’t have paging built in, so you’d have to build that capability in, which can be more work.

16

Thanks.
I tried modifying line 73 with this :

$get_videos = $db->LEFT JOIN " . T_LANGS . " L ON V.sub_category = L.lang_key WHERE("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%' OR sub_category LIKE '%$keyword%') AND privacy = 0 ".$category.$date)->where('approved',1)->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

and got a 500 error

any additional ideas are appreciated

17

Did you read the link I just posted? The code would be more like this (again T_VIDEOS and T_LANGS may be reversed, dunno):

$db->join(T_VIDEOS . " v", "v.sub_category = l.id");
$get_videos = $db->where("(title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%' OR sub_category LIKE '%$keyword%') AND privacy = 0 AND approved = 1".$category.$date)
                 ->orderBy('id', 'ASC')
                 ->objectbuilder()
                 ->paginate(T_LANGS " l", $pt->page_number);
18

Wouldn’t you need the table aliases “v” and “l” on the where fields as well?

$db->join(T_VIDEOS . " v", "v.sub_category = l.id", “LEFT”);
$get_videos = $db->where("(v.title LIKE '%$keyword%' OR v.tags LIKE '%$keyword%' OR v.description LIKE '%$keyword%' OR l.sub_category LIKE '%$keyword%') AND v.privacy = 0 AND v.approved = 1".$category.$date)
                 ->orderBy('v.id', 'ASC')
                 ->objectbuilder()
                 ->paginate(T_LANGS " l", $pt->page_number);
19

Only if the fields were named the same on both tables. If the field names are unique, then no. But for human readability? Yes

20

Much appreciated again.
In the T_LANGS table, the column that matches the T_VIDEOS ‘sub_category’ column is named ‘lang_key’ (not id). I substituted l.id with l.lang_key and tried a search using the above #17 example and #18, but get a 500 error each time. (Also, reversed T_VIDEOS and T_LANGS as suggested).

This is my latest (500 error) attempt:

} else {
$db->join(T_LANGS . " v", "v.sub_category = l.lang_key", “LEFT”);
$get_videos = $db->where("(v.title LIKE '%$keyword%' OR v.tags LIKE '%$keyword%' OR v.description LIKE '%$keyword%' OR v.sub_category LIKE '%$keyword%') AND v.privacy = 0 AND v.approved = 1".$category.$date)
                 ->orderBy('v.id', 'ASC')
                 ->objectbuilder()
                 ->paginate(T_VIDEOS " l", $pt->page_number);

any additional guidance is welcomed.