Help with adding subcategory name to the Videos table

He’s using the mysqli class structure (at least best I can figure), so we’re guessing based on this documentation that I found.
https://php-mysqli.readthedocs.io/en/latest/join-method.html

If there’s a 500, then there has to be a log somewhere. Not sure where, but there has to be one…

Thanks again for the replies.
I have looked around the script for other join queries and don’t see any.
I looked in several logs and don’t see any error logged.
Originally, my post requested “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”, then I would think that the seached ‘name’ can be pulled from $get_videos when searched for.
I looked forward to your comments.

Something doesn’t add up here, and there’s a piece someone is missing. If you actually got the query to work in phpmyadmin, You should be able to copy/paste that whole thing into this block and get valid results.

$search_result = $db->rawQuery("SELECT V.* FROM 'videos' LEFT OUTER JOIN 'langs' ON BINARY V.sub_category = BINARY L.lang_key WHERE (V.title LIKE '%$search_value%' OR V.tags LIKE '%$search_value%' OR V.description LIKE '%$search_value%' OR L.english LIKE '%$search_value%' ) AND V.privacy = 0 LIMIT 10");

If you’re getting a 500 with a copy/paste, then something is different. Double check the web account the page is running under has access to write to the php_error log. Also check the instructions here.

I’m sorry, but you’re going to have to do some detective work to figure out what the error is before any more guidance can be given. Finding the error will probably give you enough guidance to know how to fix it yourself, especially if it’s related to something covered earlier in this thread.

Just to check from post #40, even testing this join() array gives a 500 error?

You might try adding this after the query to see if anything is shown.

if($db->getLastErrno() === 0){}else{ echo 'Update failed. Error: '. $db->getLastError();}

ALSO can you verify if you are using “mysqli class structure” or is it a class structure you wrote. I remember when you were learning to write and use “class” a few years back and so I questioned if this is something you created.

A new approach to try.
Please check in phpmyadmin the following. Yes I do realize I have AND privacy = 0 AND approved = 1 twice. It is because of a orWHERE condition.
Note: change search term as needed.

SELECT * FROM videos WHERE BINARY sub_category IN (SELECT BINARY lang_key FROM langs WHERE english = 'Atlanta')  
 AND privacy = 0 AND approved = 1 
 OR (title LIKE '%Atlanta%' OR tags LIKE '%Atlanta%' OR description LIKE '%Atlanta%')
 AND privacy = 0 AND approved = 1 
 ORDER BY id ASC

If this works in phpmyadmin then hopefully this will work.

$subids = $db->subQuery ();
$subids->where ("english", $search_value, "=");
$subids->get (T_LANGS, null, "BINARY lang_key");

$db->where ("BINARY sub_category", $subids, 'in');	  
$db->where (" AND privacy = 0 AND approved = 1")
$db->orWHERE ("(title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%')");
$db->where (" AND privacy = 0 AND approved = 1")
->orderBy('id', 'ASC')
->objectbuilder()
->paginate(T_LANGS, $pt->page_number);

#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’

It appears that the error isn’t caused by the $search_result modification, but appears upon the $get_videos lines of code modifications

Why haven’t you (in phpmyadmin) edited the table/field structure so the fields lang/lang_key and videos/sub_category both have the same collation? That seems like a simple edit to fix the error.

The resolution used in the join syntax should resolve this same issue. It’s still doing a join essentially, just in a different (and more expensive) way.

So wait, you got results from the rawQuery approach?

Helps if I can find the official documentation for the database class… :roll_eyes:

https://github.com/ThingEngineer/PHP-MySQLi-Database-Class/blob/master/readme.md#join-method.

This looks like it should work…

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

looks like you should be able to print_r the $get_videos object. If you do that and exit before you try to use the object, you might get more debugging data…

print_r ($get_videos);

I don’t know how to edited the table/field structure so the fields lang/lang_key and videos/sub_category both have the same collation

Yes on the JOIN query, but he had the same collation error when testing the IN sub query test query from #45. How can BINARY be applied to ‘IN( values )’?

BINARY field1 IN (SELECT Binary Field2....

Error 500:

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

I’m sorry, I don’t understand “If you do that and exit before you try to use the object, you might get more debugging data…”

In phpmyadmin, select the table then click structure and click on the field and click Edit. You should see something like this with collation being a dropdown selection. Edit both (or one) so they are both using the same collation.

You’re getting the 500 because the $get_videos object is trying to be used (like $get_videos[“id”]) and it doesn’t exist because you’ve got something odd going on.

So if you do something like this right after the creation of $get_videos, you should be able to see the generated SQL statement (according to the docs at least). Then you should be able to copy/paste that to phpmyadmin and see what you get…

print_r($get_videos);
exit();

I edited code in #45 adding BINARY if you care to give that a try.

I have retried #45 with added BINARY in phpmyadmin > table ‘langs’ > sql box > go:
SELECT * FROM videos WHERE BINARY sub_category IN (SELECT BINARY lang_key FROM langs WHERE english = 'Atlanta') AND privacy = 0 AND approved = 1 OR (title LIKE '%Atlanta%' OR tags LIKE '%Atlanta%' OR description LIKE '%Atlanta%') AND privacy = 0 AND approved = 1 ORDER BY id ASC

and see one search result row displayed from the ‘videos’ table where the title and description info is outlined with a border.

So, I then run this line of code:
$search_result = $db->rawQuery("SELECT * FROM videos WHERE BINARY sub_category IN (SELECT BINARY lang_key FROM langs WHERE english = '$search_value') AND privacy = 0 AND approved = 1 OR (title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%') AND privacy = 0 AND approved = 1 ORDER BY id ASC");

is that correct?
“no videos found”

and also have this currently:
$get_videos = $db->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);

which is the orginal line of code, because all other modifications in that file generate the 500 error.

When I add

print_r ($get_videos);

like so:

$get_videos = $db->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); print_r ($get_videos);

I see:
Array ( )

and ‘no videos found’

I look forward to any replies

NOTE: Dave suggested placing exit(); after calling print_r($get_videos);
AND ALSO… this $search_value query is only being run when $search_value isset and not empty, correct?

Are you able to edit the table/field structure for collation? This should be fixed if possible.

Where you have this test… Isn’t correct as you should not directly be searching sub_category for $keyword.

$get_videos = $db->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);

Does this query work if you remove the sub_category part and just search for part of a title? Just ruling out normal search issues.

$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);

With exit(); added, I see only:
Array ( )

Regarding 'not empty, here is that file:

$data = array('status' => 400);
if (!empty($_POST['search_value'])) {
	$search_value = PT_Secure($_POST['search_value']);
$search_result = $db->rawQuery("SELECT * FROM videos WHERE BINARY sub_category IN (SELECT BINARY lang_key FROM langs WHERE english = '$search_value') AND privacy = 0 AND approved = 1 OR (title LIKE '%$search_value%' OR tags LIKE '%$search_value%' OR description LIKE '%$search_value%') AND privacy = 0 AND approved = 1 ORDER BY id ASC");
	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);
	}
}

In regard to “does this query work”, it has the same result, no videos found", upon searching for a sub category name

Regarding collation:
langs > lang_key is utf8_unicode_ci
I have changed videos > sub_category from utf8_general_ci to utf8_unicode_ci.
What are the chances that could cause other issues?

Is it possible to:
Add ‘sub_category id’ corresponding to ‘sub category name’ from ‘langs’ table and add to ‘videos’ table ‘tags’ column ?

Hmm I still think adding a subcategory_name field to video table would be a good option.
I think the query would go like this.

UPDATE videos v SET v.subcategory_name = (SELECT l.english FROM langs WHERE l.lang_key = v.sub_category LIMIT 1)

If want to update the tags I think the query would go like this.

UPDATE videos vid SET vid.tags = vid.tags.",".(SELECT l.english FROM langs l ON l.lang_key = vid.sub_category")

HEY do hold me to these… Make a backup DB to test on.