In this tutorial we will cover all non-trivial techniques that made this project possible. As you can imagine, the main challenge making this project look sleek and easy to use and I believe we achieved this goal.

See the final leaderboard online

We will talk about:

1. Creating image-based edit controls
2. Grouping team pickers and preventing duplicate selections
3. Enforcing one prediction per user with PHPRunner events
4. Disabling Add and Edit after the submission deadline
5. Enabling Save only after all required answers are selected
6. Calculating scores and rankings with custom SQL
7. Adding rank graphics, progress bars, and current-user highlighting
8. Integrating custom landing, scoring, and thank-you pages
9. A few more visual tweaks
10. How to create your own custom Edit controls easily

Download project for PHPRunner

1. Creating image-based edit controls with table and SQL lookups

I think this is the most important part. We introduced three image-based custom edit controls that you can use in other applications as well. All three are available for download on our marketplace. Right now it is for PHPRunner only but we will provide ASPRunner.NET equivalents very soon. We will also teach you how to create new custom Edit controls using ChatGPT.

ImagePicker

ImagePicker on marketplace

This control allows a selection of a single country. We display all countries and their flags in a scrollable popup and allow to select one. Once country is selected the popup is closed.

ImageDropdown

ImageDropdown on marketplace

This is an advanced version of a regular dropdown box. We have used it to select podium teams and top scorer players. Displaying country flag next to player name definitely makes it easier to choose the player you want.

ImageChoice

ImageChoice on marketplace

This is something that resembles a regular radio-button control, it just looks nicer. We have used to allow a selection of the host team that advances further.

2. Grouping team pickers and preventing duplicate selections

The Top 8 prediction is stored in eight fields: top8_team1_id through top8_team8_id. Without additional logic, eight independent lookup controls would allow the same country to be selected several times. The project solves this inside the custom edit control by assigning all eight controls to one group.

Each Top 8 field uses the following group settings:

$this->settings["picker_group"] = "top8";
$this->settings["max_group_count"] = 8;
$this->settings["status_element_id"] = ["status_element_id"];
$this->settings["prevent_duplicates"] = 1;

The group name tells the controls that they belong to the same logical question. When a country is selected in one field, duplicate prevention makes it unavailable to the other seven. The shared status element (“status_element_id”) can report progress across the whole group rather than treating each field as an unrelated input. You can see status field saying something like “7 of 8 selected” at the top right corner of the first section.

The podium fields use the same idea with a different group:

$this->settings["picker_group"] = "podium";
$this->settings["prevent_duplicates"] = 1;

Champion, second place, and third place remain separate database columns, but they behave as one coordinated set of choices. This is useful in many PHPRunner projects: room assignments, ranked preferences, product bundles, team selection, or any form where several fields use the same type of lookup wizard but must not repeat the same value.

3. Enforcing one prediction per user with PHPRunner events

Every participant should have one prediction record. Hiding the Add button after submission is not sufficient because the user could revisit the Add URL directly. The project checks for an existing record in the prediction table’s Before Process Add event.

$currentUser = Security::getUserName();

$sql = DB::PrepareSQL(
    "SELECT p.id
     FROM wc_predictions p
     INNER JOIN wc_users u ON u.id = p.user_id
     WHERE u.email = ':1'
     LIMIT 1",
    $currentUser
);

$existingId = DB::DBLookup($sql);

if ($existingId) {
    header(
        "Location: wc_predictions_edit.php?editid1="
        . rawurlencode($existingId)
    );
    exit();
}

Security::getUserName() returns the logged-in email because email is configured as the PHPRunner username field. The query translates that login identity into a prediction record ID. If the record exists, the user is redirected to Edit instead of being allowed to create another row.

The project also performs the same lookup in the Menu event. A normal user who reaches the menu is sent to Edit when a prediction exists or Add when it does not. The Menu event improves navigation; the Before Process Add event enforces the rule at the page boundary.

4. Disabling Add and Edit after the submission deadline

Prediction forms must close at a known time. Instead of only hiding buttons, the project changes the table permission string in the Table permissions event. This affects PHPRunner’s permission checks for every request. The following code was added to wc_predictions table GetTablePermissions event.

$lockDate = "2026-06-28 00:00:00";

if (date("Y-m-d H:i:s") >= $lockDate) {
    $permissions = str_replace(
        array("A", "E"),
        "",
        $permissions
    );
}

return $permissions;

PHPRunner represents table operations with letters in the permission string. Removing A and E disables Add and Edit while leaving allowed read operations intact. This is stronger than CSS or JavaScript because it is evaluated on the server.

The project also detects direct requests for wc_predictions_add.php or wc_predictions_edit.php and redirects them to a read-only page. That redirect is useful feedback, but the permission change is the actual security control.

The example has a hard-coded date. A reusable implementation should read the deadline and timezone from an application settings table. It should also locate the current user’s prediction ID before redirecting to View so the destination contains a valid editid1 key.

5. Enabling ‘Save’ button only after all required answers are selected

PHPRunner’s normal validation works field by field. This form also needs a page-level rule: the participant should not submit until every required prediction category is complete. The Add and Edit JavaScript OnLoad events monitor the required controls and toggle the Save item.

var requiredFields = [
    "top8_team1_id", "top8_team2_id",
    "top8_team3_id", "top8_team4_id",
    "top8_team5_id", "top8_team6_id",
    "top8_team7_id", "top8_team8_id",
    "champion_team_id",
    "second_place_team_id",
    "third_place_team_id",
    "top_scorer_id",
    "final_goes_to_penalties",
    "host_team_goes_furthest_id"
];

function allRequiredAnswered() {
    return requiredFields.every(function(fieldName) {
        var ctrl = Runner.getControl(pageid, fieldName);
        return ctrl && $.trim((ctrl.getValue() || "") + "") !== "";
    });
}

function refreshSaveButton() {
    var button = pageObj.getItemButton("add_save");
    button.toggleClass("disabled", !allRequiredAnswered());
}

The important detail is that the page contains both standard PHPRunner inputs and custom image controls. Listening only to ordinary change events is not enough, so the project also listens for interaction with the custom-control elements:

$(document).on(
    "change keyup click",
    "input, select, textarea, .ip-control, .idrop-control, .ichoice-card",
    function() {
        setTimeout(refreshSaveButton, 50);
    }
);

The short delay lets the custom editor update its PHPRunner control value before the page checks it. The Edit page uses the same code with the edit_save item ID. The script also adds min=”0″ and max=”10″ to the final-score inputs.

This improves the user experience, but it is not a replacement for server-side validation. Required values and numeric ranges should still be checked in Before Add and Before Edit before the record is written.

6. Calculating scores and rankings with custom SQL

The Leaderboard is implemented as a custom view. We wanted to perform the whole score calculation in SQL query so it looks quite tricky.

Top 8 selections are unordered. Each predicted team is tested against all eight actual Top 8 fields:

CASE
    WHEN p.top8_team1_id > 0
     AND p.top8_team1_id IN (
        NULLIF(a.top8_team1_id, 0),
        NULLIF(a.top8_team2_id, 0),
        NULLIF(a.top8_team3_id, 0),
        NULLIF(a.top8_team4_id, 0),
        NULLIF(a.top8_team5_id, 0),
        NULLIF(a.top8_team6_id, 0),
        NULLIF(a.top8_team7_id, 0),
        NULLIF(a.top8_team8_id, 0)
    )
    THEN 1 ELSE 0
END

NULLIF(…, 0) prevents an unanswered actual-result field stored as zero from being treated as a legitimate team. The pattern is repeated for all eight predicted positions and then multiplied by five for the point score.

The exact-position categories use direct comparisons with their own weights:

CASE
    WHEN a.champion_team_id > 0
     AND p.champion_team_id = a.champion_team_id
    THEN 25 ELSE 0
END
+
CASE
    WHEN a.second_place_team_id > 0
     AND p.second_place_team_id = a.second_place_team_id
    THEN 15 ELSE 0
END
+
CASE
    WHEN a.third_place_team_id > 0
     AND p.third_place_team_id = a.third_place_team_id
    THEN 10 ELSE 0
END

The remaining categories add points for top scorer, both final-score values, penalties, and the host nation progressing furthest. The result is exposed as normal Leaderboard fields, allowing PHPRunner to build a List page over calculated data.

The exported query orders by points DESC. If the published rules promise additional tie breakers—correct picks and then submission time—the ORDER BY should explicitly include them:

ORDER BY points DESC, correct_picks DESC, submitted_at ASC

7. Adding rank graphics, progress bars, and current-user highlighting

The SQL produces leaderboard data, but the List page turns it into a competition display. Before processing the List page, an event resets the rank counter and removes any previous current-user standing from the session:

$_SESSION["rank"] = 1;
unset(
    $_SESSION["leaderboard_current_rank"],
    $_SESSION["leaderboard_current_points"],
    $_SESSION["leaderboard_current_player"]
);

Before each row is displayed, the project compares the row’s player with the current user’s display name. When they match, it stores the rank and points for the standing panel and highlights the row:

$userData = Security::currentUserData();

if ($data["player"] == $userData["display_name"]) {
    $_SESSION["leaderboard_current_rank"] = $_SESSION["rank"];
    $_SESSION["leaderboard_current_points"] = $data["points"];
    $record["css"] = "background:#fff7dc;";
}

$_SESSION["rank"]++;

Custom field formats replace raw values with visual components. Positions 1, 2, and 3 use gold, silver, and bronze images. The correct_picks value becomes both text and a progress bar:

$percent = $value * 100 / 16;

$value = '<div class="wc-correct-picks">
    <span class="wc-correct-picks-value">'.$value.' / 16</span>
    <span class="wc-progress-track">
        <span class="wc-progress-fill" style="width:'.$percent.'%"></span>
    </span>
</div>';

Two Page Designer snippets complete the page. The header displays the latest actual-answer update time, and the standing snippet reads the session values to show “Your current standing: #N with N points.” This is a useful example of coordinating List events, custom field formats, session state, snippets, images, and CSS on one generated page.

For a larger application, compare users by an immutable user ID rather than display_name. Display names can change and may not be unique.

8. Integrating custom landing, scoring, and thank-you pages

Not every page in a PHPRunner application must be generated from a database table. This project carries standalone files in the export’s files/ directory. PHPRunner copies them into the output folder when the application is built.

index.php is the public homepage/landing page. It explains the challenge, dates, prizes, and entry process, then links visitors to PHPRunner’s register.php and login.php. scoring.php explains the rules implemented by the Leaderboard SQL. thankyou.php provides a deliberate completion screen after the participant saves the initial prediction.

The connection back to PHPRunner happens through events and ordinary URLs. For example, the prediction After Add event sends the browser to the standalone confirmation page:

header("Location: thankyou.php");
exit();

Registration uses the opposite direction: a generated PHPRunner page sends the new account into the prediction workflow after assigning its role and logging it in:

// Before Register
$userdata["usertype"] = "user";
return true;

// After Register
Security::loginAs($userdata["email"]);
header("Location: wc_predictions_add.php");
exit();

The exported files also include include/wc_functions.php, which is loaded by the global initialization event. It provides shared rendering functions used by prediction View and leaderboard formats, allowing generated pages to display the same flags and labels as the custom controls.

This approach keeps authentication, permissions, and data entry inside PHPRunner while allowing marketing, rules, and confirmation pages to have purpose-built layouts. The important maintenance rule is to keep URLs, asset paths, and shared includes consistent between the standalone files and generated output.

9. A few more visual tweaks

Yes/No switch on Add/Edit pages

We decided that using a new Edit control for such a simple task would be an overkill. Instead, we used a simple radio-button control for ‘Final goes to penalty?’ field and a bit of CSS and Javascript OnLoad event of Add/Edit pages. Javascript basically assigns CSS class to radio-button control and the styling itself is done in CSS.

$(function() {
    $('input[name^="radio_final_goes_to_penalties_"]')
        .closest('.rnr-horizontal-lookup')
        .addClass('wc-penalty-toggle');
});

Check classes that start with .wc-penalty-toggle in Custom CSS.

One more tweak, the display of correct_picks field on leaderboard page. It is implemented as ‘View as’ Custom and use some additional CSS classes. Here is the code:

$value = '<div class="wc-correct-picks">
    <span class="wc-correct-picks-value">'.$value.' / 16</span>
    <span class="wc-progress-track">
        <span class="wc-progress-fill" style="width: '. $value*100/16 .'%;"></span>
    </span>
</div>';

In this code 16 is the total number of picks and this is how we calculate the percentage of correct picks.

10. How to create your own custom Edit controls easily

• Download this skill pack
• Start a new ChatGPT conversation
• Upload the ZIP
• Tell ChatGPT: “Use the PHPRunner custom Edit controls skill from the uploaded ZIP.”
• Then describe the control you want. For instance:

  Use the PHPRunner custom Edit controls skill.
  
  Create a custom Edit control called CurrencyInput.
  
  Purpose:
  A currency-friendly numeric input that formats values instantly for display, while submitting a clean numeric value to the database.
  
  Requirements:
  - User can type 1234.5 and see $1,234.50.
  - Submitted value should be 1234.50, without currency symbol or thousand separators.
  - Support configurable currency_symbol, symbol_position, decimal_places, thousand_separator, decimal_separator, allow_negative, format_on_load, and format_while_typing.
  - Empty value should remain empty.
  - Must work with normal Add/Edit form submission.
  - Provide PHP, JavaScript, CSS, sample.php, and README.txt.
  - sample.php must contain settings code only, without PHP opening/closing tags.
  - Package files at the root of a ZIP archive.

Enjoy!

Find trial version download links below. If you are a customer with a valid maintenance, you can download registered version 11.3 via your control panel.

You can watch this video on YouTube that explains how to use new features.

Trial version download links

1. AI features. Help with writing the code ( PHP, C#, Javascript, CSS, SQL ).
More info in the manual
Video on AI features from Corrie

2. Intellisense. Code completion in all areas where code can be edited.
More info

3. Save project as template
More info

4. Export/import of individual objects between projects. We you export a table in includes everything that is relevant: field settings, page designs, events etc.
More info

5. Small things. Adding search to all dialogs where more than a dozen objects is listed: list of projects, list of buttons, or code snippets etc.

6. New cloud providers: Google Cloud and Azure Blob Storage
Google Cloud Storage info
Azure Blob Storage info

Enjoy!

All others can find trial version links below.

Trial version download links

PHPRunner 11.2 for Windows trial
  
ASPRunner.NET 11.2 for Windows trial

PHPRunner 11.2 for Mac trial

PHPRunner 11.2 for Linux (Debian) trial

PHPRunner 11.2 for Linux (RedHat) trial

You can install and run it side by side with versions 10.x and 11.1. Existing software functionality will not be affected. Just make sure you do not launch previous version and v11.2 at the same time if you use a built-in database to store projects.

This new version comes with ten improvements.

1. Cards layout on the List page

Similar to vertical or columns layout but lightweight. No field names, just the data. Fixed width and height. View page of the record can be open by click on the whole card on on one of the fields. Customization: list of fields, layout, fonts, colors, paddings, borders, rounded corners.

2. Two new visual themes

New visual themes are named Futuro (light) and Obsidian (dark). You can choose them on the Style Editor screen. They are modern, sleek and lightweight.

3. Three new welcome page layouts

New Welcome page layouts are called Nebula, Optima and Sonic. You can switch between them while on the Page Designer screen and menu page is open. Here is an example of how Nebula layout looks.

4. Dashboards tabs

You can combine several dashboard elements into a single one and use tabs to switch between them.

5. Dashboard widgets

Similar to dashboard code snippets but easier to customize.

6. SMTP with OAuth support

At this moment we support Gmail Workplace and Microsoft Exchange accounts.

7. “Javascript Create TinyMCE” event for TinyMCE customization

This new event allows you to customize TinyMCE editor behavior across the whole project.

8. ‘View as’ option Copy value to clipboard

This option can be enabled for any text field under ‘View as’ settings.

9. Foldable elements in dashboards

All dashboard elements can be folded by clicking on their header.

10. Show details key column as a hyperlink

Hyperlink points to the View page of the master and can be open in the same window, in a new window or in a popup. The same functionality for the Lookup Wizard field in view mode. On screenshot below OrderID is a foreign column in ‘Order Details’ table. It is set to open master table data in a popup.

Enjoy!

More details on what is new in version 11.2.

1. Cards layout

Cards layout is a compact way to present the information on the List page. Consistent design, all cards look the same, automatically responsive, adjusts to the screen size. No extra information like field names. Previously this required some trickery and now this is a built-in feature. Take a look at this example.

Cards layout can be customized in the Page Designer. You can choose which fields will be displayed and what are their types: image, title, subtitle, price etc.

2. Dashboard improvements

Two main new features are tabbed elements and foldable elements. You can now fit even more info into a single dashboard using tabbed elements. This is how it looks in the generated application. You can see ‘Customers’ tabbed section including multiple elements. You can also see ‘Alphabetical list pf products’ element being collapsed.

And here is how it looks in the Page Designer. You can see tabbed section on the left side and properties like ‘foldable’ and ‘closed initially’ on the right.

Creating Key Vault in Azure

1. Create an application in Azure. Write down tenantId, clientId and clientSecret values.

2. Logon to Azure and create a subscription. You need to specify subscription name and select a billing account.

More info:
https://learn.microsoft.com/ru-ru/azure/cost-management-billing/manage/create-subscription

3. Go back to portal home and proceed to Key Vaults. Click ‘Create’. Select a subscription, select a Resource group ( or create a new one ). Enter Key Vault name.

4. Now it is the time to assign access permissions. Under the Key Vault you just created proceed to ‘Access control (IAM)’. Add -> Add Role assigment, select ‘Key Vault Certificates Officer’ using search option, click ‘Next’. Select ‘User, group, or service principal’

Click ‘Select members’ search for your application name and select it. . в поисковой строке справа найти свое приложение (не пользолвателя). Next, Review + assign.

Permissions may take a bit of time to be applied but in our situation it worked right away.

5. Create a secret. The secret is the actual object that stores the sensitive information, database password in our case. Select your Key Vault and under Objects -> Secrets -> Generate/Import enter the name of the secret ( “pass” in our case ) and its value.

Now we are ready to use this in our code.

Using Key Vault in PHPRunner

1. Under Style Editor -> Custom Files add a new file named keyvault.php. Paste the code below and use your own values of tenantId, clientId and clientSecret.

<?php
function getSecret($secretName){
	$tenantId = '...';
	$clientId = '...'; 
	$clientSecret = '...';
	$vaultUrl = "https://xkvault.vault.azure.net/";

	$url = "https://login.microsoftonline.com/".$tenantId."/oauth2/token";
	$parameters = array(
		'grant_type' => 'client_credentials',
		'client_id' => $clientId,
		'client_secret' => $clientSecret,
		'resource' => 'https://vault.azure.net'
	);
	$headers = array("Content-Type"=>"application/x-www-form-urlencoded");
	$response = runner_post_request($url, $parameters, $headers);

	$json = runner_json_decode($response["content"]);
	if (!isset($json['access_token'])) {
		throw new Exception("Failed to get access token: " . $response);
	}
	$accessToken = $json['access_token'];

	$url = $vaultUrl . "secrets/".$secretName."?api-version=7.3";

	$headers = array(
		"Authorization"=>"Bearer ".$accessToken,
		"Content-Type"=>"application/json"
	);
	$response = runner_http_request($url, "", "GET", $headers);
	$result = runner_json_decode($response["content"]);
	return $result['value'];
}
?>

2. An example of using Key Vault in your application. We will create a new Server Database Connection and will retrieve database password from Key Vault. In this example we use MySQL database.

include("keyvault.php");
$host="localhost";
$user="root";
$pwd=getSecret("pass");
$port="";
$sys_dbname="cars";

Using Key Vault in ASPRunner.NET

1. Under Style Editor -> Custom Files add a new file named keyvault.cs. Paste the code below and use your own values of tenantId, clientId and clientSecret.

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Reflection;
using runnerDotNet;
namespace runnerDotNet
{
	public partial class CommonFunctions
	{
		public static XVar getSecret(dynamic secretName)
		{
			dynamic accessToken = null, clientId = null, clientSecret = null, headers = null, json = XVar.Array(), parameters = null, result = XVar.Array(), tenantId = null, url = null, var_response = XVar.Array(), vaultUrl = null;
			tenantId = new XVar("...");
			clientId = new XVar("...");
			clientSecret = new XVar("...");
			vaultUrl = new XVar("https://xkvault.vault.azure.net/");
			url = XVar.Clone(MVCFunctions.Concat("https://login.microsoftonline.com/", tenantId, "/oauth2/token"));
			parameters = XVar.Clone(new XVar("grant_type", "client_credentials", "client_id", clientId, "client_secret", clientSecret, "resource", "https://vault.azure.net"));
			headers = XVar.Clone(new XVar("Content-Type", "application/x-www-form-urlencoded"));
			var_response = XVar.Clone(MVCFunctions.runner_post_request((XVar)(url), (XVar)(parameters), (XVar)(headers)));
			json = XVar.Clone(CommonFunctions.runner_json_decode((XVar)(var_response["content"])));
			if(XVar.Pack(!(XVar)(json.KeyExists("access_token"))))
			{
				new Exception((XVar)(MVCFunctions.Concat("Failed to get access token: ", var_response)));
			}
			accessToken = XVar.Clone(json["access_token"]);
			url = XVar.Clone(MVCFunctions.Concat(vaultUrl, "secrets/", secretName, "?api-version=7.3"));
			headers = XVar.Clone(new XVar("Authorization", MVCFunctions.Concat("Bearer ", accessToken), "Content-Type", "application/json"));
			var_response = XVar.Clone(MVCFunctions.runner_http_request((XVar)(url), new XVar(""), new XVar("GET"), (XVar)(headers)));
			result = XVar.Clone(CommonFunctions.runner_json_decode((XVar)(var_response["content"])));
			return result["value"];
		}
	}
}

2. In BeforeConnect event use the following code:

dynamic pass = CommonFunctions.getSecret("pass");
GlobalVars.ConnectionStrings["conn"] = MVCFunctions.Concat("Server=localhost;Database=cars;User Id=root;Password=",pass);

This approach will involve the following steps
1. Creating and populating database tables to store triggers ( when to apply the logic ) and actions ( what happens when trigger goes off )
2. Server-side PHP and C# code that passes this data to Javascript
3. Javascript code itself that listens to “change” event and implements the logic defined in the database.

Database tables

Triggers table. The structure is fairly simple. You can see that we store table name, page type, field name and what event we are listening to. In this article we will only show how to implement the most common “change” event as it covers 95% of required functionality.

Actions table is a details one while triggers is a master. They are linked by trigger_id field. This table knows what condition to check and what action to perform. Lets take a look at the first row and try to decipher it.

The action is tied to trigger #1 ( change event of Country field on the Edit page of customers table). The condition is equal and the condition_value is ‘USA’. Which means that when Country field equals ‘USA’ we should proceed with our action. And the action itself is showing of the Region field.

And here is the SQL script that will create both tables for you with sample data.

CREATE TABLE IF NOT EXISTS `actions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `condition` varchar(250) DEFAULT NULL,
  `target` varchar(250) DEFAULT NULL,
  `action` varchar(250) DEFAULT NULL,
  `condition_value` varchar(250) DEFAULT NULL,
  `trigger_id` int DEFAULT NULL,
  KEY `Index 1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `actions` (`id`, `condition`, `target`, `action`, `condition_value`, `trigger_id`) VALUES
	(1, 'equals', 'Region', 'show', 'USA', 1),
	(2, 'empty', 'ContactTitle', 'hide', NULL, 2),
	(3, 'notepmty', 'ContactTitle', 'readonly', NULL, 2),
	(4, 'notempty', 'ContactTitle', 'show', NULL, 2),
	(5, 'notequals', 'Region', 'hide', 'USA', 1),
	(6, 'notempty', 'Fax', 'disable', NULL, 2),
	(7, 'empty', 'Fax', 'enable', NULL, 2),
	(8, 'equals', 'Region', 'clear', 'USA', 1),
	(9, 'equals', 'Region', 'focus', 'USA', 1),
	(10, 'equals', 'Region', 'require', 'USA', 1);

CREATE TABLE IF NOT EXISTS `triggers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `table` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `page` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `triggers` (`id`, `table`, `page`, `event`, `field`) VALUES
	(1, 'customers', 'edit', 'change', 'Country'),
	(2, 'customers', 'edit', 'change', 'ContactName');

Server-side code

Server-side code goes to BeforeDisplay event of each page where this functionality needs to be implemented. In our situation this code goes to BeforeDisplay event of Customers table Edit page. The code itself is fairly straightforward. It just dumps all the data from actions and triggers tables and makes this data available in Javascript via proxy object.

Note that for better code structure you need to create an external PHP or C# file, define a function there and place this code into that function. Then in all BeforeDisplay events

PHP code

$table = $pageObject->tName;
$page = $pageObject->pageName;

// get all the triggers for the current page and send it to Javascript
$sql = DB::PrepareSQL("select * from triggers WHERE `table`=':1' and page=':2'",
    $table, $page);

$triggers=array();

$rs = DB::Query($sql);
while( $data = $rs->fetchAssoc() )
{
    $triggers[] = $data;
}

// do the same for actions table

$sql = DB::PrepareSQL("SELECT * FROM actions WHERE trigger_id IN (select id from triggers WHERE `table`=':1' and page=':2'",
    $table, $page);

$actions=array();

$rs = DB::Query($sql);
while( $data = $rs->fetchAssoc() )
{
    $actions[] = $data;
}

$pageObject->setProxyValue("triggers", $triggers);
$pageObject->setProxyValue("actions", $actions);

C# code

dynamic actions = XVar.Array(), page = null, sql = null, triggers = XVar.Array();
table = XVar.Clone(pageObject.tName);
page = XVar.Clone(pageObject.pageName);
sql = XVar.Clone(DB.PrepareSQL(new XVar("select * from triggers WHERE `table`=':1' and page=':2'"), (XVar)(table), (XVar)(page)));
triggers = XVar.Clone(XVar.Array());
rs = XVar.Clone(DB.Query((XVar)(sql)));
while(XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
	{
	triggers.InitAndSetArrayItem(data, null);
	}
sql = XVar.Clone(DB.PrepareSQL(new XVar("SELECT * FROM actions WHERE trigger_id IN (select id from triggers WHERE `table`=':1' and page=':2'"), (XVar)(table), (XVar)(page)));
actions = XVar.Clone(XVar.Array());
rs = XVar.Clone(DB.Query((XVar)(sql)));
while(XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
	{
	actions.InitAndSetArrayItem(data, null);
	}
pageObject.setProxyValue(new XVar("triggers"), (XVar)(triggers));
pageObject.setProxyValue(new XVar("actions"), (XVar)(actions));
return null;

Javascript code

Javascript code goes to Javascript OnLoad event of the page where the action should happen, in our case this is Javascript OnLoad event of Customers table Edit page. The same idea with creating a Javascript function and calling from the external file is also valid here.

You can extend this code by adding more conditions types and more actions. Check inline comments for more info.

$('input, textarea, select, radio').on('change', function() {
        // 'this' refers to the element that triggered the change event
        const str = $(this).attr('id');
        const match = str.match(/^value_(.*?)_/);
        var name;
        if (match) {
            console.log('Value changed for:', match[1] , 'New value:', $(this).val());
            name = match[1];
            value = $(this).val();
        }
        
        // lets see if we have a trigger associated with the current field
        let triggers = proxy['triggers'];
        let actions = proxy['actions'];
        for (const trigger of triggers) {
            if (trigger["field"]==name ) {
                // loop through actions array to see what kind of actions we need to perform
                for (const action of actions) {
                    if ( trigger["id"] == action["trigger_id"]) {
                        
                        var proceed = false;
                        
                        // check conditions
                        // equals
                        if (action["condition"] == "equals") {
                            if ( action["condition_value"] == value ) {
                                proceed = true;
                            }
                        }
                        // not equals
                        if (action["condition"] == "notequals") {
                            if ( action["condition_value"] != value ) {
                                proceed = true;
                            }
                        }
                        // empty
                        if (action["condition"] == "empty") {
                            if ( value.length==0 ) {
                                proceed = true;
                            }
                        }
                        // not empty
                        if (action["condition"] == "notempty") {
                            if ( value.length!=0 ) {
                                proceed = true;
                            }
                        }
                        
                        // do we have mathcing conditions 
                        
                        if (proceed) {
                            // show action
                            if (action["action"] == "show") {
                                pageObj.showField(action["target"]);
                            }

                            // hide action
                            if (action["action"] == "hide") {
                                pageObj.hideField(action["target"]);
                            }
                            // clear action
                            if (action["action"] == "clear") {
                                Runner.getControl(pageid, action["target"]).setValue();
                            }
                            // focus action
                            if (action["action"] == "focus") {
                                Runner.getControl(pageid, action["target"]).setFocus();
                            }
                            // require action
                            if (action["action"] == "require") {
                                Runner.getControl(pageid, action["target"]).addValidation("IsRequired");
                            }
                            
                        }

                    }
                }
            }
        }    
    });

Enjoy!

Version 11 most frequently asked questions answered.

To download a registered version logon to your control panel account and find download links under ‘My purchases’.

Trial version download links

PHPRunner 11.1 for Windows trial
  
ASPRunner.NET 11.1 for Windows trial

PHPRunner 11.1 for Mac trial

PHPRunner 11.1 for Linux (Debian) trial

PHPRunner 11.1 for Linux (RedHat) trial

You can install and run it side by side with versions 10.x and 11.0. Existing software functionality will not be affected. Just make sure you do not launch v11 and v11.1 at the same time if you use a built-in database to store projects.

New features in v11.1

• Calendar View
• GANTT View
• Source control systems support ( SVN, git )
• Lookup wizard enhancements

Calendar View

When you launch version 11.1 you notice two new buttons on the toolbar, ‘Create Calendar’ and ‘Create GANTT chart’. Click ‘Create Calendar’. Choose to create a new table to get familiar with this feature. You can switch to using your own database table later. The software will create a new table for you and will even add some test data. You can now build the project and enjoy a fully-working Calendar page.

If you choose to use your own table make sure to proceed to ‘Calendar settings’ screen ( between Pages and Fields ) and configure your Calendar. Date field and subject field are mandatory and rest are optional.

PS. Calendar view is based on FullCalendar open source project.

GANTT View

GANTT Chart is similar in many ways to the Calendar. We also recommend creating a new table and getting familiar with this feature first. GANTT View specific settings can be found on ‘Gantt’ screen.

You can add, edit, delete tasks, add dependent tasks, use drag-n-drop to change task/subtask dates or update task progress.

PS. We use Frappe Gantt component as a core of this new feature.

SVN/git support

Source control system support provides two main scenarios.
1. Using SVN/git for backups only.
2. Single developer working on the same project from different machines.

SVN, backup only

1. Install SVN command-line client software. On Windows we recommend SilkSVN.

2. Create a new empty folder in your SVN repository. Checkout it to any folder on your local computer.

3. In PHPRunner/ASPRunner.NET proceed to Project -> Version control settings and select ‘Export directory’, the same one where you performed the checkout. Other settings will be populated automatically.

4. ‘Commit after each project saving’ – this is up to you to decide.

5. ‘Update on each project opening’ – if you use SVN for backups only then turn this option off.

Once you performed these steps, you will see a new ‘Save and commit’ button at the bottom of the software. Use it to save your project and commit changes to repository.

6. To restore a project from backup proceed to the start screen of the software, ‘Import’ tab. Click Browse and point the software to project.json file in the checkout folder.

SVN, working from different computers

1. On the first computer repeat all steps for ‘Backup only’. Enable option ‘Update on each project opening’. Now, in order to open this project you will need to use ‘Open from version control’ tab on the start screen.

2. On the second computer perform project checkout from SVN. On start screen proceed to ‘Open from version control’ tab, click Browse and point it to the folder where you performed the checkout. You will see a dialog with SVN settings, just leave everything as is and click ‘OK’.

Note: it is important not to use this feature for simultaneous development by multiple developers. Each commit will override changes made on another computer.

GIT

The ideology is the same but there a few extra commands you need to execute manually.

1. Create a new repository at github.com, for example JonDoe/Cars

2. Create a directory on your computer, open the terminal and change to that directory.

3. Run these commands, replace JonDoe/Cars with the actual repository name:

git init
git remote add origin https://github.com/JonDoe/Cars.git
git branch -M main

4. Repeat steps 2 and 3 to access your github repository on the second computer.

5. During the first Save and Commit you may encounter this error:

Author identity unknown
*** Please tell me who you are.

If this happens, run the following commands in the terminal/command line and and try saving and committing again.

git config --global user.email "you@example.com"
git config --global user.name "Your Name"

6. Now you can do the same, in PHPRunner/ASPRunner.NET proceed to ‘Open from version control’ tab, click Browse and point it to the folder where you performed the checkout.

Lookup Wizard enhancements

New feature here is ‘Edit selected’ which allows you to edit selected entry in a lookup wizard. Can be handy when you noticed a typo in Lookup Wizard data and want to fix it without leaving the page.

Enjoy!

How this can be useful? There are many possible uses of Docker containers. You can use them for testing for instance. If you have a website that runs on PHP 8.1 and want to make sure that switching to PHP 8.3 doesn’t break anything, you can create a container based on PHP 8.3 and fully test your app there before making a switch on the main website. Other options include distributing your application to your customers as a container or using containers for web hosting if your web hosting provider supports this option.

In this article we will explain how you can package your PHPRunner application as a Docker container.

Docker Desktop installation

As a first step you need to install Docker Desktop on your local computer. Just download it from the official website and install keeping all default settings.

Once Docker Desktop is installed, launch it. Two main sections we are going to work with are Images and Containers.

A Docker image is a read-only template that contains everything needed to run an application, including the application’s code, system tools, and libraries. A Docker container is a running instance of a Docker image Think of an image as a blueprint and a container as the building constructed from that blueprint.

Project structure

Create a new folder for our Docker project i.e. C:\Project\Docker. Here is how the default Docker project looks like. src folder needs to be created manually and this is where all our PHP files will be stored. Luckily, we do not need to create the rest of file manually, Docker desktop can help us creating those.

Docker init

Start by opening a terminal ( click Terminal icon in the bottom right corner ). First, switch to your project folder and then run docker init command.

cd C:\Projects\Docker

docker init

Docker will ask you a few questions about what you want to create. Select Apache+PHP web application. You can use up and down keyboard keys to change your selection. Press Enter to make a choice.

For all other questions leave default options. Docker will create an image for you, based on your selection. On Images tab you are now going to see a new image. Docker will also tell you that now you need to run docker compose up –build command in order to build and launch your container.

Dockerfile

This is how the initial dockerfile looks. I just removed some extra comments for brevity. Lets try to create a container and run it.

# syntax=docker/dockerfile:1

# https://docs.docker.com/go/dockerfile-reference/

FROM php:8.2-apache

RUN mv "$PHP_INI_DIR/php.ini-production" "$PHP_INI_DIR/php.ini"

# Copy app files from the app directory.
COPY ./src /var/www/html

# Switch to a non-privileged user (defined in the base image) that the app will run under.
# See https://docs.docker.com/go/dockerfile-user-best-practices/
USER www-data

Build and first run

Without making any changes to dockerfile we go back to the terminal and run the suggested build command:

docker compose up –build

First build will take about a minute and consecutive will be a bit faster. Dcker performs the build and starts our container. You can see container’s status on Containers tab. You can see that our new container is up and running.

Which means we can can now open a web browser and type in the following URL: http://localhost:9000. This is what you are supposed to see:

Good news: our container is in fact running.
Bad news: mysqli extension is missing and we cannot connect to MySQL.

Installing mysqli extension

Quick Google search for “docker php install mysqli” points us to the solution and we modify our dockerfile in any text editor adding lines 7 and 8.

# syntax=docker/dockerfile:1

# https://docs.docker.com/go/dockerfile-reference/

FROM php:8.2-apache

# this is the line we have added manually
RUN docker-php-ext-install mysqli

RUN mv "$PHP_INI_DIR/php.ini-production" "$PHP_INI_DIR/php.ini"

# Copy app files from the app directory.
COPY ./src /var/www/html

# Switch to a non-privileged user (defined in the base image) that the app will run under.
# See https://docs.docker.com/go/dockerfile-user-best-practices/
USER www-data

Now we stop the container, run the build command again and once finished we refresh our browser window. This takes us one step further but we are not quite there yet.

While this error is not very descriptive we can guess that our application is not able to connect to MySQL. We use “localhost” as MySQL server address which in case of the container refers to container itself. We need to find a way to connect to MySQL on the host which is our local computer running Docker desktop. Another quick Google search for “docker connect to host from container” points us to host.docker.internal which we need to use instead of localhost while connecting to MySQL.

We proceed to PHPRunner, create a new Server Database Connection on the ‘Output Directory’ screen and change the address of MySQL server as follows:

$host = 'host.docker.internal';
$user = 'root';
$pwd = '';
$port = 3306;
$sys_dbname = 'cars';

And one more try

1. Stop the container
2. Build PHPRunner project ( make sure that new connection is selected )
3. Copy all files from PHPRunner’s output folder to C:\Projects\Docker\src
4. Execute docker compose up –build command one more time.
5. Refresh browser’s window

It runs!

Additional notes. You can make your container connect to any database. It can be a database that is a part of the same container ( though it is not recommended ), or a database running in another container or any external database server. All you need to do is to point the database connection to your database of choice.

1. Download updated PHPDesktop. Unzip it to C:\Program Files\PHPRunner 10.91\DesktopApp keeping directory structure and overwriting existing files.

2. Download updated phprunnerapp-setup.iss file and replace the existing one in C:\Program Files\PHPRunner 10.91\DesktopApp folder.

This is it! Now you can use PHPRunner 10.91 to build desktop apps that support PHP 8.3.

We will convert their IP address to lat/lng coordinates and display those markers on OpenStreetMap map. To perform the conversion of IP addresses to lat/lng pairs we are going to use the geolocation data from ip2location.com.

We will display users that were active in the last ten minutes. If the user had some activity in the last 60 seconds, their dot will be pulsing.

There is also a YouTube video that provides more details of this project.

1. Database structure.

We are going to need two tables, ‘users’ and ‘ip2location’. The following is the script for MySQL database.

CREATE TABLE `ip2location`(`id` int NOT NULL AUTO_INCREMENT, `ip_start` decimal(20,6) NULL, `ip_end` decimal(20,6) NULL, `STATE` varchar(50) NULL, `COUNTRY` varchar(50) NULL, `REGION` varchar(50) NULL, `CITY` varchar(100) NULL, `LATITUDE` double NULL, `LONGITUDE` double NULL, PRIMARY KEY (`id`))CHARACTER SET utf8;
CREATE TABLE `users`(`id` int NOT NULL AUTO_INCREMENT, `ip` varchar(50) NOT NULL DEFAULT '0', `lat` double NOT NULL DEFAULT 0, `lng` double NOT NULL DEFAULT 0, `last_activity` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`))CHARACTER SET utf8;

Please note that this SQL script only creates ‘ip2location’ but doesn’t come with the data. The data set itself is about 300Mb and you can download it for free at https://lite.ip2location.com/database/ip-country.

2. Insert a code snippet into a page where you want to display the map. In our case it would be the menu page. The code itself is very simple, it merely outputs the div with ‘map’ ID.

PHP code:

echo "<div id='map' style=''></div>";

3. AfterApplicationInitialized event

PHP code:

// convert IP address to a decimal number in order to perform a database search
function ip_to_decimal($ip_address) {
    $parts = explode('.', $ip_address);
    $decimal_ip = 0;
    foreach ($parts as $part) {
        $decimal_ip = $decimal_ip * 256 + (int) $part;
    }
    return $decimal_ip;
}

function saveCurrentUserData(){
		$ip = $_SERVER["REMOTE_ADDR"];
		if( empty($ip) )
			return false;
		$userRs = DB::Select("users",array("ip" => $ip));
		$user = $userRs->fetchAssoc();
		if( $user ){
			DB::Update("users", array("last_activity" => date("Y-m-d H:i:s")) ,array("ip" => $ip));
		}
		else{
			$decimalip = ip_to_decimal($ip);
			$coordsRs = DB::Query("select * from ip2location where ".$decimalip." BETWEEN ip_start and ip_end");

			$coords = $coordsRs->fetchAssoc();
			if( $coords ){
				$userData = array("ip" => $ip,
                                            "lat" => $coords["LATITUDE"], 
                                            "lng" =>  $coords["LONGITUDE"],
                                             "last_activity" => date("Y-m-d H:i:s"));
				DB::Insert("users",$userData);
			}
		}
}
if( postvalue("getActiveUsers") ){
	$interval = 10;  
        // we only display on the map users that accessed any page in the last ten minutes

	saveCurrentUserData();
	$dateCondition = date("Y-m-d H:i:s",time() - ($interval*60));
	$userRs = DB::Query("select * from users where last_activity > '".$dateCondition."'");
	$latLng = array();

        $userData = $userRs->fetchAssoc();
	while( $userData ){
		$coordsInfo = array("id" => $userData['id'], 
                              "lat" => $userData['lat'],
                              "lng" => $userData['lng'],
                               "active" => false);

		if( ( time() - strtotime($userData["last_activity"]) ) <=60 ) {
		    $coordsInfo['active'] = true;
                }
		$latLng[] = $coordsInfo;
                $userData = $userRs->fetchAssoc();
	}
	echo my_json_encode($latLng);
	exit();
}

4. custom_function.js

The following Javascript code goes to Event Editor -> custom_function.js section.

$(document).ready(function() {

    $("#map").width($(".r-fluid").width());
    var height = $(window).height() - $("#map").offset().top - 30;
    $("#map").height(height);

    window.mapObj = new OpenLayers.Map("map", {
        controls: [
            new OpenLayers.Control.PanZoomBar(),

            new OpenLayers.Control.Navigation()
        ],
    });

    var layer = new OpenLayers.Layer.OSM();

    mapObj.addLayer(layer);

    window.markersList = new OpenLayers.Layer.Markers("Markers");
    mapObj.addLayer(markersList);
    mapObj.zoomToMaxExtent();

    updateMarkers();
    setInterval(updateMarkers, 5000);

    function updateMarkers() {

        $.post("", {
            getActiveUsers: true
        }, function(response) {

            var coordsArr = JSON.parse(response),
                activeIds = coordsArr.map(function(coords) {
                    return coords.id;
                }),
                allIds = markersList.markers.map(function(marker) {
                    return marker.id;
                });


            $.each(coordsArr, function(i, latLon) {

                if (!allIds.includes(latLon.id)) {
                    addMarker(latLon.id, latLon.lat, latLon.lng, latLon.active);
                } else {
                    var curMarker = markersList.markers.find(function(marker) {
                        return marker.id == latLon.id
                    });
                    if (curMarker.active != latLon.active) {
                        $(curMarker.icon.imageDiv).toggleClass("active", latLon.active);
                    }
                }
            });
            allIds = markersList.markers.map(function(marker) {
                return marker.id;
            });

            for (var i = 0; i < allIds.length; i++) {

                if (allIds[i] != undefined && !activeIds.includes(allIds[i])) {

                    var markerToRemove = markersList.markers.find(function(marker) {
                        return marker.id == allIds[i]
                    });
                    markersList.removeMarker(markerToRemove);
                }

            }

            function addMarker(id, lat, lng, active) {

                var lonLat = new OpenLayers.LonLat(lng, lat)
                    .transform(
                        new OpenLayers.Projection("EPSG:4326"), // transform from WGS 1984
                        mapObj.getProjectionObject() // to Spherical Mercator Projection
                    );
                var icon = new OpenLayers.Icon("", new OpenLayers.Size(15, 15));
                var marker = new OpenLayers.Marker(lonLat, icon);
                if (active) {
                    $(marker.icon.imageDiv).addClass("active");
                }

                marker.id = id;
                marker.active = active;
                markersList.addMarker(marker);

                return marker;
            }
        });


    }
    updateMarkers();


});

5. CSS code ( Style Editor -> Modify CSS )

We use this CSS code to customize and prettify the default look of OSM map.

.olTileImage {
    filter: brightness(48%) contrast(256%);
}

[id^="OL_Icon"] .olAlphaImg {
    background: white;
    cursor: pointer;
    border-radius: 100%;
  }
  [id^="OL_Icon"].active .olAlphaImg {
    animation: pulse 2s infinite;
    box-shadow: 10px 10px 10px rgba(255,255,255, 0.7);
    width:20px !important;
    height: 20px !important;
  }

  @-webkit-keyframes pulse {
    0% {
      -webkit-box-shadow: 10px 10px 10px rgba(255,255,255, 0.7);
    }
    70% {
        -webkit-box-shadow: 0 0 0 10px rgba(255,255,255, 0);
    }
    100% {
        -webkit-box-shadow: 0 0 0 0 rgba(255,255,255, 0);
    }
  }
  @keyframes pulse {
    0% {
      -moz-box-shadow: 0 0 0 0 rgba(255,255,255, 0.7);
      box-shadow: 0 0 0 0 rgba(255,255,255, 0.7);
    }
    70% {
        -moz-box-shadow: 0 0 0 10px rgba(255,255,255, 0);
        box-shadow: 0 0 0 10px rgba(255,255,255, 0);
    }
    100% {
        -moz-box-shadow: 0 0 0 0 rgba(255,255,255, 0);
        box-shadow: 0 0 0 0 rgba(255,255,255, 0);
    }
  }