For security reasons AppGini filters out CSS styles if injected into HTML code using PHP hook function. In this article you will find an easy-to-use, depencdency-free javascript fuction for changing background-color of table view cells depending on cell's value.

Common function

Place the following function in <script>...</script> tags in hooks/header-extras.php, for example, so you can re-use it in any table view later on.

function AppGiniTableViewBackgroundChanger(cn, options) {
  jQuery(function() {
    const options_lc = Object.fromEntries(Object.entries(options).map(([key, val]) => [key.toLowerCase(), val]));
    jQuery(`tr[data-id] > td.${AppGini.currentTableName()}-${cn}`).each(function(i, e) {
                jQuery(e).css('background-color', options_lc[jQuery(e).text().trim().toLowerCase()]);
    });
  });
}

Usage

For a specific table view, create a javascript hooks file named /hooks/TABLENAME-tv.js, if not already exists. In there call the function shown above.

AppGiniTableViewBackgroundChanger("COLUMNNAME", {
  "State1": "red",
  "State2": "yellow",
  "State3": "green",
});

Parameters:

1. column name (string)
2. object having states (as keys) and background-colors (as values).

The example above will find all cells of column COLUMNNAME and will set the background-color depending on the cell's text. If, for example, cell's text equals "State1", the background-color will be red. The comparison is case-insensitive, which means, also "state1" and "STATE1" would match and coloured red.

Example

AppGiniTableViewBackgroundChanger("type_id", {
  "Bauabnahme": "orange",
  "Auslieferung": 'red',
});

AppGiniTableViewBackgroundChanger("description", {
  "test": "lightblue",
});

AppGiniTableViewBackgroundChanger("order_id", {
  "1": "red",
  "2": "yellow",
  "3": "green",
});

Output

Now we know how to use an inline-SQL command case-when for dynamically evaluating and returning string values depending on database table values. It's time to put it all together.

Code

Let's return to our PHP editor and clean up the code a bit. I have copied our case-when command of Part 7 and using it now for building our replacement string in PHP, see Lines 9-15.

function machines_init(&$options, $memberInfo, &$args)
{
    $options_old = $options->QueryFieldsTV;
    $options_new = [];

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

        if ($column == 'year_of_manufacture') {
            $css_class = "(case
                year(now()) - year_of_manufacture
                when 0 then 'label label-success'
                when 1 then 'label label-warning'
                when 2 then 'label label-warning'
                else 'label label-danger'
                end)";

            $parts = [
                "'<span class=\"'",
                $css_class,
                "'\">'", 
                "year_of_manufacture",
                "'</span>'",
            ];
			
            $new_value = "concat(" . implode(", ", $parts) . ")";
            $options_new[$new_value] = $column;
        } else {
            $options_new[$sql] = $column;
        }
    }
    $options->QueryFieldsTV = $options_new;
    return true;
}

That's it, let's save and see the results:

And let's check the generated HTML of one of those elements:

Look good. You now have all required bricks for building your own customizations.

Teaser / Preview

If all of this is too complicated for you because you need to know several techniques (HTML, CSS, SQL, PHP), stay tuned, I consider publishing an easy-to-use PHP library. This library will make it much easier, have a short look, the code below produces exactly the same output with less code and is more readable:

// file: hooks/machines.php
function machines_init(&$options, $memberInfo, &$args)
{
    $table_highlighter = AppGiniHelperTVHighlighter::create("machines", $options);
    $column_highlighter = $table_highlighter->getColumnHighlighter("year_of_manufacture");
    $column_highlighter
        ->if("year_of_manufacture <= year(now())-3")->then("label label-danger")
        ->if("year_of_manufacture <= year(now())-1")->then("label label-warning")
        ->else("label label-success");

    $table_highlighter->apply();
    return true;
}

And that's the output of the Highlighter-code above:

If you are interested in such an easy-to-use library, please contact me so I will see if there is enough demand.

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

In Part 6 we have learned how to add custom CSS classes and assign them to TV cell values. But up to now we have assigned exactly one CSS class. In this part we are going to bring some more flexibility into it. Before we can apply different CSS-classes depending on the cell value we need to know how to evaluate the database and return different strings depending on conditions per row.

Some SQL basics

Let's have a look at the database table first. As mentioned before I am using Adminer as database workbench.

Target

First of all, I'd like to get different class name depending on the values in our database table machines.

SQL command case-when-then-else-end

There is a SQL command case when ... then ... else ... end which will help us:

Read and try to understand the following SQL-command:

Explanation

We select two fields from machines table, ordered by year_of_manufacure.

• First field is just year_of_manufacure.
• Second field contains a case-when command and will return string values depending on given conditions:
  • If the year equals 2019, then return the string old.
  • In all other cases return the string -undefined-.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END AS 'COLUMN_NAME'

There is a good tutorial here.

Let's go a bit further, and don't worry, there will be a more flexible solution later on without hard-coded years.

SELECT 
year_of_manufacture, 
case 
when year_of_manufacture <= 2019 then 'old'
when year_of_manufacture <= 2021 then 'ok'
else 'brand new' 
end as 'css_class_name'
FROM `machines`
order by year_of_manufacture

This works well, but I don't want to hard-code the years here. I need some more flexibility, for example: machines older than 3 years from now.

Check out the next SQL command:

SELECT 
year_of_manufacture, 
year(now()) - year_of_manufacture  as 'age_in_years',
case year(now()) - year_of_manufacture 
when 0 then 'brand_new'
when 1 then 'ok'
when 2 then 'ok'
else 'old'
end as 'css_class_name'
FROM `machines`
order by year_of_manufacture

We are close. Our SQL command uses year(now()). This means we don't have to hard-code any year into our SQL statement any longer.

Let's just change the output labels a bit, because in your TV, later on, I'd like to use the output as CSS class-name directly. Also, we don't need the first two columns and longer but just the evaluated class name:

SELECT 
case year(now()) - year_of_manufacture 
when 0 then 'label label-success'
when 1 then 'label label-warning'
when 2 then 'label label-warning'
else 'label label-danger'
end as 'class_name'
FROM `machines`
order by year_of_manufacture

And this is the case-part we will need in next step:

case year(now()) - year_of_manufacture 
when 0 then 'label label-success'
when 1 then 'label label-warning'
when 2 then 'label label-warning'
else 'label label-danger'
end

What's next?

That's enough for today. You have learned how to use SQL's case-when command. Next we can put all together, PHP code in hooks/machines.php and SQL code for conditionally highlighting TV cell values. See Part 8, the final part, including a teaser for an easy-to-use PHP function.

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