Help with adding subcategory name to the Videos table

PHP
61

Thank you again for your reply.
First, I’m not sure where the update code should be placed. More likely in the submit-video.php file that my initial posting showed: https://pastebin.com/V4rQUH09
would you agree?
Also, should the v and l need to be defined for the query?
Additionally, does it need a $sql = before this:
UPDATE videos v SET v.subcategory_name = (SELECT l.english FROM langs WHERE l.lang_key = v.sub_category LIMIT 1)

I look forward to additional assistance.

62

I would not put either of those queries in your code. This kind of query should only be done one time probably in phpmyadmin as it it updating all records. Whichever approach/query you use you should update your video upload and edit coding so if a subcategory is selected, you either insert/update both the sub_category (lang_key) and subcategory_name (english) into `videos OR you would add the (english value) into tags along with other tags.

63

Thanks again.
You say “one time”. I am looking for a solution where ‘each time’ a video is uploaded the subcategory name is added to the videos table (corresponding to the subcategory_id that appears in that row) without updating all records. Is that possible?

I look forward to your comments

64

Yes, this coding needs to added to your upload and insert sections… The same query/array that you have for the sub_category selection in the form can be used to get the name when dealing with POST sub_category_id… Than likely (based on the selection) the array is built like lang_key => english so english is shown to the user and lang_key is the option value. Assuming you have or can make a $sub_categories array like I mentioned you could get the name using this same array.

$subcategory_name = (!empty($_POST['sub_category_id']) && array_key_exists($_POST['sub_category_id'],$sub_categories) ? $sub_categories[$_POST['sub_category_id']] : '');

Otherwise you could do a quick query to get english using the posted lang_key.

65

Also around line 208 you have a sub category section where you query for count(*) of POST sub category against the langs table. Instead of calling for count(*) you could call for english and so with a few small mods to existing code you can get the value.

$sub_category = 0;
$subcategory_name = '';
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(*)');
	$is_found = $db->where('type',PT_Secure($_POST['category_id']))->where('lang_key',PT_Secure($_POST['sub_category_id']))->getValue(T_LANGS,'english');
	//if ($is_found['langcnt'] > 0) {
	if (!empty($is_found['langcnt'])) {
		$sub_category = PT_Secure($_POST['sub_category_id']);
		$subcategory_name = $is_found;
	}
}
66

Thanks again Drummin.
I tried your suggestion without success, which means that in the videos table where I added the ‘subcategory_name’ column (with the same structure as langs > english table) only NULL appears in that column after uploading a file.

You referenced the file in the link:https://pastebin.com/V4rQUH09
I don’t know if it makes a difference, but in your suggested code in #65 you are commenting out this line:
//if ($is_found['langcnt'] > 0) {

but that doesn’t exist in the file.

Line 212 is this:
if ($is_found > 0) {

also, does subcategory_name now need to appear in the array on line 232?

Much thanks again, I look forward to your comments.

67

Correct. My bad… I see my error. I had originally edited that line adding the key ['langcnt'] but with my edited query getValue should be returning english so you would not be using the key ['langcnt'] We also would not use if ($is_found > 0) { anymore, Instead $is_found should return english so it should be if (!empty($is_found)) {

$sub_category = 0;
$subcategory_name = '';
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,'english');
	if (!empty($is_found)) {
		$sub_category = PT_Secure($_POST['sub_category_id']);
		$subcategory_name = $is_found;
	}
}
68

Many thanks again.
However, after uploading a video file only NULL appears in the videos > subcategory_name column

also, does subcategory_name now need to appear in the array on line 232?

Much thanks again, I look forward to your comments.

69

Yes, you would add that field => value pair.

70

I have tried to add this without success.

'subcategory_name' => $subcategory_name,

any additional guidance is welcomed

71

There needs to be some self investigating on your part.
Does $sub_category have a value? Or are they both missing?
What do you see if you print_r($is_found); ?

I do not have a program to test these queries on.

1 Like
72

Brilliant. Many, many many thanks.
Your code in #67 works successfully.
(I had something in the wrong place).
Greatly appreciate all your help…

1 Like
73

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