Using (Custom) DB Fields and List Filters for Clips | Advanced Customization | WS.WebTV Documentation | WebTV Solutions

Print

(Custom) Database fields and list filters for Clips

Since WS.WebTV 3.0.2pf1 it is possible to use (custom) database fields and list filters for Clips. This allows a greater degree of customization and will be great for those users which need to handle additional Clip data structures, for specific projects.

Once the new field(s) has(have) been added to the "clips" table of the database (read point 1 before adding!), it is necessary to specify it(them) -and define the corresponding list filters- in the WebTV (mainly) by adding variables into the configuration file (config/Config.inc.php) and editing template files.

We are going to explain how to do it using a practical example.

1: Database modification

Suppose you want to add a custom "country" field to the clips table of the database. Regardless of how do you want to name the new field, we recommend adding a security prefix (Ex. "x_") in order to avoid "collisions" with future versions of the WebTV. Thus, the new custom field to be added, including the security prefix, would be named "x_country".

2: Specifying the field in the WebTV

Once the custom field was added to the "clips" table, you have to tell the system that it must handle it when operating on the DB. This is done through the variable (associative array) $CUSTOM_DB_FIELDS["clips"], by adding a new associative array with the name of the field as index and a data array as value.

Example:
Suppose that you added a string/text field (CHAR, VARCHAR, TEXT, etc.) named "x_country" to the "clips" table. It must be specified in the config/Config.inc.php this way:

$CUSTOM_DB_FIELDS["clips"]["x_country"] = array (
	"data_type"   => "plain_text", // Type of the data stored in the field. Ops: "number" (if it stores numbers), "plain_text" (if it stores normal text), "html" (if it stores text in HTML format) , "timestamp" (if it is a number which corresponds to a Unix timestamp)
	"default_val" => "",           // Default value (can be "NULL" if required), when the field is not defined or when it is not provided when creating/editing/updating the "clips". It is necessary for inserting data in the DB. 
	"output_formatting" => "no",   // Here you specify whether it must be formatted (according to regional settings) when it is printed in the templates. Ops: "no" (printed as is), "date" (date without time), "number" (not decimal), "decimal" (decimal number), "time" (time part of a date), "datetime" (date and time) 
);

It is worth mentioning that once the new field is defined it will be available (by his name) in the Clip edit page/template (Back-End) and in the page/template used for displaying the Clip(Front-End), by using the following labels:

Additionally, the new field will also be included (by his name) in the results of the clips > get API function.

3: Printing the field+value in the Clip edit page (Back-End / Form)

After the previous, you can now modify the Clip edit template (Back-End) in order to store/read the new field, by adding new form elements.

Example:
Suppose that you want to add a drop down menu (a select) for selecting the country, and you want it to appear above the Clip description field... edit the template file public/backend/admin_clip_edit.tpl and add the following (red and blue color) before the div with class "formRow" which wraps the "item_description" (green):

IMPORTANT: Validating the added form components
The fields that you manually add won't be validated by the built-in Javascript validation functions; therefore, you need to validate the User input using your custom Javascript function(s) which must be defined in the same template. Note that if you want to run a final validation when the User clicks the "Save" button and stop the submission if the new fields are not validated then you can define a Javascript function named validateCustomFields() which must return true or false and, based on that return value the main form will continue the submission or will stop it.
Complimentary to the previous, keep in mind that your validation functions can add the "error" CSS class to the form elements (input, textarea, select...) which are not validated; that class will make the form elements to highlight in color red. Additionally, if you need to display an error message you can use the built-in function showResultMessage() - see example.

Example of validation function:

<script>
function validateCustomFields()
{
    $("#x_country").removeClass("error");
    if($("#x_country").val()=="")
    {
      $("#x_country").addClass("error");
      showResultMessage("Oops, you must select a country","error",5000); // 5000 is the time in milliseconds that the message will last in the page
      return false;
    }

return true;
}
</script>

NOTES
- 1: It is worth mentioning that, for printing the value in the page/template, since we are doing it inside a Javascript string, for safety, it is better to use the label {k.html.x_country_escaped}.
- 2: If a special character, with tilde, etc. is not shown correctly, it will be necessary to convert (with the text editor) the encoding of the template file from ANSI to UTF-8.
- 3: You have to repeat the above procedure for the template file public/backend/admin_clip_edit_auto_encoding.tpl (which applies exclusively for Auto-Encoding Clips).

...That's it: You can now edit the Clips and assign the country.

4: Showing the field value on the Clip page (Front-End)

The next step is to modify the Front-End template used for displaying the Clip to the visitors, in order to print the value of new field (if you want to display it...).

Example:
Suppose you want to display the country below the Clip description, but above its categories... edit the template file -used by default, for Clips when the V2 theme is selected- public/frontend/v2/clip.tpl and add the following (blue and red color) before <div class="itemCategoriesTitle" ...:

NOTES
- 1: The label {k.lang.fe.country} prints the word "Country" according to the Front-End language.
- 2: It is worth mentioning that, as a general rule, and if the value of the field won't be used inside a HTML/Javascript quoted string, then you should use the label _formatted for printing it.

5: Showing the field value in lists (Front-End)

5.1: Specifying the field so that the system handles it

If you want the new field(s) to be available for printing in Clip lists (searches, etc) then you must first specify it(them) in the configuration file (config/Config.inc.php) through the variable (associative array) $CUSTOM_DB_FIELDS_LIST_INCLUSION["clips"] assigning an array with the list of custom field(s) to be included:

$CUSTOM_DB_FIELDS_LIST_INCLUSION["clips"] = array("x_country"); // Custom fields that will be included in Clip lists (one or more)

5.2: Showing the field value

After the previous, the field value can now be printed in the lists, for that, you need to include the appropriate label for the corresponding loop structure of the Front-End templates.

Example:
Suppose you want to include the value of the new country field in the Clip lists of the search results, below "views". Edit the template file -used for listing the Clips, Channels and Pages when the V2 theme is selected- public/frontend/v2/search_results.tpl and add the following (red and blue color) after <div class="views...:

NOTE: Since the previous template is valid either for Clips or Channels or Pages then it is necessary to add a JS and CSS code to the head of the Web so that the new div is only displayed in the case of Clips. Add the following into Configuration > Additional HTML Code / <head> section:

<script>
if ( 
  window.location.href.indexOf("/searchChannel/")>1 ||
  window.location.href.indexOf("portal/channels")>1 ||
  window.location.href.indexOf("/channels/")>1 ||
  window.location.href.indexOf("/searchPage/")>1 ||
  window.location.href.indexOf("portal/pages")>1 ||
  window.location.href.indexOf("/pages/")>1
)
{ $("html").addClass("channels"); }
</script>
<style>
.channels .xCountry { display:none; }
</style>

6: Search filters

If you need to create search filters for custom fields, these must be defined in the configuration file (config/Config.inc.php). First you must specify the filter names and then you must create the functions corresponding to each one.

6.1: Creating the filters (specifying the names)

The name of the filters is specified through the variable (associative array) $CUSTOM_DB_FIELDS_LIST_FILTERS["clips"], assigning an array with the list of the filters (names) which will be available:

$CUSTOM_DB_FIELDS_LIST_FILTERS["clips"] = array("xCountryFilter"); // Array with the list of names of the new filters. These names will be passed as POST or GET vars

6.2: Creating the filters (defining the functions)

Once the names have been specified, it is necessary to define the function for each of them. The filter functions will take care of generating the additional SQL clauses which will be included in the SQL query that lists the Clips. Functions are defined as shown below (also in the config/Config.inc.php file) - Example with the country field
NOTE: The function are named this way: prefix "custom_db_fields_list_filter_clips_" + filter name.

function custom_db_fields_list_filter_clips_xCountryFilter()
{
  // For this example, the filter will restrict the list of Clips according with the country value

  // The input value should be found in a GET or POST var
  // NOTE: GET and POST vars are automatically "sanitized" by the system
  $input_value = "";
  if ( isset($_POST["xCountryFilter"])) $input_value = $_POST["xCountryFilter"];
  else if ( isset($_GET["xCountryFilter"])) $input_value = $_GET["xCountryFilter"];

  // Prepare the return array with default values (all blank)
  $clauses=array(
                  "query_select_field" => "", // Ej. "other_table.x_another_field_name" -> Leave blank if you don't need to select data from other table(s) different from "clips"
                  "query_select_from" => "",  // Ej. "other_table" -> Leave blank if you don't need to operate on other table(s) different from "clips"
                  "query_select_where" => ""  // Ej. "clips.xCountryFilter = '%$input_value%'"
              );

  // Validate the input value and generate the additional (SQL) query clauses
  if ($input_value!='')
  {
    $clauses["query_select_where"]= " clips.x_country LIKE '%$input_value%' ";
  }
  
  return $clauses;
}  

That's it: Now the filter is created and when the "xCountryFilter" POST or GET var is passed (to the search form or API clips > list function) the filter will be applied to the list.

6.3: Using the filter with the API

In order to use the filter with the API Clip list function (clips > list), simply pass the name of the filter as POST var.

6.4: Adding the filter to the existing Clip search filters, in the Front-End

Example:
Suppose you want to include the new country filter in the Clip search page (in the Filters section). Edit the template - used by default to include the Clip search filters when the V2 theme is selected- public/frontend/v2/inc_search_filters_clips.tpl and add the following (red and blue color) after <form name="filterForm"... in the desired place:

NOTES
- 1: It is worth mentioning that, for printing the value in the page/template, since we are doing it inside a Javascript string, for safety, it is better to use the label {k.html.xCountryFilter_escaped}.
- 2: If a special character, with tilde, etc. is not shown correctly, it will be necessary to convert (with the text editor) the encoding of the template file from ANSI to UTF-8.
- 3: Labels like {k.lang.fe....} display a text according to the Front-End language: {k.lang.fe.country} = "Country", {k.lang.fe.filter_any} = "Any"

7: Associative/Combined Fields

Associative/combined fields are those which are the result of a "JOIN" between a field of the "clips" table and a field from another table. To explain it with an example: Suppose that the "x_country" field of the examples, instead of storing the name of a country, what stores is the country code (ES, FR, GB, etc.), and the actual country name is specified in another table of the database. Well, this is also possible. Contact for more info on how to do add the additional SQL clauses.