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.
First of all, I'd like to get different class name depending on the values in our database table
There is a SQL command
case when ... then ... else ... end which will help us:
Read and try to understand the following SQL-command:
We select two fields from machines table, ordered by year_of_manufacure.
- First field is just
- Second field contains a
case-whencommand and will return string values depending on given conditions:
- If the year equals
2019, then return the string
- In all other cases return the string
- If the year equals
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
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.