Skip to content
Advertisement

PHPMaker 2019 | Dynamic caption based on data in database

In PHPMaker (v2019) i have the following need:

Generate a caption for an column which contains a dynamic part – the year. Currently I’m using the system-date with following code:

$this->COLUMN_IN_DATABASE ->Caption .= "MyColumnName " . (date("Y") + 0)

This works finde but i need change this to an value in the database. There is an column which contains the year and an month like this:

YYYY-MM

Therefore I need to extract the YYYY and use it in the caption.

Any ideas?

Advertisement

Answer

The solution is:

Put the following code in the top of “PageLoad” event:

    ($YEAR = ExecuteScalar ("select DISTINCT (CAST((SUBSTR(YEARANDMONTH,0,4)) AS NUMBER(4))) YEAR FROM TABLE WHERE YEARANDMONTH = (SELECT MAX(YEARANDMONTH) FROM TABLE )"));
    ($MONTHNUM= ExecuteScalar ("select DISTINCT (CAST((SUBSTR(YEARANDMONTH,6,2)) AS NUMBER(4))) MONTHNUMBER from TABLE WHERE YEARANDMONTH= (SELECT MAX(YEARANDMONTH) FROM TABLE )"));

Then, after that, you can use the results in every column caption of your choice like this: (We assume the result of the above mentioned SQL-result is “2021” as $YEAR and “5” as $MONTHNUM)

    $this->YEAR2 ->Caption .= "In two Years it will be the year " . (($YEAR)+2) . " and the current month number is " . ($MONTHNUM);

Then the caption if the “YEAR2”-column will be: “In two Years it will be the year 2023 and the month number is 5”

Extra: In my case I had in some columns the need to display the full month name based on the month number. So i have added the third line of code in the top section:

    ($MONTHNAME = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]);

And to display the month name the code is like this: (We assume the result of the above mentioned SQL-result is “2021” as $YEAR and “5” as $MONTHNUM)

    $this->YEAR2 ->Caption .= "In two Years it will be the year " . (($YEAR)+2) . " and the month name will be " . ($MONTHNAME[$MONTHNUM-1]);

Then the caption if the “YEAR2”-column will be: “In two Years it will be the year 2023 and the month name will be May”

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement