'where' => "the_client LIKE

9 posts by 2 authors in: Forums > CMS Builder
Last Post: March 23, 2022   (RSS)

By rez - March 22, 2022 - edited: March 22, 2022

Is this still valid in 3.55 and php 7.4? not sure how else to do it.

  list($case_studiesRecords, $case_studiesMetaData) = getRecords(array(
    'tableName'   => 'case_studies',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $case_studiesRecord = @$case_studiesRecords[0]; // get first record    

if ($case_studiesMetaData):
 list($youtube_videoRecords, $youtube_videoMetaData) = getRecords(array(
      'tableName'   => 'youtube_video',
      'where' => "the_client LIKE '%\t{$case_studiesRecord['the_client']}\t%'",			
      'loadUploads' => true,
      'allowSearch' => false,
    ));
 // showme( $case_studiesRecord);
endif;

I have one editor that is a list of clients. A text field is named the_client

I have a second editor named case studies.  It has a select list and I select a client [in this case studies editor].

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

All content when displaying a case_study viewer record displays fine, specific to a client as intended. 

On the same page, trying to display the videos editor list only shows fine if I remove the "where" option (which would show irrelevant videos from other clients).  I can't figure out why they won't show with the where statement. the_client should match the_client and display.

In fact, to get this done I am going to do if statements in the body. With this test, both echo statements display "20" in a test case (client #20).


		list($videosRecords, $videosMetaData) = getRecords(array(
			'tableName'   => 'videos',			
    	'loadUploads' => true,
			'allowSearch' => false,

		));



<?php foreach ($videosRecords as $record): ?>
  <div class="cell medium-6 padding-1">
    <p><?php echo ($record['the_client']) ?></p>
    <p><?php echo ($case_studiesRecord['the_client']) ?></p>
  </div>
<?php endforeach ?>



web page confirms and displays:
20
20

And this gives the results I was looking for so I'm not sure why the where option doesn't work the same way in the top of the page instead.

              <?php foreach ($videosRecords as $record): ?>
                <div class="cell medium-6 padding-1">
                  <?php if ($record['the_client'] == $case_studiesRecord['the_client'] ): ?>
                    <?php echo ($record['embed_code']) ?>
                  <?php endif?>
                </div>
              <?php endforeach ?>

By daniel - March 22, 2022

Hi rez,

Generally speaking, this form of search should still be valid, but I think it might not be what you need to use in this case, or, your data might need some adjustment. I have a few questions to help narrow things down:

  • For both of the "the_client" select fields (in the case_studies table and in the youtube_video table), are they single- or multi-select fields?
  • For the the_client field in the case_studies table, how are the options set up? Is it an advanced list using num/title the same as the youtube_video table?

Also, could you run the youtube_video query with the "debugSql" option enabled, and copy the output here? Like this:

 list($youtube_videoRecords, $youtube_videoMetaData) = getRecords(array(
      'tableName'   => 'youtube_video',
      'where' => "the_client LIKE '%\t{$case_studiesRecord['the_client']}\t%'",			
      'loadUploads' => true,
      'allowSearch' => false,
      'debugSql' => true,
    ));

Note that this will display some MySQL query data on the screen, so don't leave it enabled on any live pages.

Thanks,

Daniel
Technical Lead
interactivetools.com

By rez - March 22, 2022 - edited: March 22, 2022

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?  

By daniel - March 22, 2022

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,

Daniel
Technical Lead
interactivetools.com

By rez - March 22, 2022 - edited: March 22, 2022

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.

By daniel - March 23, 2022

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,

Daniel
Technical Lead
interactivetools.com

By rez - March 23, 2022 - edited: March 24, 2022

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.

By daniel - March 23, 2022

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,

Daniel
Technical Lead
interactivetools.com