TV-customization (Part 2): More complex value replacement

In Part 1 we have replaced the values of year_of_manufacture by static text "Test". In this Part 2 I am going to replace the values by a concatenated string, containing static string AND table data.

Current Code

Let's start with code from Tutorial Part 1:

// 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;
}

Target of Part 2

I'd like to replace the database values (currenly 2019, 2020, 2021) by concatenated values like Built: 2019 etc.. This means he have to combine (concat) a static string "Built: " (note the blank) with the values of a field.

Step 1: SQL theory first

Let's check the database records first. I am using Adminer as SQL workbench.

SELECT concat('Built: ', year_of_manufacture)
FROM `machines`

This SQL command concat will concatenate all parameters given in brackes, separated by comma.

Note the single quotes, wrapping the static string.

In this case I am concatenating the static text Built: (note the blank) and the dynamic field value of column year_of_manufacture.

So, the SQL-query for this field looks like this:

concat('Built: ', year_of_manufacture)

Step 2: Change TV field value

Now, knowing Part 1 and knowing the SQL-command, let us put it alltogether:

// 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 =  "concat('Built: ', year_of_manufacture)";
            $options_new[$new_value] = $column;
        } else {
            $options_new[$sql] = $column;
        }
    }
    $options->QueryFieldsTV = $options_new;
    return true;
}

In Part 1 you have learned about those double quotes "ArrayKey" for PHP and single quotes 'SQLQUERY' for SQL. Here you can see how I built the $new_value value (see line 10).

Let's check the result by saving and reloading our browser page:

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.

Still interested? See Part 3 of this series.

Kind regards,
Jan

PS: Task for you: Play around with concat function, for example append a static text suffix after the year.