'where' => "the_client LIKE

SELECT SQL_CALC_FOUND_ROWS `videos`.*
FROM `cmsb_videos` as `videos`
 WHERE (the_client LIKE '%	20	%') 
 ORDER BY dragSortOrder DESC

Hi Daniel. Thank you for helping me.

My table is now named "videos" instead of youtube_videos but all is the same.

Both advanced select lists are pulldown displays so not mulit, right?

Both lists use num for value and title for labels. 

Additional question for you: is running the lists at the top of the page with "where" any more efficient than in the body with a foreach? Or is the same loop running behind the scenes in the php header for a list anyway?  

Daniel,

I am sorry. I described the setup wrong originally.

For the videos editor, it should have read:

I have another editor called youtube videos. It has a select list and I select a client in this youtube videos editor.

I made the edit to the orginal above to re-read less confusing, I hope (although youtube_videos is now just named vidoes) 

Hey rez,

I think I see the issue - the "LIKE '%\t{$value}\t%'" syntax is used when matching on multi-select list fields, to account for the difference in how the values are saved. When querying single-select list fields, you can do something like this instead:

'where' => mysql_escapef('the_client = ?', $case_studiesRecord['the_client']),

Additional question for you: is running the lists at the top of the page with "where" any more efficient than in the body with a foreach? Or is the same loop running behind the scenes in the php header for a list anyway?  

Using the "where" is more efficient, though the difference in performance will be quite small unless you're working with rather large numbers of records. There are still practical reasons to use the "where," though, e.g. the pagination functionality built into getRecords() won't be able to account for additional filtering done on the page.

Let me know if that helps, or if you have any other questions!

Thanks,

I see. Sorry about that, noted.

This works great. I'll have to look up what it means. Learning some MySQL would be useful. I do want to end up adding a visitor search page. Advanced searches / filtering for different projects and types.

I'll be adding another list for video types. Just like selecting a client for a video, I will select ad, 3D, etc. This will be a multi list though. Like a tag system.

Same for print projects. These will be uploaded graphics and probably tagged somehow.

So in the end, I'd like to have a search page to search all Video that are ads, for instance. 

Or all print, signs.

Or all client #20 videos.

I know there may be 50 ways to set this up but in the end, would you use MySQL to search like this, arrays, or what? There could be 1k print projects uploaded... but definitely hundreds.

Hey rez,

Glad that worked! Learning MySQL can definitely help when crafting these "where" clauses, though there are a few things here that are CMSB-specific which I can expand on a bit.

mysql_escapef() is a CMSB function used to prepare portions of a MySQL query by escaping values, which is good for security. The first parameter is the MySQL with "?" where you want any values to be entered, and the following parameters are the values. So this statement:

'where' => mysql_escapef('the_client = ?', $case_studiesRecord['the_client']),

Will evaluate to something like this:

'where' => "the_client = '20'",

It can also be used for two or more values like this:

'where' => mysql_escapef('field_one = ? AND field_two = ?', $value1, $value2),

As for the multi-select fields, the "where" is formatted differently because the data for these fields is stored in a "tab-delimited" format. The "\t" is the code representing a tab, and the "%" is a wildcard. Using "LIKE" instead of "=" is what lets the wildcards work. So if a multi-select field is storing the values 20, 21, and 22, the data will look like this:

\t20\t21\t22\t

So when we use "LIKE '%\t20\t%'" this means "match when the field contains '\t20\t'", rather than requiring an exact match on the whole field, so you can match a single value from many.

I know there may be 50 ways to set this up but in the end, would you use MySQL to search like this, arrays, or what? There could be 1k print projects uploaded... but definitely hundreds.

I would personally use PHP to craft a "where" to handle this. As you say there are many different ways, but a simple method could look something like this:

$whereClause = "TRUE";

// single select
if (!empty($_REQUEST['filterOne'])) {
  $whereClause .= mysql_escapef(' AND field_one = ?', $_REQUEST['filterOne']);
}

// single value in multi-select
if (!empty($_REQUEST['filterTwo'])) {
  $whereClause .= mysql_escapef(' AND field_two LIKE ?', "%\t{$_REQUEST['filterTwo']}\t%");
}

// multiple values in a multi-select
if (!empty($_REQUEST['multiFilter'])) {
  foreach($_REQUEST['multiFilter'] as $filterValue) {
    $whereClause .= mysql_escapef(' AND field_three LIKE ?', "%\t{$filterValue}\t%");
  }
}

list($records, $meta) = getRecords([
  ...
  'where' => $whereClause,
  ...
]);

We start with a "TRUE" to default to showing all records, then append " AND ..." for each different filter that exists.

Your exact implementation will depend a lot on the fields you use, but hopefully that gives you a good starting place!

Cheers,

This is so powerful.

The second half, the query explanations, I will have to wait to get hands on. I haven't done anything like this. Is this a situation where the page reloads and all of this executes for search? Where are these filter values / selects submitted from? Also, I'm certainly willing for a boost of help in customization if I get stuck. Just trying to understand what's going on here. This is a visitor executed search? I'll try to play with this later.

I wish you had a blog or something that you would regularly put out bits of info like this and maybe user requests for future posts / additions? Maybe this only took you five minutes, I don't know. (I've seen the Cookbook by the way, it's not what I mean, that's pretty much the forum info).

Perfect example: the advanced construction of where clauses in CMSB. Broken down and an example or two. I feel like it would be the most useful information in all of my experience here.

Sorry for the rant style response. I just wish we had more info like this to take steps past the docs. Maybe I sound lazy. I am supposed to go become a PHP and mySQL expert on my own. I just know I can build plenty of sites now as a jack of all trades using the basic docs. Advanced docs, CMSB function explanations, etc. would be nice in some form.

Hey rez,

I'm glad my explanation resonated with you! Though it did take a little longer than 5 minutes :) - a blog is an interesting idea though! Something I'll keep in mind.

To address this question:

Is this a situation where the page reloads and all of this executes for search? Where are these filter values / selects submitted from? Also, I'm certainly willing for a boost of help in customization if I get stuck. Just trying to understand what's going on here. This is a visitor executed search?

Yes, the idea here is it would be a user-executed search using an HTML form, where the input names correspond to the $_REQUEST keys; e.g. <select name="filterOne"> would provide the value to $_REQUEST['filterOne']. The "if" statements are set up so that the "where" will only be constructed from the inputs that have values entered.

Cheers,