TV-customization (Part 1): Change Value

There are many requests on AppGini forum for customizing Table View columns to your own needs. Especially many of you would like to change the table-cell-style depending on certain values, for example highlight a cell in red under certain conditions. Well, there has been a change recently: AppGini now removes custom style-attributes for security reasons. In this tutorial I am going to show how to reach the goal anyway. I will start with this Part 1 where I am going to show how to just replace values.

If you are interested, just search the internet for "Cross Site Scripting (XSS) Software Attack".

Model: machines-table

For this sample I am going to use machines table which looks like this in AppGini project:

There is a column named year_of_manufacure which I will use for demonstration purposes.

Demo-Data

The column just contains years from 2019 to 2021 as demo-data.

Target of Part 1

I am going to focus in the column machines.year_of_manufacture now. In this part 1 I am just going to replace the value of column year_of_manufacture by the static value "Test". Later on I am going to change display style of cells.

Step 1: Check current status

Before starting I usually check the exact keys and values by dumping out the TV column array:

function machines_init(&$options, $memberInfo, &$args)
{
    var_dump($options->QueryFieldsTV);
    exit;
}

This is the result shown in the browser after reloading the machines-TV page:

...\hooks\machines.php:7:
array (size=6)
  '`machines`.`id`' => string 'id' (length=2)
  'IF(    CHAR_LENGTH(`partners1`.`label`), CONCAT_WS('',   `partners1`.`label`), '') /* Geschäftspartner */' => string 'partner_id' (length=10)
  '`machines`.`serialnumber`' => string 'serialnumber' (length=12)
  'IF(    CHAR_LENGTH(`machinetypes1`.`name`), CONCAT_WS('',   `machinetypes1`.`name`), '') /* Typ */' => string 'type_id' (length=7)
  'IF(    CHAR_LENGTH(`partners2`.`label`), CONCAT_WS('',   `partners2`.`label`), '') /* Hersteller */' => string 'vendor_label' (length=12)
  '`machines`.`year_of_manufacture`' => string 'year_of_manufacture' (length=19)

It is important to understand the array, so let me give you a picture:

Structure of the array

$options->QueryFieldsTV["SQL_QUERY"] = "COLUMN_NAME";

Attention

In a way this seems strange, because this means we cannot just replace the value. It would be more readable if you could just do this: $options->QueryFieldsTV["year_of_manufacture"] = "'Test'"; but we cannot and must not!

So, always remember:

• If we want to find a column by name in our array, we have to compare the value, not the key.
• If we want to change the value of a column, we have to change the key, not the value.

As I said: This sounds strange, but this is the way it works in current AppGini version.

So, instead of your changing the values of our array, we can build a new array from the existing, unchanged entries plus entries for our changed columns.

Step 2: Boilerplate

Before changing anything, I'd like to give a boilerplate code to you. The following code just copies all columns into a new array.

// file: hooks/machines.php
function machines_init(&$options, $memberInfo, &$args)
{
    $options_old = $options->QueryFieldsTV;
    $options_new = [];

    foreach ($options_old as $sql => $column) {
            $options_new[$sql] = $column;
    }
    $options->QueryFieldsTV = $options_new;
    return true;
}

We iterate through all TV-fields and just copy entries from the existing array into a new array. This will not change anything in the UI. Just check in your browser and ensure that you did not do any typing or syntax errors!

Step 3: Find column and change it

Now it's time to find our column and change the displayed value.

I am using the boilerplate code and just adding an if () { ... } else { ... }.

// file: hooks/machines.php
function machines_init(&$options, $memberInfo, &$args)
{
    $options_old = $options->QueryFieldsTV;
    $options_new = [];

    foreach ($options_old as $sql => $column) {

        if ($column == 'year_of_manufacture') {
            $new_value =  "'Test'";
            $options_new[$new_value] = $column;
        } else {
            $options_new[$sql] = $column;
        }
    }
    $options->QueryFieldsTV = $options_new;
    return true;
}

If we have found the column (see condition in line 9) we do not just copy the entry into our new array, but we insert a different key-value pair into our new array (see lines 10 and 11).

All the other fields (see else-condition in line 12) will be just copied into our new array (see line 13).

Important

Remember the array-structure?

$options->QueryFieldsTV["SQL_QUERY"] = "COLUMN_NAME";

PHP: In this case PHP requires a string as array-key. So, SQL_QUERY has been wrapped in double quotes.

SQL: If we have static strings in SQL, we need to wrap them in single quotes in SQL.

This means our key contains a PHP string which represents a SQL string:

$new_value =  "'Test'";
$options_new[$new_value] = $column;

For readablity reasons I have added two lines here, but you can also place it in one line:

$options_new["'Test'"] = $column;

It does not matter which way you prefer. It is important you unserstand that the innermost part (in single quotes) 'Test' will be passed over to your SQL database server and that the outer (double) quotes wrap the array key for PHP. This will be important later on when we try to concatenate static strings with SQL function or SQL columns.

So, let's save and reload our browser:

Step 4: Check HTML

Finally, let's check the HTML output in our browsers' dev-tools. Use Inspect context menu item on one of the three cells:

That's it for now.

If you like the way I try to help you, please consider following me on Twitter, purchasing our products or donating a coffee. All of this will help me, helping you in the future.

Kind regards,
Jan

PS: Stay tuned for Part 2 See Part 2 of this tutorial.