Help with adding subcategory name to the Videos table

I’m spitballing here as I’ve never used the mySQLi class, but if you reverse the names, you also need to reverse the aliases.

If that still doesn’t work, you should be able to find more detailed info on the 500 in your error log.

$db->join(T_LANGS . " l", "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 " v", $pt->page_number);

Then at least it would be needed for the ->orderBy('v.id', 'ASC')
Also I believe both tables have sub_category but can’t say for sure.

→ CHRIS: I copied that “LEFT” from the link that Dave had posted but those quotes are curved like would be typed in a WORD Doc. Try fixing that to "LEFT".

→ CHRIS: Also in #20 you using v instead of l in

v.sub_category LIKE '%$keyword%'

so it should be the name of the sub_category found in T_LANGS

l.sub_category LIKE '%$keyword%'

Also you are missing the concatenate . between T_LANGS and "l"

->paginate(T_LANGS . " l", $pt->page_number);

Based on what I’ve seen, this is what I have.

			 
$db->join(T_VIDEOS . " 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 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);		

I appreciate the replies.
I have made the suggested changes and have tested #21 and #22 above, but still get 500 error. Nothing reflected in the error log file.
Any additional suggestions are appreciated…

I attempted to flip tables around as Dave mentioned with FULL OUTER JOIN as you are not paginating off the T_LANGS table.

$db->join(T_LANGS . " l", "l.lang_key = v.sub_category", "FULL OUTER JOIN");
$get_videos = $db->where("(l.sub_category 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);

Also like I said in #7and #9, if you can’t do a joined query you can query for the sub_category first.

$subsearch_result = $db->rawQuery("SELECT lang_key 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;
		}
	}
	$subsearch = (!empty($sub_ids) ? " OR sub_category IN (".implode(',',$sub_ids).")" :'');
	$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")
	->orderBy('id', 'ASC')->objectbuilder()->paginate(T_VIDEOS, $pt->page_number);

Thanks again. I appreciate the help.
I tried #24 with same error result.
Just want to add that there is no ‘sub_category’ column in the ‘langs’ table.
Here is a sample of that table:

langs

and a sample of the ‘videos’ table:
videos
which also has a ‘title’, ‘description’ and ‘tags’ column.

I’m hoping that when someone types in ‘Atlanta’, in the search box, for example, all videos where the uploader selected ‘Atlanta’ as a subcategory - upon uploading/submitting the video - will appear in the search results.

any additional guidance is welcomed

Then you would change the search field to english.

WHERE (english LIKE '%$search_value%'

ALSO then you can TEST any of the JOIN queries that use

l.sub_category LIKE '%$keyword%'

and CORRECT them to your table field names…

l.english LIKE '%$keyword%'

Having the wrong field names would kill things on the spot.

Much thanks. Yet, same result with this:

$search_result = $db->rawQuery("SELECT V.* FROM " . T_VIDEOS . " V LEFT OUTER JOIN " . T_LANGS . " L ON V.sub_category = 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 privacy = 0 LIMIT 10");

and

$db->join(T_LANGS . " l", "l.lang_key = v.sub_category", "FULL OUTER JOIN");
$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);

Have you done any direct query tests in phpmyadmin to check query against the database. Sometimes this can reveal where a problem might be.

SELECT V.* FROM `videos` V LEFT OUTER JOIN `langs` L ON V.sub_category = L.lang_key WHERE (V.title LIKE '%Atlanta%' OR V.tags LIKE '%Atlanta%' OR V.description LIKE '%Atlanta%' OR L.english LIKE '%Atlanta%' ) AND V.privacy = 0 LIMIT 10

I was just coming in to suggest this. Get the query working in phpmyadmin and then translate it over to code. If you can get the code working there, it should (:crossed_fingers:) be relatively easy to translate it over to the mysqli syntax.

1 Like

Thanks.
I tried this:
SELECT V.* FROM videosV LEFT OUTER JOINlangs L ON V.sub_category = L.lang_key WHERE (V.title LIKE '%Atlanta%' OR V.tags LIKE '%Atlanta%' OR V.description LIKE '%Atlanta%' OR L.english LIKE '%Atlanta%' ) AND V.privacy = 0 LIMIT 10

and see this:

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

I look forward to any comments

That means the collation on the two tables are not the same. (a quick google search would have told you that).

I’ve seen two approaches on my quick search. I don’t know if either will work…

ON (V.sub_category = L.lang_key utf8_unicode_ci)

or

ON BINARY v.sub_category = BINARY l.lang_key

Can the collation of those database fields just be changed so they match?

1 Like

Thanks.
The BINARY worked to resolve the error in sql, and showed a search result there, but when I applied it to the code, and searched, no search results. Currently I have this:

$search_result = $db->rawQuery("SELECT V.* FROM videosV LEFT OUTER JOINlangs L 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");

and this:
$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);

these two together show “no search results”.

When I replace with this:

$db->join(T_LANGS . " l", "l.lang_key = v.sub_category", "FULL OUTER JOIN");
$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 see 500 error.

Any additional guidance is appreciated.

Regarding “Can the collation of those database fields just be changed so they match?”
that is something I don’t know. Can you give me an example of that?

The left outer join doesn’t look right in the first item - should be a space before the V and between the JOIN and langs.

In the second, you didn’t apply the BINARY to the joined fields. Additionally, why are you doing a FULL OUTER JOIN instead of a LEFT OUTER JOIN?

Ahh that was my bad on post #24.

Thanks again.
I have corrected “doesn’t look right”. And now I have this:

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

and this:

$db->join(T_LANGS . " l", "BINARY l.lang_key = BINARY v.sub_category", "LEFT OUTER JOIN");
$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);

which displays 500 error upon search.
I look forward to any other guidance…

What are the results if you remove search conditions?

$db->join(T_LANGS . " l", "BINARY l.lang_key = BINARY v.sub_category", "LEFT OUTER JOIN");
$get_videos = $db->where(" v.privacy = 0 AND v.approved = 1")->orderBy('v.id', 'ASC')->objectbuilder()->paginate(T_VIDEOS . " v", $pt->page_number);

If that works add the category and date back in (assuming they are defined.)

$db->join(T_LANGS . " l", "BINARY l.lang_key = BINARY v.sub_category", "LEFT OUTER JOIN");
$get_videos = $db->where(" v.privacy = 0 AND v.approved = 1".$category.$date)->orderBy('v.id', 'ASC')->objectbuilder()->paginate(T_VIDEOS . " v", $pt->page_number);

I appreciate your suggestion, however, same result - 500 error

The it is time to look deeper into your program where queries are processed for a join() class.
If it is just php join() then shouldn’t an array be passed?

$db->join(array(T_LANGS . " l", " BINARY l.lang_key = BINARY v.sub_category", " LEFT OUTER JOIN"));

This would look like

langs l BINARY l.lang_key = BINARY v.sub_category LEFT OUTER JOIN

But that’s not what we want. We want to see the joined query, right?

$db->join(array(T_VIDEOS . " v", " LEFT OUTER JOIN ", T_LANGS . " l"," ON BINARY l.lang_key = BINARY v.sub_category "));

which would look like

video v LEFT OUTER JOIN langs l ON BINARY l.lang_key = BINARY v.sub_category