<?xml version="1.0" encoding="UTF-8"?><rss version="2.0"
	xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:atom="http://www.w3.org/2005/Atom"
	xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
	xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
	>

<channel>
	<title>Xlinesoft Blog</title>
	<atom:link href="https://xlinesoft.com/blog/feed/" rel="self" type="application/rss+xml" />
	<link>https://xlinesoft.com/blog</link>
	<description>Building the best web application builder, one byte at the time</description>
	<lastBuildDate>Mon, 16 Nov 2026 23:52:50 +0000</lastBuildDate>
	<language>en-US</language>
	<sy:updatePeriod>
	hourly	</sy:updatePeriod>
	<sy:updateFrequency>
	1	</sy:updateFrequency>
	<generator>https://wordpress.org/?v=7.1.4</generator>
	<item>
		<title>Save PHPRunner Forms Offline and Synchronize Them Later</title>
		<link>https://xlinesoft.com/blog/2026/11/16/save-phprunner-forms-offline-and-synchronize-them-later/</link>
					<comments>https://xlinesoft.com/blog/2026/11/16/save-phprunner-forms-offline-and-synchronize-them-later/#respond</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Mon, 16 Nov 2026 21:50:20 +0000</pubDate>
				<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3564</guid>

					<description><![CDATA[Learn how to let users complete a PHPRunner master-detail Add form without a connection, store records and photos in IndexedDB, and synchronize them later.]]></description>
										<content:encoded><![CDATA[<p>Field work does not always happen where a reliable network connection is available. In this tutorial, we will modify a PHPRunner master-detail Add page so users can continue entering inspections after the connection is lost.</p>
<p>The loaded page stores the inspection, its detail records, and selected photos in the browser. When the application server becomes available again, the queued submissions are synchronized automatically.</p>
<p>This is not a fully offline application. The user must open the Add page while connected and keep that page open. Once it is loaded, the form can continue working without access to the server.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb.png"><img fetchpriority="high" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb-1024x551.png" alt="PHPRunner inspection Add page with two offline submissions stored in IndexedDB" width="810" height="436" class="alignnone size-large wp-image-3571" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb-1024x551.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb-600x323.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb-768x413.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb-1536x826.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-inspections-indexeddb.png 1922w" sizes="(max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3564"></span></p>
<h3>1. Preparing the inspection form</h3>
<p>Create the following MySQL tables and add them to the PHPRunner project:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE `inspections` (
    `inspection_id` int NOT NULL AUTO_INCREMENT,
    `property_address` varchar(255) NOT NULL,
    `inspection_date` date NOT NULL,
    `notes` longtext NULL,
    `submission_id` varchar(36) NULL,
    PRIMARY KEY (`inspection_id`)
);

CREATE TABLE `findings` (
    `finding_id` int NOT NULL AUTO_INCREMENT,
    `inspection_id` int NOT NULL,
    `room` varchar(40) NOT NULL,
    `description` longtext NOT NULL,
    `photos` longtext NULL,
    PRIMARY KEY (`finding_id`)
);</textarea></pre>
</div>
<p>Create a master-detail relationship between <strong>inspections.inspection_id</strong> and <strong>findings.inspection_id</strong>.</p>
<p>Open the <strong>inspections Add page</strong> in Page Designer and add a Findings details preview. The Findings List page must allow Inline Add because each finding is entered as a detail row on the inspection form.</p>
<p>Configure the Findings fields as follows:</p>
<ul>
<li><strong>room</strong>: Lookup Wizard with a static list such as Kitchen, Bathroom, Basement, Garage, and Other.</li>
<li><strong>description</strong>: Text area.</li>
<li><strong>photos</strong>: Document upload, with File-based Image as its View format.</li>
</ul>
<p>A static lookup works after the connection is lost because its values are already included in the loaded page. An AJAX lookup would need to contact the server.</p>
<h3>2. Adding the server availability check</h3>
<p>Open <strong>Editor &#8211; Custom files</strong>, create <strong>offline_ping.php</strong>, and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
<?php
require_once __DIR__ . '/include/dbcommon.php';

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');

echo json_encode([
    'ready' => true,
    'scope' => 'home-inspection:demo'
]);</textarea></pre>
</div>
<p>The browser&#8217;s online status does not guarantee that the application server is reachable. This file provides a small application-specific response that the synchronization code can verify. The request uses a four-second timeout and disables caching.</p>
<h3>3. Saving submissions in IndexedDB</h3>
<p>IndexedDB is a database built into modern web browsers. <strong>localStorage</strong> is useful for small text values, but IndexedDB can store structured records and binary objects such as the photos selected on this form. Its storage allowance is also generally much larger, although the exact limit is controlled by the browser and device.</p>
<p>Open <strong>Events &#8211; Global events &#8211; custom_functions.js</strong> and add the following JavaScript:</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-form-custom-functions.zip"><strong><i class="fas fa-link" style="solid"></i> Download custom_functions.js</strong></a>.</p>
<p>The queue uses a generated ID for each submission and stores records under the <strong>home-inspection:demo</strong> scope. Change this scope consistently in both the JavaScript and <strong>offline_ping.php</strong> when adapting the example to another application.</p>
<p>The runtime listens for the browser&#8217;s <strong>online</strong> and <strong>focus</strong> events. It also checks the server every 30 seconds as a fallback. Browser locks prevent two tabs from draining the same queue simultaneously.</p>
<h3>4. Intercepting the Inspections Add page</h3>
<p>Open the <strong>inspections Add page &#8211; JavaScript OnLoad event</strong> and add this code:</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/11/phprunner-offline-form-add-page-onload.zip"><strong><i class="fas fa-link" style="solid"></i> Download the JavaScript OnLoad event code</strong></a>.</p>
<p>PHPRunner normally uploads a selected file immediately. The event intercepts file selection and drag-and-drop before that request begins, keeps the File objects in the browser, and generates local previews with <strong>URL.createObjectURL()</strong>.</p>
<p>The event also intercepts the page&#8217;s normal Save operation. It validates the master fields and visible finding rows, adds the complete submission to IndexedDB, and then clears the form without reloading the page.</p>
<h3>5. Synchronizing queued inspections</h3>
<p>When the server responds to <strong>offline_ping.php</strong>, the project-level JavaScript reproduces the required PHPRunner save sequence:</p>
<ol>
<li>Create the inspection through the generated <strong>inspections_add.php</strong> page.</li>
<li>Read the new inspection key returned by PHPRunner.</li>
<li>For each finding, upload its photos through <strong>mfhandler.php</strong>.</li>
<li>Create the finding through <strong>findings_add.php</strong> using that inspection key.</li>
<li>Delete the IndexedDB entry only after all responses have been confirmed.</li>
</ol>
<p>The synchronization checkpoint records the inspection key and the next detail row to send. If a write may have reached the server but its response was not confirmed, the entry remains queued instead of blindly repeating the write and possibly creating a duplicate.</p>
<p>Status messages show whether submissions are waiting, being sent, successfully synchronized, or blocked by an error.</p>
<h3>6. Testing the offline workflow</h3>
<p>Build the project and open the Inspections Add page while connected to the server. In the browser&#8217;s developer tools, open the Network panel and select &#8216;Offline&#8217;.</p>
<p>Enter an inspection, add one or more finding rows, select photos, and click &#8216;Save&#8217;. The form should remain open and display a message that the inspection was saved on the device.</p>
<p>To inspect the queue in Chrome or Edge, open <strong>Developer tools &#8211; Application &#8211; IndexedDB &#8211; phprunner-offline-add-v1 &#8211; submissions</strong>.</p>
<p>Add another inspection if required, then clear the Network panel&#8217;s &#8216;Offline&#8217; setting. The application should begin synchronization immediately. If the browser&#8217;s online event does not fire, the next 30-second server check will start it.</p>
<p>Open the Inspections List page and confirm that the master records, finding rows, and uploaded photos were saved.</p>
<h3>7. Current scope and possible next steps</h3>
<p>The Add page must be loaded before the connection is lost. Do not reload or close it while offline. IndexedDB data belongs to the current browser profile and device, so opening the application in another browser will not expose the same queue.</p>
<p>This approach can be extended in several ways. A Progressive Web App and service worker could allow the page itself to open offline. Lookup-table data could be cached in IndexedDB and refreshed while connected. Applications that require login could detect an expired session, ask the user to sign in again, and resume synchronization afterward. A dedicated REST API could also replace the generated-page requests when a more formal synchronization interface is required.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/11/16/save-phprunner-forms-offline-and-synchronize-them-later/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Version 11.4</title>
		<link>https://xlinesoft.com/blog/2026/11/12/version-11-4/</link>
					<comments>https://xlinesoft.com/blog/2026/11/12/version-11-4/#comments</comments>
		
		<dc:creator><![CDATA[admin]]></dc:creator>
		<pubDate>Thu, 12 Nov 2026 23:01:11 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[News]]></category>
		<category><![CDATA[PHP]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3547</guid>

					<description><![CDATA[The PHPRunner and ASPRunner.NET 11.4 beta is here! Cody, the built-in Code Assistant, can now make changes directly to your project. The beta also adds accessibility improvements for the web applications you build. Beta version download links: PHPRunner 11.4 for Windows PHPRunner 11.4 for Mac PHPRunner 11.4 for Linux (Debian) PHPRunner 11.4 for Linux (RedHat) ASPRunner.NET 11.4 1. Talk to Cody to make changes to your project In version 11.4, you can ask Cody to change how a field behaves, reorganize a page, or create...<span class="clearfix clearfix-post"></span><a href="https://xlinesoft.com/blog/2026/11/12/version-11-4/" class="more-link">Continue Reading <span class="screen-reader-text">"Version 11.4"</span> <span class="meta-nav">&#8594;</span></a>]]></description>
										<content:encoded><![CDATA[<p><strong>The PHPRunner and ASPRunner.NET 11.4 beta is here!</strong></p>
<p>Cody, the built-in Code Assistant, can now make changes directly to your project. The beta also adds accessibility improvements for the web applications you build.</p>
<p><strong>Beta version download links: </strong><br />
<a href="https://asprunner.com/files/phprunner114trial.exe"><i class="fas fa-link" style="solid"></i> PHPRunner 11.4 for Windows </a><br />
<a href="https://asprunner.com/files/phprunner114trial.dmg"><i class="fas fa-link" style="solid"></i> PHPRunner 11.4 for Mac</a><br />
<a href="https://asprunner.com/files/phprunner114trial.deb"><i class="fas fa-link" style="solid"></i> PHPRunner 11.4 for Linux (Debian)</a><br />
<a href="https://asprunner.com/files/phprunner114trial.rpm"><i class="fas fa-link" style="solid"></i> PHPRunner 11.4 for Linux (RedHat)</a><br />
<a href="https://asprunner.com/files/asprunnernet114trial.exe"><i class="fas fa-link" style="solid"></i> ASPRunner.NET 11.4</a></p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages.png"><img decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages-1024x576.png" alt="" width="810" height="456" class="alignnone size-large wp-image-3554" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages-1024x576.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages-600x338.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages-768x432.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages-1536x864.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/11/PDF-to-database-and-application-pages.png 1672w" sizes="(max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3547"></span></p>
<h3>1. Talk to Cody to make changes to your project</h3>
<p>In version 11.4, you can ask Cody to change how a field behaves, reorganize a page, or create a chart or report.</p>
<p>In the video, we give Cody a PDF job application form and ask it to create the database tables and pages we need to collect applications. Cody shows the proposed database changes for approval, creates the related tables, and adds the pages to the project. You can then open the pages in Page Designer and adjust them. The video also shows Cody creating a chart of sales totals by country from a prompt.</p>
<p><a href="https://www.youtube.com/watch?v=YlkdtnWmQS4"><i class="fas fa-link" style="solid"></i> Watch Cody build from a PDF and create a chart.</a></p>
<p>Here are some sample prompts you can try in PHPRunner and ASPRunner.NET 11.4:</p>
<ul>
<li>On the Calendar Add and Edit pages, disable the Desc field whenever Subject is empty.
<li>Add a button to the Calendar List page that writes the current record’s data to a file on disk. Pick a directory for now. I’ll change it later.
<li>Update this event so it also writes the resulting status message to the browser console.
<li>Review the form in the attached file. Use an existing database table if one matches, or create a table if needed. Create an Add page that looks like the form, saves submissions to the database, and includes the standard pages for reviewing the results.
<li>Create a chart that maps carsusers.id to username.
<li>Create a report from the carsusers table. Group records by the first letter of username and show the maximum id for each group.
<li>Add about half of the remaining fields to the report and its print view. Choose which fields to include and display them without adding summaries.
<li>Regroup the fields on the Calendar Add page. Show the most important fields by default and give users a way to reveal the remaining fields.
</ul>
<h3>2. WCAG 2.1 Level AA accessibility support</h3>
<p>Version 11.4 adds accessibility improvements to generated applications. This is especially relevant if you build web applications for U.S. state and local governments. The ADA Title II web accessibility rule sets WCAG 2.1 Level AA as the technical standard for covered web content and mobile apps. Read the U.S. Department of Justice fact sheet.</p>
<p>Keyboard access. Users can move between controls with Tab and activate buttons with Enter or Space.</p>
<p>Forms and reports. Screen readers can identify the label for each form field and the headings associated with report values.</p>
<p>Text contrast. We increased the contrast between text and backgrounds to make text easier to read.</p>
<p>Image descriptions. Users can add text descriptions when uploading informative images. Decorative images are marked so screen readers can skip them. AI-generated description suggestions are planned for the final release.</p>
<p><strong>We expect the final release before December 10. Two features below will be available in the final version</strong></p>
<h3>3. Built-in MCP server</h3>
<p>The built-in MCP server will let chatbots and other AI agents connect to web applications built with PHPRunner and ASPRunner.NET. It will provide the same basic record operations as the REST API, such as searching, adding, and updating records. You’ll also be able to define custom MCP actions directly in your project.</p>
<p>In the PHPRunner prototype, we ask an AI assistant to show the last five orders. A custom action goes a step further and creates a customer and a complete order in one request.</p>
<p><a href="https://www.youtube.com/watch?v=EJun7UB42PM"><i class="fas fa-link" style="solid"></i> Watch the PHPRunner MCP prototype demo</a><br />
<a href="https://xlinesoft.com/blog/2026/09/10/experimenting-with-mcp-support-in-phprunner/"><i class="fas fa-link" style="solid"></i> Read about the prototype and download the sample project</a></p>
<h3>4. Search individual dashboard grids</h3>
<p>You’ll be able to add a separate search box to each grid on a dashboard. For example, users can search the Products grid while the Customers and Employees grids continue to show their current results.</p>
<p><a href="https://xlinesoft.com/blog/2026/09/01/add-search-boxes-to-individual-dashboard-grids"><i class="fas fa-link" style="solid"></i> See how search works within an individual dashboard grid in our earlier tutorial.</a></p>
<p>Enjoy!</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/11/12/version-11-4/feed/</wfw:commentRss>
			<slash:comments>4</slash:comments>
		
		
			</item>
		<item>
		<title>Build Semantic Search with Ollama and MariaDB</title>
		<link>https://xlinesoft.com/blog/2026/10/29/build-local-semantic-search-with-ollama-and-mariadb/</link>
					<comments>https://xlinesoft.com/blog/2026/10/29/build-local-semantic-search-with-ollama-and-mariadb/#respond</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Thu, 29 Oct 2026 21:32:16 +0000</pubDate>
				<category><![CDATA[PHP]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3537</guid>

					<description><![CDATA[Add local semantic search and duplicate detection to a PHPRunner application using Ollama embeddings and MariaDB vector functions.]]></description>
										<content:encoded><![CDATA[<p>Semantic search does not have to send text to an external embeddings service or calculate similarity in PHP. In this tutorial, we will use <a href="https://ollama.com/" target="_blank" rel="noopener">ollama.com</a> to generate embeddings locally and MariaDB to store and compare the vectors.</p>
<p>This is a local alternative to our earlier <a href="https://xlinesoft.com/blog/2026/10/18/add-semantic-search-to-a-phprunner-application/">PHPRunner semantic search tutorial</a>. The example searches support tickets by meaning and also checks new tickets for possible semantic duplicates before saving them.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB.png"><img decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB-1024x576.png" alt="" width="810" height="456" class="alignnone size-large wp-image-3544" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB-1024x576.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB-600x338.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB-768x432.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB-1536x864.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB.png 1672w" sizes="(max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3537"></span></p>
<p>Ollama runs the embedding model on the same computer as the generated application. MariaDB&#8217;s <strong>VECTOR</strong> type stores each 768-value embedding, while <strong>VEC_DISTANCE_COSINE()</strong> finds the closest records. The ticket text and search phrases remain on the local system.</p>
<h3>1. Installing Ollama and the embedding model</h3>
<p>Install Ollama from <a href="https://ollama.com/download" target="_blank" rel="noopener">ollama.com/download</a>. This example uses <a href="https://ollama.com/library/embeddinggemma" target="_blank" rel="noopener">EmbeddingGemma</a>, which requires Ollama 0.11.10 or later.</p>
<p>Open a command prompt and download the model:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
ollama pull embeddinggemma</textarea></pre>
</div>
<p>Ollama exposes its local API at <strong>http://127.0.0.1:11434</strong>. Keep Ollama running on the machine that hosts the generated PHPRunner application. PHP also needs the cURL extension enabled.</p>
<p>This tutorial requires MariaDB 11.7 or later because it uses the native <strong>VECTOR</strong> type and <strong>VEC_DISTANCE_COSINE()</strong> function.</p>
<h3>2. Adding local vector fields</h3>
<p>The example continues with the <strong>support_tickets</strong> table from the previous tutorial. Create a copy for the Ollama version, then add the local vector and hash fields:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE support_tickets_ollama LIKE support_tickets;

INSERT INTO support_tickets_ollama
SELECT * FROM support_tickets;

ALTER TABLE support_tickets_ollama
    ADD COLUMN embedding_local VECTOR(768) NULL,
    ADD COLUMN embedding_hash_local CHAR(64) NULL;</textarea></pre>
</div>
<p>Add <strong>support_tickets_ollama</strong> to the PHPRunner project. On its <strong>SQL Query</strong> screen, add a calculated field named <strong>semantic_query</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
SELECT
    support_tickets_ollama.*,
    '' AS semantic_query
FROM support_tickets_ollama</textarea></pre>
</div>
<p>Do not display <strong>embedding_local</strong>, <strong>embedding_hash_local</strong>, or <strong>semantic_query</strong> on the generated pages. Make <strong>semantic_query</strong> searchable so its normal search condition can be replaced by the semantic-search result IDs.</p>
<p>Turn off &#8216;Highlight search results&#8217;. Semantic matches often use different words, so ordinary keyword highlighting is not useful here.</p>
<h3>3. Adding the Ollama embedding function</h3>
<p>Open <strong>Events &#8211; Global events &#8211; After App Init</strong> and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if (!function_exists("devclubOllamaEmbedding")) {
    function devclubOllamaEmbedding($text)
    {
        if (!function_exists("curl_init")) {
            throw new RuntimeException(
                "PHP cURL extension is required."
            );
        }

        $payload = json_encode(array(
            "model" => "embeddinggemma:latest",
            "input" => $text
        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

        if ($payload === false) {
            throw new RuntimeException("Cannot encode Ollama request.");
        }

        $ch = curl_init("http://127.0.0.1:11434/api/embed");

        if ($ch === false) {
            throw new RuntimeException("Cannot initialize cURL.");
        }

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $payload,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 90
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $error = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "Ollama connection failed: " . $error
            );
        }

        $data = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = is_array($data)
                ? ($data["error"] ?? "Unknown error")
                : "Unknown error";

            throw new RuntimeException(
                "Ollama HTTP " . $httpCode . ": " . $detail
            );
        }

        $vector = $data["embeddings"][0] ?? null;

        if (!is_array($vector) || count($vector) !== 768) {
            throw new RuntimeException(
                "Expected 768-dimensional embedding."
            );
        }

        foreach ($vector as $number) {
            if (!is_numeric($number) ||
                !is_finite((float)$number)) {
                throw new RuntimeException(
                    "Invalid number in embedding."
                );
            }
        }

        $json = json_encode(
            $vector,
            JSON_PRESERVE_ZERO_FRACTION
        );

        if ($json === false) {
            throw new RuntimeException(
                "Cannot encode embedding."
            );
        }

        return $json;
    }
}</textarea></pre>
</div>
<p>The function sends text to the local Ollama API and returns a JSON representation of the 768-value vector. The same model and text format must be used for stored ticket embeddings and search embeddings.</p>
<h3>4. Generating embeddings for existing tickets</h3>
<p>Open the <strong>support_tickets_ollama List page</strong> in Page Designer and add a button named &#8216;Generate embeddings&#8217;. Open its <strong>Server</strong> event and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
try {
    $batchSize = 100;
    $tickets = array();
    $inputs = array();

    $rs = DB::Query(
        "SELECT ticket_id, subject, description " .
        "FROM support_tickets_ollama " .
        "WHERE embedding_local IS NULL " .
        "ORDER BY ticket_id LIMIT " . (int)$batchSize
    );

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    while ($row = $rs->fetchAssoc()) {
        $text = "title: " . (string)$row["subject"] .
            " | text: " . (string)$row["description"];

        $tickets[] = array(
            "id" => (int)$row["ticket_id"],
            "text" => $text
        );

        $inputs[] = $text;
    }

    if (!$tickets) {
        $result["message"] =
            "All tickets already have local embeddings.";
    } else {
        if (!function_exists("curl_init")) {
            throw new RuntimeException(
                "PHP cURL extension is required."
            );
        }

        $payload = json_encode(array(
            "model" => "embeddinggemma:latest",
            "input" => $inputs
        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

        if ($payload === false) {
            throw new RuntimeException("Cannot encode request.");
        }

        $ch = curl_init("http://127.0.0.1:11434/api/embed");

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $payload,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 120
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo(
            $ch, CURLINFO_HTTP_CODE
        );
        $curlError = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "Cannot connect to Ollama: " . $curlError
            );
        }

        $data = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = $data["error"] ??
                substr($response, 0, 200);

            throw new RuntimeException(
                "Ollama HTTP " . $httpCode . ": " . $detail
            );
        }

        $vectors = $data["embeddings"] ?? null;

        if (!is_array($vectors) ||
            count($vectors) !== count($tickets)) {
            throw new RuntimeException(
                "Unexpected number of embeddings returned."
            );
        }

        $updated = 0;

        foreach ($tickets as $i => $ticket) {
            $vector = $vectors[$i];

            if (!is_array($vector) || count($vector) !== 768) {
                throw new RuntimeException(
                    "Invalid vector for ticket " . $ticket["id"]
                );
            }

            foreach ($vector as $number) {
                if (!is_numeric($number) ||
                    !is_finite((float)$number)) {
                    throw new RuntimeException(
                        "Invalid vector number for ticket " .
                        $ticket["id"]
                    );
                }
            }

            $jsonVector = json_encode(
                $vector,
                JSON_PRESERVE_ZERO_FRACTION
            );

            if ($jsonVector === false) {
                throw new RuntimeException(
                    "Cannot encode vector for ticket " .
                    $ticket["id"]
                );
            }

            $hash = hash("sha256", $ticket["text"]);

            $sql = DB::PrepareSQL(
                "UPDATE support_tickets_ollama " .
                "SET embedding_local = VEC_FromText(':1'), " .
                "embedding_hash_local = ':2' " .
                "WHERE ticket_id = :3",
                $jsonVector,
                $hash,
                $ticket["id"]
            );

            if (!DB::Exec($sql)) {
                throw new RuntimeException(
                    "Saving ticket " . $ticket["id"] .
                    " failed: " . DB::LastError()
                );
            }

            $updated++;
        }

        $countRs = DB::Query(
            "SELECT COUNT(*) AS remaining " .
            "FROM support_tickets_ollama " .
            "WHERE embedding_local IS NULL"
        );

        if (!$countRs) {
            throw new RuntimeException(DB::LastError());
        }

        $countRow = $countRs->fetchAssoc();
        $remaining = (int)$countRow["remaining"];

        $result["message"] =
            "Generated " . $updated . " embeddings. " .
            $remaining . " tickets remaining.";
    }
} catch (Throwable $e) {
    $result["message"] =
        "Embedding generation failed: " . $e->getMessage();
}</textarea></pre>
</div>
<p>Open the button&#8217;s <strong>Client After</strong> event and display the returned progress message:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
ctrl.setMessage(result["message"]);</textarea></pre>
</div>
<p>The button processes up to 100 records each time it is clicked. Continue until the message reports that all tickets have local embeddings.<br />
MariaDB requires an indexed vector column to be <strong>NOT NULL</strong>. After all existing records have embeddings, make the column required and create a cosine vector index:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
ALTER TABLE support_tickets_ollama
    MODIFY COLUMN embedding_local VECTOR(768) NOT NULL;

CREATE VECTOR INDEX idx_support_tickets_embedding
    ON support_tickets_ollama (embedding_local)
    M=8 DISTANCE=cosine;</textarea></pre>
</div>
<p>The index uses the same cosine distance function as the semantic-search query. Without a matching cosine index, MariaDB performs a full table scan.</p>
<h3>5. Replacing basic search with local semantic search</h3>
<p>Open the <strong>support_tickets_ollama List page &#8211; Before process</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$srch = SearchClause::getSearchObject("support_tickets_ollama");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

if ($phrase !== "") {
    $key = hash("sha256", "embeddinggemma:latest\n" . $phrase);
    $cache = $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] ?? null;
    $ids = null;

    unset($_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"]);

    if (
        is_array($cache) &&
        isset($cache["key"], $cache["time"], $cache["ids"]) &&
        $cache["key"] === $key &&
        time() - (int)$cache["time"] < 300
    ) {
        $ids = $cache["ids"];
    }

    if ($ids === null) {
        try {
            if (strlen($phrase) > 1000) {
                throw new RuntimeException("Search text is too long.");
            }

            $payload = json_encode(array(
                "model" => "embeddinggemma:latest",
                "input" => "task: search result | query: " . $phrase
            ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);

            if ($payload === false) {
                throw new RuntimeException("Cannot encode Ollama request.");
            }

            $ch = curl_init("http://127.0.0.1:11434/api/embed");

            if ($ch === false) {
                throw new RuntimeException("Cannot initialize cURL.");
            }

            curl_setopt_array($ch, array(
                CURLOPT_POST => true,
                CURLOPT_POSTFIELDS => $payload,
                CURLOPT_HTTPHEADER => array(
                    "Content-Type: application/json"
                ),
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_CONNECTTIMEOUT => 5,
                CURLOPT_TIMEOUT => 60
            ));

            $response = curl_exec($ch);
            $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $curlError = curl_error($ch);
            curl_close($ch);

            if ($response === false) {
                throw new RuntimeException(
                    "Ollama connection failed: " . $curlError
                );
            }

            $data = json_decode($response, true);

            if ($httpCode !== 200) {
                $detail = is_array($data)
                    ? ($data["error"] ?? "Unknown error")
                    : "Unknown error";

                throw new RuntimeException(
                    "Ollama HTTP " . $httpCode . ": " . $detail
                );
            }

            $vector = $data["embeddings"][0] ?? null;

            if (!is_array($vector) || count($vector) !== 768) {
                throw new RuntimeException(
                    "Expected a 768-dimensional embedding."
                );
            }

            foreach ($vector as $number) {
                if (!is_numeric($number) ||
                    !is_finite((float)$number)) {
                    throw new RuntimeException(
                        "Invalid query embedding value."
                    );
                }
            }

            $vectorJson = json_encode(
                $vector,
                JSON_PRESERVE_ZERO_FRACTION
            );

            if ($vectorJson === false) {
                throw new RuntimeException("Cannot encode query vector.");
            }

            $sql = DB::PrepareSQL(
                "SELECT ticket_id FROM support_tickets_ollama " .
                "ORDER BY VEC_DISTANCE_COSINE(" .
                "embedding_local, VEC_FromText(':1')) " .
                "LIMIT 30",
                $vectorJson
            );

            $rs = DB::Query($sql);

            if (!$rs) {
                throw new RuntimeException(
                    "MariaDB query failed: " . DB::LastError()
                );
            }

            $ids = array();

            while ($row = $rs->fetchAssoc()) {
                $ids[] = (int)$row["ticket_id"];
            }

            $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] = array(
                "key" => $key,
                "time" => time(),
                "ids" => $ids
            );
        } catch (Throwable $e) {
            $_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"] =
                $e->getMessage();

            unset($_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"]);
            $ids = array();
        }
    }

    $condition = $ids
        ? "ticket_id IN (" .
            implode(",", array_map("intval", $ids)) . ")"
        : "1=0";

    $srch->setSearchSQL("semantic_query", $condition);
} else {
    unset($_SESSION["DEVCLUB_LOCAL_SEARCH_ERROR"]);
}</textarea></pre>
</div>
<p>The event sends the search phrase to local Ollama, then MariaDB compares the returned vector with <strong>embedding_local</strong>. The 30 closest ticket IDs replace the calculated field&#8217;s normal search condition. Repeating the same query within five minutes uses the session cache.</p>
<h3>6. Sorting tickets by similarity</h3>
<p>Open the <strong>support_tickets_ollama List page &#8211; Before SQL query</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$srch = SearchClause::getSearchObject("support_tickets_ollama");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

$cache = $_SESSION["DEVCLUB_LOCAL_SEARCH_CACHE"] ?? null;
$key = hash("sha256", "embeddinggemma:latest\n" . $phrase);

if (
    $phrase !== "" &&
    is_array($cache) &&
    isset($cache["key"], $cache["time"], $cache["ids"]) &&
    $cache["key"] === $key &&
    time() - (int)$cache["time"] &lt; 300 &&
    !empty($cache["ids"])
) {
    $ids = array_map("intval", $cache["ids"]);

    $strOrderBy = " ORDER BY FIELD(ticket_id, " .
        implode(",", $ids) . ")";
}</textarea></pre>
</div>
<p>The search query filters the grid to the matching IDs. MariaDB&#8217;s <strong>FIELD()</strong> function preserves the similarity order so the strongest match appears first.</p>
<h3>7. Preventing possible duplicate tickets</h3>
<p>Semantic similarity can also be checked before a record is added. Open the <strong>support_tickets_ollama Add page &#8211; Before record added</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
try {
    $subject = trim((string)($values["subject"] ?? ""));
    $description = trim((string)($values["description"] ?? ""));

    if ($subject === "" || $description === "") {
        $message = "Subject and description are required.";
        return false;
    }

    $text = "title: " . $subject .
        " | text: " . $description;

    $hash = hash("sha256", $text);
    $vectorJson = devclubOllamaEmbedding($text);

    $sql = DB::PrepareSQL(
        "SELECT ticket_id, subject, " .
        "VEC_DISTANCE_COSINE(" .
        "embedding_local, VEC_FromText(':1')) AS distance " .
        "FROM support_tickets_ollama " .
        "WHERE embedding_hash_local IS NOT NULL " .
        "ORDER BY VEC_DISTANCE_COSINE(" .
        "embedding_local, VEC_FromText(':2')) " .
        "LIMIT 3",
        $vectorJson,
        $vectorJson
    );

    $rs = DB::Query($sql);

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    $duplicateThreshold = 0.93;

    while ($row = $rs->fetchAssoc()) {
        if ($row["distance"] === null) {
            continue;
        }

        $similarity = 1.0 - (float)$row["distance"];

        if ($similarity >= $duplicateThreshold) {
            $message =
                "Possible duplicate ticket found. " .
                "Ticket ID: " . $row["ticket_id"] .
                ". Subject: " . $row["subject"] .
                ". Similarity: " .
                number_format($similarity, 3) .
                ". Please review before adding.";

            return false;
        }
    }

    $sqlValues["embedding_local"] = DB::PrepareSQL(
        "VEC_FromText(':1')",
        $vectorJson
    );

    $sqlValues["embedding_hash_local"] = DB::PrepareSQL(
        "':1'",
        $hash
    );

    return true;
} catch (Throwable $e) {
    $message = "Cannot add ticket: " . $e->getMessage();
    return false;
}</textarea></pre>
</div>
<p>The event checks the three closest existing tickets. A cosine distance of zero means identical direction, so the code converts distance to similarity with <strong>1 &#8211; distance</strong>. The <strong>0.93</strong> threshold is an experimental starting point; test it against your own records and adjust it to balance missed duplicates and false warnings.</p>
<h3>8. Refreshing the vector after an edit</h3>
<p>Open the <strong>support_tickets_ollama Edit page &#8211; Before record updated</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
try {
    $subject = array_key_exists("subject", $values)
        ? (string)$values["subject"]
        : (string)($oldvalues["subject"] ?? "");

    $description = array_key_exists("description", $values)
        ? (string)$values["description"]
        : (string)($oldvalues["description"] ?? "");

    $text = "title: " . trim($subject) .
        " | text: " . trim($description);

    $hash = hash("sha256", $text);
    $ticketId = (int)$keys["ticket_id"];

    $sql = DB::PrepareSQL(
        "SELECT embedding_hash_local " .
        "FROM support_tickets_ollama " .
        "WHERE ticket_id = :1",
        $ticketId
    );

    $rs = DB::Query($sql);

    if (!$rs) {
        throw new RuntimeException(DB::LastError());
    }

    $row = $rs->fetchAssoc();

    if (!$row) {
        throw new RuntimeException("Ticket not found.");
    }

    $existingHash = (string)(
        $row["embedding_hash_local"] ?? ""
    );

    if ($existingHash !== $hash) {
        $vectorJson = devclubOllamaEmbedding($text);

        $sqlValues["embedding_local"] = DB::PrepareSQL(
            "VEC_FromText(':1')",
            $vectorJson
        );

        $sqlValues["embedding_hash_local"] = DB::PrepareSQL(
            "':1'",
            $hash
        );
    }

    return true;
} catch (Throwable $e) {
    $message = "Cannot update ticket: " . $e->getMessage();
    return false;
}</textarea></pre>
</div>
<p>The stored hash identifies whether the subject or description has changed. Editing other ticket fields keeps the current vector and avoids an unnecessary Ollama request.</p>
<p>Rebuild the application and use the &#8216;Generate embeddings&#8217; button until every existing ticket has a local vector. A natural-language phrase in the List page&#8217;s basic search box should now return tickets with the closest meaning. When a user submits a ticket that is very similar to an existing record, the Add event stops the insert and displays the possible match.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/10/29/build-local-semantic-search-with-ollama-and-mariadb/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Add Semantic Search to a PHPRunner Application</title>
		<link>https://xlinesoft.com/blog/2026/10/18/add-semantic-search-to-a-phprunner-application/</link>
					<comments>https://xlinesoft.com/blog/2026/10/18/add-semantic-search-to-a-phprunner-application/#comments</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Sun, 18 Oct 2026 14:28:23 +0000</pubDate>
				<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3523</guid>

					<description><![CDATA[Build semantic search in PHPRunner with OpenAI embeddings, cosine similarity, cached queries, and MySQL result ranking.]]></description>
										<content:encoded><![CDATA[<p>Keyword search can miss records that describe the same problem using different words. In this tutorial, we will add semantic search to a PHPRunner support-ticket application. A search for &#8216;customers having access or login problems&#8217; can find tickets about rejected credentials, unauthorized sessions, or account access even when they do not contain the exact search phrase.</p>
<p>The application uses the OpenAI Embeddings API to convert ticket text and each search phrase into vectors. PHPRunner then compares those vectors locally with cosine similarity and returns the 30 closest tickets. This approach is intended for relatively small collections. For tens of thousands of records or more, use a vector database instead of comparing every vector in PHP. The cosine similarity scores in the diagram measure how closely the vectors are related. They are not probabilities or percentages of correctness.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network-1024x683.png" alt="" width="810" height="540" class="alignnone size-large wp-image-3524" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network-1024x683.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network-600x400.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network-768x512.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/Support-Ticket-Similarity-Network.png 1536w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3523"></span></p>
<p><strong>Semantic Search in Action</strong></p>
<p>Let&#8217;s try searching for <strong>&#8220;Employees are being denied entry to the company portal.&#8221;</strong></p>
<p>Notice that the results include tickets about failed logins, incorrect credentials, authentication errors, and users being redirected back to the sign-in page.</p>
<p>These tickets describe similar problems using different terminology. A traditional keyword search might miss some of them because the search phrase doesn&#8217;t contain words such as &#8220;password,&#8221; &#8220;login,&#8221; or &#8220;authentication.&#8221;</p>
<p>Semantic search recognizes the relationship between these descriptions and returns relevant tickets based on their meaning, not just matching words.</p>
<p>The application uses cosine similarity to rank the results, displaying the most relevant tickets first. Click the screenshot to see the exact search phrase and the results it returns. </p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search-1024x550.png" alt="" width="810" height="435" class="alignnone size-large wp-image-3531" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search-1024x550.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search-600x323.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search-768x413.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search-1536x826.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr_semantic_search.png 1920w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a></p>
<p><strong>How the search works:</strong></p>
<ol>
<li>Each ticket&#8217;s subject and description are converted into an embedding vector.</li>
<li>The vector is stored in the database with the ticket.</li>
<li>The user&#8217;s search phrase is converted into a vector with the same embedding model.</li>
<li>The generated PHP application calculates cosine similarity between the query vector and the stored ticket vectors.</li>
<li>The most relevant tickets are returned in similarity order.</li>
</ol>
<p>When a ticket is added or its searchable text changes, its subject and description are sent to the OpenAI Embeddings API. During a normal search, only the user&#8217;s search phrase is sent. Existing ticket vectors remain in the local database and are reused. OpenAI creates the vectors but does not search the ticket table; the similarity calculations happen locally in the generated PHP application.</p>
<h3>1. Creating the support tickets table</h3>
<p>Create the following MySQL table and add it to the PHPRunner project.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE `support_tickets` (
    `ticket_id` int NOT NULL AUTO_INCREMENT,
    `ticket_code` varchar(16) NOT NULL,
    `subject` varchar(255) NOT NULL,
    `description` longtext NOT NULL,
    `customer_name` varchar(100) NOT NULL,
    `customer_email` varchar(150) NOT NULL,
    `category` varchar(60) NOT NULL,
    `topic` varchar(80) NOT NULL,
    `status` varchar(30) NOT NULL DEFAULT 'Open',
    `priority` varchar(20) NOT NULL DEFAULT 'Normal',
    `channel` varchar(20) NOT NULL DEFAULT 'Web Portal',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `embedding_json` json NULL,
    `embedding_model` varchar(64) NULL,
    `embedding_sha256` varchar(64) NULL,
    `embedded_at` datetime NULL,
    PRIMARY KEY (`ticket_id`)
);</textarea></pre>
</div>
<p>The optional sample SQL download creates this table and adds 500 support tickets with their precomputed embeddings.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/support_tickets_500_with_embeddings.zip">support_tickets_500_with_embeddings</a></p>
<p>Open the table&#8217;s <strong>SQL Query</strong> screen and add a calculated field named <strong>semantic_query</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
SELECT
    ticket_id,
    ticket_code,
    subject,
    description,
    customer_name,
    customer_email,
    category,
    topic,
    status,
    priority,
    channel,
    created_at,
    updated_at,
    embedding_json,
    embedding_model,
    embedding_sha256,
    embedded_at,
    '' AS semantic_query
FROM support_tickets</textarea></pre>
</div>
<p>The calculated field gives PHPRunner a search condition that we can replace with the IDs returned by semantic search. Do not display <strong>semantic_query</strong> or the four embedding fields on the generated pages. On the List page, keep fields such as <strong>subject</strong> and <strong>description</strong> visible so readers can evaluate the results.</p>
<p>Turn off &#8216;Highlight search results&#8217;. Semantic matches often use different words, so ordinary keyword highlighting is not useful here.</p>
<h3>2. Adding the embedding functions</h3>
<p>Create an API key at <a href="https://platform.openai.com/api-keys" target="_blank" rel="noopener">platform.openai.com/api-keys</a>. In the code below, replace <strong>YOUR_OPENAI_API_KEY</strong> with that key. The key is stored in a server-side session variable and is never sent to browser-side JavaScript.</p>
<p>Open <strong>Events &#8211; Global events &#8211; After App Init</strong> and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if (!defined("DC_EMBEDDING_MODEL")) {
    define("DC_EMBEDDING_MODEL", "text-embedding-3-small");
    define("DC_EMBEDDING_DIMENSIONS", 1536);
}

$_SESSION["DEVCLUB_OPENAI_API_KEY"] = "YOUR_OPENAI_API_KEY";

if (!function_exists("dcTicketText")) {
    function dcTicketText($subject, $description)
    {
        return "Subject: " . (string)$subject .
            "\nDescription: " . (string)$description;
    }
}

if (!function_exists("dcEmbeddingVector")) {
    function dcEmbeddingVector($text)
    {
        $apiKey = trim((string)($_SESSION["DEVCLUB_OPENAI_API_KEY"] ?? ""));

        if ($apiKey === "" || $apiKey === "YOUR_OPENAI_API_KEY") {
            throw new RuntimeException(
                "OpenAI API key is missing from the session."
            );
        }

        if (!function_exists("curl_init")) {
            throw new RuntimeException("The PHP cURL extension is required.");
        }

        $request = json_encode(array(
            "model" => DC_EMBEDDING_MODEL,
            "input" => (string)$text,
            "encoding_format" => "float"
        ), JSON_INVALID_UTF8_SUBSTITUTE);

        if ($request === false) {
            throw new RuntimeException("Cannot create the OpenAI request.");
        }

        $ch = curl_init("https://api.openai.com/v1/embeddings");

        if ($ch === false) {
            throw new RuntimeException("Cannot initialize the OpenAI request.");
        }

        curl_setopt_array($ch, array(
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $request,
            CURLOPT_HTTPHEADER => array(
                "Authorization: Bearer " . $apiKey,
                "Content-Type: application/json",
                "Accept: application/json"
            ),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 15,
            CURLOPT_TIMEOUT => 45,
            CURLOPT_SSL_VERIFYPEER => true
        ));

        $response = curl_exec($ch);
        $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $curlError = curl_error($ch);
        curl_close($ch);

        if ($response === false) {
            throw new RuntimeException(
                "OpenAI connection failed: " . $curlError
            );
        }

        $body = json_decode($response, true);

        if ($httpCode !== 200) {
            $detail = isset($body["error"]["message"])
                ? (string)$body["error"]["message"]
                : "Unexpected HTTP response";
            throw new RuntimeException(
                "OpenAI HTTP " . $httpCode . ": " . substr($detail, 0, 300)
            );
        }

        $vector = $body["data"][0]["embedding"] ?? null;

        if (!is_array($vector) || count($vector) !== DC_EMBEDDING_DIMENSIONS) {
            throw new RuntimeException(
                "OpenAI returned an embedding with an unexpected length."
            );
        }

        foreach ($vector as $value) {
            if (!is_numeric($value) || !is_finite((float)$value)) {
                throw new RuntimeException(
                    "OpenAI returned an invalid embedding value."
                );
            }
        }

        return $vector;
    }
}

if (!function_exists("dcTicketEmbeddingFields")) {
    function dcTicketEmbeddingFields($subject, $description)
    {
        $text = dcTicketText($subject, $description);
        $vector = dcEmbeddingVector($text);
        $vectorJson = json_encode($vector, JSON_PRESERVE_ZERO_FRACTION);

        if ($vectorJson === false) {
            throw new RuntimeException("Cannot save the returned embedding.");
        }

        return array(
            "embedding_json" => $vectorJson,
            "embedding_model" => DC_EMBEDDING_MODEL,
            "embedding_sha256" => hash("sha256", $text),
            "embedded_at" => gmdate("Y-m-d H:i:s")
        );
    }
}</textarea></pre>
</div>
<p>The same text format must be used whenever a ticket embedding is created or checked. The hash lets the search code ignore an embedding if its subject or description no longer matches the stored vector.</p>
<p>The code contains a placeholder, not a real API key. Do not publish or distribute a populated key in project or generated application files. In production, keep the real key in server-side configuration outside the public application files, then assign that value to the session variable in <strong>After App Init</strong>.</p>
<p>Stored ticket vectors and search vectors must use the same embedding model and dimensions. Different models generally produce incompatible vector spaces. Preserve the original ticket text so the vectors can be regenerated if the model is replaced or discontinued. Regeneration uses the Embeddings API again and incurs API usage costs, although those costs are generally modest for small datasets.</p>
<h3>3. Generating an embedding when a ticket is added</h3>
<p>Open the <strong>support_tickets Add page &#8211; Before record added</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
try {
    $subject = trim((string)($values["subject"] ?? ""));
    $description = trim((string)($values["description"] ?? ""));

    if ($subject === "" || $description === "") {
        throw new RuntimeException("Subject and description are required.");
    }

    $embeddingFields = dcTicketEmbeddingFields($subject, $description);

    foreach ($embeddingFields as $field => $value) {
        $values[$field] = $value;
    }
} catch (Throwable $e) {
    $message = "Ticket was not saved: " . $e->getMessage();
    return false;
}

return true;</textarea></pre>
</div>
<p>The request is made on the server before the record is saved. The API key and returned vector are never sent to the browser. If the API request fails, the event returns <strong>false</strong>, so the ticket is not added with a missing or partial embedding.</p>
<h3>4. Refreshing the embedding when a ticket is edited</h3>
<p>Open the <strong>support_tickets Edit page &#8211; Before record updated</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
try {
    $subject = trim((string)($values["subject"] ?? $oldvalues["subject"]));
    $description = trim((string)(
        $values["description"] ?? $oldvalues["description"]
    ));

    if ($subject === "" || $description === "") {
        throw new RuntimeException("Subject and description are required.");
    }

    $oldText = dcTicketText(
        $oldvalues["subject"],
        $oldvalues["description"]
    );
    $newText = dcTicketText($subject, $description);

    if ($newText !== $oldText) {
        $embeddingFields = dcTicketEmbeddingFields($subject, $description);

        foreach ($embeddingFields as $field => $value) {
            $values[$field] = $value;
        }
    }
} catch (Throwable $e) {
    $message = "Ticket was not saved: " . $e->getMessage();
    return false;
}

return true;</textarea></pre>
</div>
<p>The API is called only when <strong>subject</strong> or <strong>description</strong> changes. Editing another field keeps the current embedding. If the API request fails, returning <strong>false</strong> prevents the update, so the previous ticket text and embedding remain unchanged.</p>
<h3>5. Populating embeddings for existing records</h3>
<p>New and edited tickets now receive embeddings automatically. If you imported the supplied sample SQL, its 500 records already include embeddings. Use the following utility only for other existing records that do not have embeddings yet.</p>
<p>Copy the supplied <strong>populate_embeddings.php</strong> file to the root of the generated PHPRunner application, next to <strong>support_tickets_list.php</strong>. Open the file in a browser and click &#8216;Start / Resume&#8217;. It processes 20 tickets per request, skips current embeddings, and continues until all records are ready.</p>
<p>The utility allows access only from localhost or by a PHPRunner administrator. Delete it from the generated application after the initial population is complete.</p>
<h3>6. Replacing the basic search with semantic search</h3>
<p>Open the <strong>support_tickets List page &#8211; Before process</strong> event and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$srch = SearchClause::getSearchObject("support_tickets");
$phrase = trim((string)$srch->getAllFieldsSearchValue());

if ($phrase !== "") {
    $cacheKey = hash("sha256", DC_EMBEDDING_MODEL . "\n" . $phrase);
    $cached = $_SESSION["DC_SEMANTIC_CACHE"] ?? null;
    $ids = null;

    unset($_SESSION["DC_SEMANTIC_ERROR"]);

    $cacheValid = is_array($cached)
        && isset($cached["key"], $cached["time"], $cached["ids"])
        && $cached["key"] === $cacheKey;

    if ($cacheValid) {
        $cacheAge = time() - (int)$cached["time"];
        $cacheValid = $cacheAge >= 0 && !($cacheAge > 300);
    }

    if ($cacheValid) {
        $ids = $cached["ids"];
    }

    if ($ids === null) {
        try {
            if (strlen($phrase) > 1000) {
                throw new RuntimeException(
                    "Search phrase is too long. The limit is 1000 bytes."
                );
            }

            $queryVector = dcEmbeddingVector($phrase);
            $queryNorm = 0.0;

            foreach ($queryVector as $value) {
                $number = (float)$value;
                $queryNorm += $number * $number;
            }

            if ($queryNorm === 0.0) {
                throw new RuntimeException("The query vector has zero length.");
            }

            $sql = DB::PrepareSQL(
                "SELECT ticket_id, subject, description, " .
                "embedding_json, embedding_sha256 " .
                "FROM support_tickets " .
                "WHERE embedding_json IS NOT NULL " .
                "AND embedding_model=':1'",
                DC_EMBEDDING_MODEL
            );
            $rs = DB::Query($sql);

            if (!$rs) {
                throw new RuntimeException(
                    "Database query failed: " . DB::LastError()
                );
            }

            $scores = array();

            while ($row = $rs->fetchAssoc()) {
                $ticketText = dcTicketText(
                    $row["subject"],
                    $row["description"]
                );
                $expectedHash = hash("sha256", $ticketText);

                if (!hash_equals(
                    (string)$row["embedding_sha256"],
                    $expectedHash
                )) {
                    continue;
                }

                $vector = json_decode(
                    (string)$row["embedding_json"],
                    true
                );

                if (!is_array($vector)
                    || count($vector) !== DC_EMBEDDING_DIMENSIONS) {
                    continue;
                }

                $dotProduct = 0.0;
                $vectorNorm = 0.0;

                foreach ($vector as $index => $value) {
                    if (!is_numeric($value)) {
                        continue 2;
                    }

                    $number = (float)$value;

                    if (!is_finite($number)) {
                        continue 2;
                    }

                    $dotProduct += (float)$queryVector[$index] * $number;
                    $vectorNorm += $number * $number;
                }

                if ($vectorNorm > 0.0) {
                    $scores[(int)$row["ticket_id"]] =
                        $dotProduct / sqrt($queryNorm * $vectorNorm);
                }
            }

            arsort($scores, SORT_NUMERIC);
            $ids = array_slice(array_keys($scores), 0, 30);

            $_SESSION["DC_SEMANTIC_CACHE"] = array(
                "key" => $cacheKey,
                "time" => time(),
                "ids" => $ids
            );
        } catch (Throwable $e) {
            $_SESSION["DC_SEMANTIC_ERROR"] = $e->getMessage();
            $ids = array();
            unset($_SESSION["DC_SEMANTIC_CACHE"]);
        }
    }

    $safeIds = array_map("intval", $ids);
    $condition = $safeIds
        ? "ticket_id IN (" . implode(",", $safeIds) . ")"
        : "1=0";

    $srch->setSearchSQL("semantic_query", $condition);
}</textarea></pre>
</div>
<p>The code sends only the search phrase to OpenAI. The ticket database remains on your server. PHPRunner reads the stored vectors, calculates cosine similarity in PHP, and keeps the 30 highest-scoring ticket IDs. Repeating the same query within five minutes uses the session cache instead of creating another embedding.</p>
<h3>7. Sorting tickets by similarity</h3>
<p>The previous event filters the grid to the 30 matching IDs. MySQL still needs an explicit sort expression to display them in similarity order.</p>
<p>Open the <strong>support_tickets List page &#8211; Before SQL query</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$srch = SearchClause::getSearchObject("support_tickets");
$phrase = trim((string)$srch->getAllFieldsSearchValue());
$cached = $_SESSION["DC_SEMANTIC_CACHE"] ?? null;
$cacheKey = hash("sha256", DC_EMBEDDING_MODEL . "\n" . $phrase);

$cacheValid = $phrase !== ""
    && is_array($cached)
    && isset($cached["key"], $cached["time"], $cached["ids"])
    && $cached["key"] === $cacheKey
    && !empty($cached["ids"]);

if ($cacheValid) {
    $cacheAge = time() - (int)$cached["time"];
    $cacheValid = $cacheAge >= 0 && !($cacheAge > 300);
}

if ($cacheValid) {
    $safeIds = array_map("intval", $cached["ids"]);
    $strOrderBy = " ORDER BY FIELD(ticket_id, " .
        implode(",", $safeIds) . ")";
}</textarea></pre>
</div>
<p>The MySQL <strong>FIELD()</strong> function follows the ID order produced by the similarity scores. The most relevant ticket appears first instead of the records being sorted by their numeric IDs.</p>
<h3>8. Displaying semantic-search errors</h3>
<p>Open the <strong>support_tickets List page &#8211; Before display</strong> event and add this code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if (!empty($_SESSION["DC_SEMANTIC_ERROR"])) {
    $message = "Semantic search failed: " .
        $_SESSION["DC_SEMANTIC_ERROR"];

    $xt->assign(
        "message",
        htmlspecialchars(
            $message,
            ENT_QUOTES | ENT_SUBSTITUTE,
            "UTF-8"
        )
    );
}</textarea></pre>
</div>
<p>Rebuild the application, populate the existing embeddings, and enter a natural-language phrase in the List page&#8217;s basic search box. The grid should return the 30 tickets whose subject and description are closest in meaning, with the strongest match at the top.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/10/18/add-semantic-search-to-a-phprunner-application/feed/</wfw:commentRss>
			<slash:comments>1</slash:comments>
		
		
			</item>
		<item>
		<title>Track Employee Training Video Completion</title>
		<link>https://xlinesoft.com/blog/2026/10/15/track-employee-training-video-completion/</link>
					<comments>https://xlinesoft.com/blog/2026/10/15/track-employee-training-video-completion/#respond</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Thu, 15 Oct 2026 23:26:41 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3514</guid>

					<description><![CDATA[Build a training video tracker in PHPRunner or ASPRunner.NET that saves playback position, prevents skipping, records completion, and displays progress.]]></description>
										<content:encoded><![CDATA[<p>Employees often need to complete onboarding, safety, or compliance videos. In this tutorial, we will build a training video tracker that resumes each video from the saved position, allows rewinding, prevents users from skipping unwatched sections, and records completion.</p>
<p>The same approach works in PHPRunner and ASPRunner.NET. The browser-side code is the same in both products, while the server-side examples are provided separately in PHP and C#.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr-1024x614.png" alt="" width="810" height="486" class="alignnone size-large wp-image-3521" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr-1024x614.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr-600x360.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr-768x461.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/scr.png 1209w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3514"></span></p>
<h3>1. Creating the required tables</h3>
<p>This example uses the <strong>employees</strong> table for application login. Its key field is <strong>EmployeeID</strong>. The sample project uses <strong>FirstName</strong> as the username field and <strong>LastName</strong> as the password field. Create the three tables below.</p>
<p><strong>MySQL</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE employees (
    EmployeeID INT NOT NULL AUTO_INCREMENT,
    LastName VARCHAR(20) NOT NULL,
    FirstName VARCHAR(10) NOT NULL,
    Title VARCHAR(30) NULL,
    TitleOfCourtesy VARCHAR(25) NULL,
    BirthDate DATETIME NULL,
    HireDate DATETIME NULL,
    Address VARCHAR(60) NULL,
    City VARCHAR(11) NULL,
    Region VARCHAR(15) NULL,
    PostalCode VARCHAR(10) NULL,
    Country VARCHAR(15) NULL,
    HomePhone VARCHAR(24) NULL,
    Extension VARCHAR(4) NULL,
    Photo LONGBLOB NULL,
    Notes LONGTEXT NULL,
    ReportsTo DATETIME NULL,
    ownerid INT NULL,
    email VARCHAR(250) NULL,
    PRIMARY KEY (EmployeeID)
);

CREATE TABLE videos (
    video_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    video_file LONGTEXT NULL,
    duration INT NULL,
    PRIMARY KEY (video_id)
);

CREATE TABLE video_watch (
    watch_id INT NOT NULL AUTO_INCREMENT,
    employee_id INT NOT NULL,
    video_id INT NOT NULL,
    last_position DECIMAL(10,2) NOT NULL DEFAULT 0,
    max_watched DECIMAL(10,2) NOT NULL DEFAULT 0,
    completed TINYINT NOT NULL DEFAULT 0,
    last_watched DATETIME NULL,
    completed_at DATETIME NULL,
    PRIMARY KEY (watch_id),
    UNIQUE KEY uq_video_watch (employee_id, video_id)
);</textarea></pre>
</div>
<p>The two position fields serve different purposes. <strong>last_position</strong> is where playback resumes. <strong>max_watched</strong> is the furthest point the employee has reached and therefore the furthest position to which the player may seek.</p>
<p>Add both tables to the project. Configure <strong>employees</strong> as the login table and make sure <strong>EmployeeID</strong> is available through the current user data.</p>
<h3>2. Configuring video upload and playback</h3>
<p>Open the <strong>videos</strong> table and configure <strong>video_file</strong> as follows:</p>
<p>1. On the Add and Edit pages, set &#8216;Edit as&#8217; to &#8216;Document upload&#8217;.<br />
2. On the View page, set &#8216;View as&#8217; to &#8216;Video file&#8217;.<br />
3. Set the video player size. The sample project uses 600 by 400 pixels.<br />
4. Include <strong>title</strong>, <strong>description</strong>, and <strong>video_file</strong> on the View page.<br />
5. Remove <strong>duration</strong> from the Add and Edit pages. The application will save it automatically when the video is played.</p>
<p>The generated application uses the browser&#8217;s HTML5 video player, so no additional video library is required.</p>
<h3>3. Processing progress requests on the server</h3>
<p>The video page sends three AJAX requests: load the current progress, save the video duration, and save the current playback position.</p>
<p>Open <strong>Events &#8211; Global events &#8211; After App Init</strong> and add the version for your product.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if (
    $_SERVER["REQUEST_METHOD"] === "POST" &&
    in_array(
        $_POST["action"] ?? "",
        array(
            "get_video_progress",
            "save_video_progress",
            "save_video_duration"
        ),
        true
    )
) {
    header("Content-Type: application/json");

    function videoResponse($data, $status = 200) {
        http_response_code($status);
        echo json_encode($data);
        exit;
    }

    if (!Security::isLoggedIn()) {
        videoResponse(array("success" => false), 401);
    }

    $user = Security::currentUserData();
    $employeeId = (int)($user["EmployeeID"] ?? 0);
    $videoId = filter_var(
        $_POST["video_id"] ?? null,
        FILTER_VALIDATE_INT
    );

    if ($employeeId <= 0 || !$videoId || $videoId <= 0) {
        videoResponse(array("success" => false), 400);
    }

    $video = DB::Select(
        "videos",
        array("video_id" => $videoId)
    )->fetchAssoc();

    if (!$video) {
        videoResponse(array("success" => false), 404);
    }

    $keys = array(
        "employee_id" => $employeeId,
        "video_id" => $videoId
    );

    $watch = DB::Select("video_watch", $keys)->fetchAssoc();

    if (!$watch) {
        DB::Insert("video_watch", array(
            "employee_id" => $employeeId,
            "video_id" => $videoId,
            "last_position" => 0,
            "max_watched" => 0,
            "completed" => 0
        ));

        $watch = DB::Select("video_watch", $keys)->fetchAssoc();
    }

    $action = $_POST["action"];

    if ($action === "get_video_progress") {
        if (empty($_SESSION["video_progress_token"])) {
            $_SESSION["video_progress_token"] =
                bin2hex(random_bytes(32));
        }

        videoResponse(array(
            "success" => true,
            "last_position" => (float)$watch["last_position"],
            "max_watched" => (float)$watch["max_watched"],
            "completed" => (int)$watch["completed"],
            "token" => $_SESSION["video_progress_token"]
        ));
    }

    $token = $_POST["token"] ?? "";

    if (
        !is_string($token) ||
        empty($_SESSION["video_progress_token"]) ||
        !hash_equals($_SESSION["video_progress_token"], $token)
    ) {
        videoResponse(array("success" => false), 403);
    }

    if ($action === "save_video_duration") {
        $duration = filter_var(
            $_POST["duration"] ?? null,
            FILTER_VALIDATE_INT
        );

        if (!$duration || $duration < 1 || $duration > 86400) {
            videoResponse(array("success" => false), 400);
        }

        if (empty($video["duration"])) {
            DB::Update(
                "videos",
                array("duration" => $duration),
                array("video_id" => $videoId)
            );
        }

        videoResponse(array("success" => true));
    }

    $position = filter_var(
        $_POST["position"] ?? null,
        FILTER_VALIDATE_FLOAT
    );
    $maxWatched = filter_var(
        $_POST["max_watched"] ?? null,
        FILTER_VALIDATE_FLOAT
    );

    if (
        $position === false || $position === null ||
        $maxWatched === false || $maxWatched === null ||
        $position < 0 || $maxWatched < 0 ||
        $position > $maxWatched + 0.5
    ) {
        videoResponse(array("success" => false), 400);
    }

    $duration = (float)($video["duration"] ?? 0);

    if (
        $duration > 0 &&
        ($position > $duration + 1 || $maxWatched > $duration + 1)
    ) {
        videoResponse(array("success" => false), 400);
    }

    $maxWatched = max(
        (float)$watch["max_watched"],
        $maxWatched
    );

    $values = array(
        "last_position" => $position,
        "max_watched" => $maxWatched,
        "last_watched" => date("Y-m-d H:i:s")
    );

    if (
        $duration > 0 &&
        $maxWatched >= $duration - 1.5 &&
        ($_POST["ended"] ?? "") === "1"
    ) {
        $values["completed"] = 1;

        if (empty($watch["completed"])) {
            $values["completed_at"] = date("Y-m-d H:i:s");
        }
    }

    DB::Update("video_watch", $values, $keys);
    videoResponse(array("success" => true));
}</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
string action = MVCFunctions.postvalue("action").ToString();

if (
    HttpContext.Current.Request.HttpMethod == "POST" &&
    (
        action == "get_video_progress" ||
        action == "save_video_progress" ||
        action == "save_video_duration"
    )
)
{
    Action<XVar, int> videoResponse = (payload, status) =>
    {
        HttpContext.Current.Response.StatusCode = status;
        HttpContext.Current.Response.ContentType = "application/json";
        MVCFunctions.Echo(MVCFunctions.runner_json_encode(payload));
        HttpContext.Current.Response.End();
        throw new RunnerInlineOutputException();
    };

    if (!XVar.Pack(Security.isLoggedIn()))
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 401);
    }

    XVar user = Security.currentUserData();
    int employeeId = 0;
    int videoId = 0;

    Int32.TryParse(user["EmployeeID"].ToString(), out employeeId);
    Int32.TryParse(
        MVCFunctions.postvalue("video_id").ToString(),
        out videoId
    );

    if (employeeId <= 0 || videoId <= 0)
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    XVar videoKeys = XVar.Array();
    videoKeys["video_id"] = videoId;

    dynamic video = DB.Select("videos", videoKeys).fetchAssoc();

    if (!XVar.Pack(video))
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 404);
    }

    XVar keys = XVar.Array();
    keys["employee_id"] = employeeId;
    keys["video_id"] = videoId;

    dynamic watch = DB.Select("video_watch", keys).fetchAssoc();

    if (!XVar.Pack(watch))
    {
        XVar values = XVar.Array();
        values["employee_id"] = employeeId;
        values["video_id"] = videoId;
        values["last_position"] = 0;
        values["max_watched"] = 0;
        values["completed"] = 0;

        DB.Insert("video_watch", values);
        watch = DB.Select("video_watch", keys).fetchAssoc();
    }

    if (action == "get_video_progress")
    {
        string sessionToken =
            XSession.Session["video_progress_token"].ToString();

        if (String.IsNullOrEmpty(sessionToken))
        {
            sessionToken = Guid.NewGuid().ToString("N");
            XSession.Session["video_progress_token"] = sessionToken;
        }

        XVar response = XVar.Array();
        response["success"] = true;
        response["last_position"] =
            Convert.ToDouble(watch["last_position"]);
        response["max_watched"] =
            Convert.ToDouble(watch["max_watched"]);
        response["completed"] =
            Convert.ToInt32(watch["completed"]);
        response["token"] = sessionToken;

        videoResponse(response, 200);
    }

    string savedToken =
        XSession.Session["video_progress_token"].ToString();
    string suppliedToken =
        MVCFunctions.postvalue("token").ToString();

    if (
        String.IsNullOrEmpty(savedToken) ||
        !String.Equals(
            savedToken,
            suppliedToken,
            StringComparison.Ordinal
        )
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 403);
    }

    if (action == "save_video_duration")
    {
        int durationValue = 0;
        Int32.TryParse(
            MVCFunctions.postvalue("duration").ToString(),
            out durationValue
        );

        if (durationValue < 1 || durationValue > 86400)
        {
            XVar response = XVar.Array();
            response["success"] = false;
            videoResponse(response, 400);
        }

        if (
            String.IsNullOrEmpty(video["duration"].ToString()) ||
            Convert.ToInt32(video["duration"]) == 0
        )
        {
            XVar values = XVar.Array();
            values["duration"] = durationValue;
            DB.Update("videos", values, videoKeys);
        }

        XVar response = XVar.Array();
        response["success"] = true;
        videoResponse(response, 200);
    }

    double position = 0;
    double maxWatched = 0;

    bool validPosition = Double.TryParse(
        MVCFunctions.postvalue("position").ToString(),
        System.Globalization.NumberStyles.Float,
        System.Globalization.CultureInfo.InvariantCulture,
        out position
    );
    bool validMaximum = Double.TryParse(
        MVCFunctions.postvalue("max_watched").ToString(),
        System.Globalization.NumberStyles.Float,
        System.Globalization.CultureInfo.InvariantCulture,
        out maxWatched
    );

    if (
        !validPosition || !validMaximum ||
        position < 0 || maxWatched < 0 ||
        position > maxWatched + 0.5
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    double duration = 0;
    Double.TryParse(video["duration"].ToString(), out duration);

    if (
        duration > 0 &&
        (position > duration + 1 || maxWatched > duration + 1)
    )
    {
        XVar response = XVar.Array();
        response["success"] = false;
        videoResponse(response, 400);
    }

    maxWatched = Math.Max(
        Convert.ToDouble(watch["max_watched"]),
        maxWatched
    );

    XVar progress = XVar.Array();
    progress["last_position"] = position;
    progress["max_watched"] = maxWatched;
    progress["last_watched"] = DateTime.Now;

    if (
        duration > 0 &&
        maxWatched >= duration - 1.5 &&
        MVCFunctions.postvalue("ended").ToString() == "1"
    )
    {
        progress["completed"] = 1;

        if (Convert.ToInt32(watch["completed"]) == 0)
        {
            progress["completed_at"] = DateTime.Now;
        }
    }

    DB.Update("video_watch", progress, keys);

    XVar successResponse = XVar.Array();
    successResponse["success"] = true;
    videoResponse(successResponse, 200);
}</textarea></pre>
</div>
<p>The handler always obtains <strong>employee_id</strong> from the logged-in user&#8217;s record. It never accepts an employee ID from the browser. A session token protects the update requests, and the server records completion only when the player reaches the end of the video.</p>
<p>If your login table uses a different key field, replace <strong>EmployeeID</strong> in this code.</p>
<h3>4. Tracking playback on the View page</h3>
<p>Open the <strong>videos</strong> View page and add the following code to its <strong>JavaScript OnLoad</strong> event:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
const video = document.querySelector("video");

if (video) {
    const params = new URLSearchParams(window.location.search);
    const videoId = params.get("editid1");
    const url = window.location.pathname + window.location.search;

    let durationSaved = false;
    let lastPosition = 0;
    let maxWatched = 0;
    let restoringPosition = false;
    let initialized = false;
    let completed = false;
    let token = "";
    let lastSave = 0;
    let saving = false;
    let pendingSave = false;

    function request(action, data = {}) {
        return $.ajax({
            url: url,
            type: "POST",
            dataType: "json",
            data: {
                action: action,
                video_id: videoId,
                ...data
            }
        });
    }

    function loadProgress() {
        video.pause();

        request("get_video_progress")
            .done(function(result) {
                if (!result.success) return;

                lastPosition = Number(result.last_position) || 0;
                maxWatched = Number(result.max_watched) || 0;
                completed = Number(result.completed) === 1;
                token = result.token || "";

                if (completed && Number.isFinite(video.duration)) {
                    maxWatched = video.duration;
                }

                restoringPosition = true;
                video.currentTime = Math.min(
                    lastPosition,
                    Math.max(0, video.duration - 0.25)
                );

                video.addEventListener(
                    "seeked",
                    function finishRestore() {
                        restoringPosition = false;
                        video.removeEventListener(
                            "seeked",
                            finishRestore
                        );
                    }
                );

                initialized = true;
            });
    }

    function saveVideoDuration() {
        if (durationSaved || !videoId || !token) return;

        const duration = Math.ceil(video.duration);

        if (!Number.isFinite(duration) || duration <= 0) return;

        durationSaved = true;

        request("save_video_duration", {
            duration: duration,
            token: token
        }).fail(function() {
            durationSaved = false;
        });
    }

    function saveProgress(ended = false) {
        if (!initialized || !token) return;

        if (saving) {
            pendingSave = true;
            return;
        }

        saving = true;

        const position = Math.max(
            0,
            Math.min(video.currentTime, maxWatched)
        );

        request("save_video_progress", {
            position: position.toFixed(2),
            max_watched: maxWatched.toFixed(2),
            ended: ended ? "1" : "0",
            token: token
        }).always(function() {
            saving = false;

            if (pendingSave) {
                pendingSave = false;
                saveProgress(video.ended);
            }
        });
    }

    function initialize() {
        if (initialized) return;
        if (!Number.isFinite(video.duration)) return;
        loadProgress();
    }

    video.addEventListener("loadedmetadata", initialize);

    if (video.readyState >= 1) {
        initialize();
    }

    video.addEventListener("play", function() {
        if (!initialized) {
            video.pause();
            return;
        }

        saveVideoDuration();
    });

    video.addEventListener("timeupdate", function() {
        if (!initialized || restoringPosition || video.seeking) {
            return;
        }

        const current = video.currentTime;

        if (
            !video.paused &&
            current > maxWatched &&
            current <= maxWatched + 2
        ) {
            maxWatched = current;
        }

        const now = Date.now();

        if (!video.paused &#038;&#038; now - lastSave >= 5000) {
            lastSave = now;
            saveProgress();
        }
    });

    video.addEventListener("seeking", function() {
        if (!initialized || restoringPosition) return;

        if (video.currentTime > maxWatched + 0.5) {
            restoringPosition = true;
            video.currentTime = maxWatched;
        }
    });

    video.addEventListener("seeked", function() {
        if (restoringPosition) {
            restoringPosition = false;
        }
    });

    video.addEventListener("pause", function() {
        if (initialized && !video.ended) {
            saveProgress();
        }
    });

    video.addEventListener("ended", function() {
        maxWatched = Math.max(maxWatched, video.duration);
        completed = true;
        saveProgress(true);
    });
}</textarea></pre>
</div>
<p>Progress is saved every five seconds during playback, when the video is paused, and when it ends. A completed video can be played or searched normally. For an unfinished video, the user may rewind but cannot seek beyond <strong>max_watched</strong>.</p>
<p>This browser-side restriction is suitable for ordinary training workflows. Like any client-side playback control, it should not be treated as tamper-proof compliance evidence against a user deliberately modifying browser requests.</p>
<h3>5. Opening the video from the List page</h3>
<p>Open the <strong>videos</strong> List page in Page Designer. Select the grid row and configure its click action to open the View page in the same browser window.</p>
<p>Users can now click anywhere on a video row to open the player. This uses the built-in row click action and does not require additional JavaScript.</p>
<h3>6. Showing progress with row colors</h3>
<p>Open the <strong>videos</strong> List page and add the appropriate code to <strong>After record processed</strong>.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >$user = Security::currentUserData();

$keys = array(
    "employee_id" => (int)$user["EmployeeID"],
    "video_id" => (int)$data["video_id"]
);

$watch = DB::Select("video_watch", $keys)->fetchAssoc();

if (!$watch || (float)$watch["max_watched"] &lt;= 0) {
    $record["css"] = "background:#fce8e6;";
} elseif ((int)$watch["completed"] === 1) {
    $record["css"] = "background:#e6f4ea;";
} else {
    $record["css"] = "background:#fff4cc;";
}</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >XVar user = Security.currentUserData();

XVar keys = XVar.Array();
keys["employee_id"] = Convert.ToInt32(user["EmployeeID"]);
keys["video_id"] = Convert.ToInt32(data["video_id"]);

dynamic watch = DB.Select("video_watch", keys).fetchAssoc();

if (
    !XVar.Pack(watch) ||
    Convert.ToDouble(watch["max_watched"]) &lt;= 0
)
{
    record["css"] = "background:#fce8e6;";
}
else if (Convert.ToInt32(watch["completed"]) == 1)
{
    record["css"] = "background:#e6f4ea;";
}
else
{
    record["css"] = "background:#fff4cc;";
}</textarea></pre>
</div>
<p>Pink rows have not been started, yellow rows are in progress, and green rows are complete. The query uses the logged-in employee&#8217;s ID, so every employee sees their own progress.</p>
<h3>7. Testing the completed application</h3>
<p>Build the project and log in as an employee. Open a video, watch part of it, pause, and leave the page. When you return, playback should resume from the saved position. Rewinding should work, while attempts to seek beyond the furthest watched position should return the player to that position.</p>
<p>After the video reaches the end, its List-page row should change to green. Log in as another employee to confirm that progress is stored separately for each user.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/10/15/track-employee-training-video-completion/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Build an Equipment Checkout System with QR Code Scanning</title>
		<link>https://xlinesoft.com/blog/2026/10/02/build-an-equipment-checkout-system-with-qr-code-scanning/</link>
					<comments>https://xlinesoft.com/blog/2026/10/02/build-an-equipment-checkout-system-with-qr-code-scanning/#respond</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Fri, 02 Oct 2026 22:05:12 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3505</guid>

					<description><![CDATA[Create an equipment checkout workflow in PHPRunner and ASPRunner.NET with status-aware actions, popup forms, overdue highlighting, and camera-based QR code scanning.]]></description>
										<content:encoded><![CDATA[<p>Equipment checkout applications need to do more than store a list of assets. The application should prevent duplicate checkouts, show only actions that match the current asset status, record returns, flag overdue equipment, and let staff find an item quickly.</p>
<p>In this tutorial, we will build that workflow in PHPRunner or ASPRunner.NET and add camera-based QR code scanning. The JavaScript is the same in both products. Server events are provided in PHP and C#.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement-1024x615.png" alt="" width="810" height="486" class="alignnone size-large wp-image-3510" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement-1024x615.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement-600x360.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement-768x461.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/assetmanagement.png 1504w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3505"></span></p>
<h3>1. Creating the tables and relationships</h3>
<p>This example uses three tables. Adjust the field types for your database if necessary.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE assets (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    asset_tag VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100),
    serial_number VARCHAR(100),
    location VARCHAR(150),
    status VARCHAR(30) NOT NULL DEFAULT 'Available',
    `condition` VARCHAR(30) DEFAULT 'Good',
    notes TEXT
);

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(255),
    department VARCHAR(100),
    active TINYINT NOT NULL DEFAULT 1
);

CREATE TABLE asset_checkouts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    asset_id INT NOT NULL,
    employee_id INT NOT NULL,
    checked_out_at DATETIME,
    due_date DATE,
    returned_at DATETIME,
    condition_out VARCHAR(30),
    condition_in VARCHAR(30),
    notes TEXT
);</textarea></pre>
</div>
<p>Add all three tables to the project. Create master-details relationships from <strong>assets.id</strong> to <strong>asset_checkouts.asset_id</strong> and from <strong>employees.id</strong> to <strong>asset_checkouts.employee_id</strong>.</p>
<p>Set <strong>employee_id</strong> to a Lookup wizard that displays <strong>employees.full_name</strong>. Use fixed lookup values for <strong>assets.status</strong>, <strong>assets.condition</strong>, <strong>asset_checkouts.condition_out</strong>, and <strong>asset_checkouts.condition_in</strong>.</p>
<h3>2. Showing the active checkout on the Assets pages</h3>
<p>Open the SQL Query for <strong>assets</strong> and use the following MySQL query. It adds the due date and employee from the most recent checkout that has not been returned.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
SELECT
    a.id,
    a.asset_tag,
    a.name,
    a.category,
    a.serial_number,
    a.location,
    a.status,
    a.`condition`,
    a.notes,
    ac.due_date,
    ac.employee_id
FROM assets a
LEFT JOIN asset_checkouts ac
    ON ac.id = (
        SELECT MAX(ac2.id)
        FROM asset_checkouts ac2
        WHERE ac2.asset_id = a.id
          AND ac2.returned_at IS NULL
    )</textarea></pre>
</div>
<p>Add <strong>due_date</strong> and <strong>employee_id</strong> to the Assets List and View pages. Configure <strong>employee_id</strong> as a Lookup wizard so the employee name is displayed.</p>
<h3>3. Adding the Check out button</h3>
<p>Open the Assets List page in <strong>Page Designer</strong>, switch to Advanced Grid, and add a custom button inside the grid. Set its caption to &#8216;Check out&#8217; and its Item ID to <strong>checkout_button</strong>. Add the same button to the Assets View page.</p>
<p>Add the appropriate code to the button&#8217;s <strong>Server</strong> section.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$record = $button->getCurrentRecord();

if (!$record || $record["status"] !== "Available") {
    $result["error"] = "This equipment is no longer available.";
    return;
}

$result["asset_id"] = $record["id"];</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
dynamic record = button.getCurrentRecord();

if (!XVar.Pack(record) || Convert.ToString(record["status"]) != "Available")
{
    result["error"] = "This equipment is no longer available.";
    return;
}

result["asset_id"] = record["id"];</textarea></pre>
</div>
<p>Add this JavaScript to <strong>Client After</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (result["error"]) {
    swal(result["error"]);
    return;
}

window.popup = Runner.displayPopup({
    url: Runner.pages.getUrl("asset_checkouts", "add")
        + "?mastertable=assets&masterkey1="
        + encodeURIComponent(result["asset_id"]),
    width: 650,
    height: 700,
    header: "Check Out Equipment"
});</textarea></pre>
</div>
<h3>4. Saving a checkout</h3>
<p>On the Asset Checkouts Add page, keep <strong>employee_id</strong>, <strong>due_date</strong>, and <strong>notes</strong> visible. The remaining values will be set on the server.</p>
<p>Add this code to <strong>Before record added</strong>.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$asset = $pageObject->getMasterRecord();

if (!$asset || $asset["status"] !== "Available") {
    $message = "This equipment is no longer available.";
    return false;
}

$values["asset_id"] = $asset["id"];
$values["checked_out_at"] = date("Y-m-d H:i:s");
$values["condition_out"] = $asset["condition"];
$values["returned_at"] = null;
$values["condition_in"] = null;

return true;</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
dynamic asset = pageObject.getMasterRecord();

if (!XVar.Pack(asset) || Convert.ToString(asset["status"]) != "Available")
{
    message = "This equipment is no longer available.";
    return false;
}

values["asset_id"] = asset["id"];
values["checked_out_at"] = DateTime.Now;
values["condition_out"] = asset["condition"];
values["returned_at"] = null;
values["condition_in"] = null;

return true;</textarea></pre>
</div>
<p>Add this code to <strong>After record added</strong>.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
DB::Update(
    "assets",
    array("status" => "Checked Out"),
    array("id" => $values["asset_id"])
);

$pageObject->stopPRG = true;
$pageObject->setProxyValue("checkoutSaved", true);</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
dynamic data = XVar.Array();
dynamic keyValues = XVar.Array();

data["status"] = "Checked Out";
keyValues["id"] = values["asset_id"];
DB.Update("assets", data, keyValues);

pageObject.stopPRG = true;
pageObject.setProxyValue("checkoutSaved", true);</textarea></pre>
</div>
<p>Add this JavaScript to the Add page&#8217;s <strong>JavaScript OnLoad</strong> event:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (proxy.checkoutSaved && window.parent && window.parent.popup) {
    window.parent.location.reload();
    window.parent.popup.close();
}</textarea></pre>
</div>
<h3>5. Adding the Return button</h3>
<p>Add another custom button to the Assets List grid and View page. Set its caption to &#8216;Return&#8217; and its Item ID to <strong>return_button</strong>.</p>
<p>Add the appropriate code to the button&#8217;s <strong>Server</strong> section.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$asset = $button->getCurrentRecord();

if (!$asset || $asset["status"] !== "Checked Out") {
    $result["error"] = "This equipment is not checked out.";
    return;
}

$where = DB::PrepareSQL(
    "asset_id = :1 AND returned_at IS NULL",
    $asset["id"]
);
$rs = DB::Select("asset_checkouts", $where);
$checkout = $rs->fetchAssoc();

if (!$checkout) {
    $result["error"] = "The active checkout record was not found.";
    return;
}

$result["checkout_id"] = $checkout["id"];</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
dynamic asset = button.getCurrentRecord();

if (!XVar.Pack(asset) || Convert.ToString(asset["status"]) != "Checked Out")
{
    result["error"] = "This equipment is not checked out.";
    return;
}

string where = DB.PrepareSQL(
    "asset_id = :1 AND returned_at IS NULL",
    asset["id"]
);
dynamic rs = DB.Select("asset_checkouts", where);
dynamic checkout = rs.fetchAssoc();

if (!XVar.Pack(checkout))
{
    result["error"] = "The active checkout record was not found.";
    return;
}

result["checkout_id"] = checkout["id"];</textarea></pre>
</div>
<p>Add this JavaScript to <strong>Client After</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (result["error"]) {
    swal(result["error"]);
    return;
}

window.popup = Runner.displayPopup({
    url: Runner.pages.getUrl("asset_checkouts", "edit")
        + "?editid1="
        + encodeURIComponent(result["checkout_id"]),
    width: 650,
    height: 500,
    header: "Return Equipment"
});</textarea></pre>
</div>
<h3>6. Processing the return</h3>
<p>Use the Asset Checkouts Edit page only for returns. Keep <strong>condition_in</strong> and <strong>notes</strong> visible and remove ordinary Edit links to this table from other pages.</p>
<p>Add this code to <strong>Before record updated</strong>.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if (!empty($oldvalues["returned_at"])) {
    $message = "This equipment has already been returned.";
    return false;
}

$values["returned_at"] = date("Y-m-d H:i:s");
return true;</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
if (XVar.Pack(oldvalues["returned_at"]))
{
    message = "This equipment has already been returned.";
    return false;
}

values["returned_at"] = DateTime.Now;
return true;</textarea></pre>
</div>
<p>Add this code to <strong>After record updated</strong>.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$status = $values["condition_in"] === "Damaged"
    ? "Maintenance"
    : "Available";

DB::Update(
    "assets",
    array(
        "status" => $status,
        "condition" => $values["condition_in"]
    ),
    array("id" => $oldvalues["asset_id"])
);

$pageObject->stopPRG = true;
$pageObject->setProxyValue("returnSaved", true);</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
string status = Convert.ToString(values["condition_in"]) == "Damaged"
    ? "Maintenance"
    : "Available";

dynamic data = XVar.Array();
dynamic keyValues = XVar.Array();
data["status"] = status;
data["condition"] = values["condition_in"];
keyValues["id"] = oldvalues["asset_id"];
DB.Update("assets", data, keyValues);

pageObject.stopPRG = true;
pageObject.setProxyValue("returnSaved", true);</textarea></pre>
</div>
<p>Add this JavaScript to the Edit page&#8217;s <strong>JavaScript OnLoad</strong> event:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (proxy.returnSaved && window.parent && window.parent.popup) {
    window.parent.location.reload();
    window.parent.popup.close();
}</textarea></pre>
</div>
<h3>7. Showing only valid actions</h3>
<p>Open <strong>Assets List page &#8211; After record processed</strong>. Use the Item IDs assigned earlier so each row shows only the action that applies to its current status.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if ($data["status"] !== "Available") {
    $pageObject->hideItem("checkout_button", $recordId);
}

if ($data["status"] !== "Checked Out") {
    $pageObject->hideItem("return_button", $recordId);
}

if (
    !empty($data["due_date"])
    && strtotime($data["due_date"]) < strtotime(date("Y-m-d"))
) {
    $record["css"] = "background: #ffdddd;";
}
[/php]
<p><strong>C#</strong></p>
[code]
if (Convert.ToString(data["status"]) != "Available")
{
    pageObject.hideItem("checkout_button", recordId);
}

if (Convert.ToString(data["status"]) != "Checked Out")
{
    pageObject.hideItem("return_button", recordId);
}

DateTime dueDate;
if (
    DateTime.TryParse(Convert.ToString(data["due_date"]), out dueDate)
    && dueDate.Date < DateTime.Today
)
{
    record["css"] = "background: #ffdddd;";
}
[/code]
<p>On <strong>Assets View page - Before display</strong>, hide the buttons without a record ID.</p>
<p><strong>PHP</strong></p>
[php]
$asset = $pageObject->getCurrentRecord();

if ($asset["status"] !== "Available") {
    $pageObject->hideItem("checkout_button");
}

if ($asset["status"] !== "Checked Out") {
    $pageObject->hideItem("return_button");
}</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
dynamic asset = pageObject.getCurrentRecord();

if (Convert.ToString(asset["status"]) != "Available")
{
    pageObject.hideItem("checkout_button");
}

if (Convert.ToString(asset["status"]) != "Checked Out")
{
    pageObject.hideItem("return_button");
}</textarea></pre>
</div>
<h3>8. Adding camera-based QR scanning</h3>
<p>Create QR labels whose encoded value is the corresponding <strong>asset_tag</strong>. The scanner only needs that value, so the labels can be produced by the application or by a separate label-printing tool.</p>
<p>Add a custom button above the Assets List grid. Set its caption to &#8216;Scan QR&#8217; and its Item ID to <strong>scan_qr_button</strong>. Add the following JavaScript to <strong>Client Before</strong>. The script loads the <a href="https://github.com/mebjas/html5-qrcode" target="_blank" rel="noopener">html5-qrcode</a> library when it is first needed.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
function openAssetScanner() {
    var scanner;
    var matched = false;
    var closing = false;

    Runner.displayPopup({
        html: '<div id="asset_qr_reader" style="width:100%;"></div>',
        width: 600,
        height: 650,
        header: "Scan Equipment Label",

        afterCreate: function(popup) {
            scanner = new Html5QrcodeScanner(
                "asset_qr_reader",
                {
                    fps: 10,
                    qrbox: { width: 250, height: 250 },
                    formatsToSupport: [
                        Html5QrcodeSupportedFormats.QR_CODE
                    ],
                    supportedScanTypes: [
                        Html5QrcodeScanType.SCAN_TYPE_CAMERA
                    ]
                },
                false
            );

            scanner.render(function(decodedText) {
                if (matched || closing) {
                    return;
                }

                matched = true;
                params["asset_tag"] = decodedText.trim();
                popup.close();
            });
        },

        beforeClose: function(popup) {
            if (closing) {
                return true;
            }

            closing = true;

            scanner.clear().then(function() {
                popup.close();

                if (matched) {
                    ajax.submit();
                }
            }).catch(function() {
                closing = false;
                matched = false;
                swal("Stop scanning before closing the window.");
            });

            return false;
        }
    });
}

if (window.Html5QrcodeScanner) {
    openAssetScanner();
} else {
    $.getScript(
        "https://unpkg.com/html5-qrcode@2.3.8/html5-qrcode.min.js"
    ).done(openAssetScanner).fail(function() {
        swal("The QR scanner library could not be loaded.");
    });
}

return false;</textarea></pre>
</div>
<p>Add the appropriate lookup code to the button&#8217;s <strong>Server</strong> section.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$tag = trim((string)$params["asset_tag"]);
$rs = DB::Select("assets", array("asset_tag" => $tag));
$asset = $rs->fetchAssoc();

if (!$asset) {
    $result["error"] = "No equipment matches this QR code.";
    return;
}

$result["asset_id"] = $asset["id"];</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
string tag = Convert.ToString(parameters["asset_tag"]).Trim();
dynamic values = XVar.Array();
values["asset_tag"] = tag;

dynamic rs = DB.Select("assets", values);
dynamic asset = rs.fetchAssoc();

if (!XVar.Pack(asset))
{
    result["error"] = "No equipment matches this QR code.";
    return;
}

result["asset_id"] = asset["id"];</textarea></pre>
</div>
<p>Add this JavaScript to <strong>Client After</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (result["error"]) {
    swal(result["error"]);
    return;
}

location.href = Runner.pages.getUrl("assets", "view")
    + "?editid1="
    + encodeURIComponent(result["asset_id"]);</textarea></pre>
</div>
<p>Camera access normally requires HTTPS, except during local development. Rebuild the project, open the Assets List page on a phone or laptop with a camera, scan an asset label, and confirm that the correct View page opens. Then test checkout, return, damaged-item handling, conditional buttons, and overdue highlighting.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/10/02/build-an-equipment-checkout-system-with-qr-code-scanning/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Process Large CSV Exports in Background Batches</title>
		<link>https://xlinesoft.com/blog/2026/10/02/process-large-csv-exports-in-background-batches/</link>
					<comments>https://xlinesoft.com/blog/2026/10/02/process-large-csv-exports-in-background-batches/#respond</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Fri, 02 Oct 2026 22:00:43 +0000</pubDate>
				<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3506</guid>

					<description><![CDATA[Build a PHPRunner export queue that processes large CSV files in scheduled batches, reports progress, and provides a ZIP download when complete.]]></description>
										<content:encoded><![CDATA[<p>Exporting hundreds of thousands of records during a normal page request can exceed PHP execution limits and leave the user waiting without any indication of progress.</p>
<p>This PHPRunner tutorial moves large CSV exports into a scheduled worker. Users submit an export request, the worker processes a fixed number of records per run, and the List page shows progress until the ZIP file is ready to download.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue-1024x569.png" alt="" width="810" height="450" class="alignnone size-large wp-image-3512" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue-1024x569.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue-600x334.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue-768x427.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/10/export_queue.png 1309w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3506"></span></p>
<h3>1. Creating the export queue</h3>
<p>Create an <strong>exports</strong> table and add it to the PHPRunner project. This example supports two export types and optional date limits.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="sql" name="mshighlighter" >
CREATE TABLE exports (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME NOT NULL,
    date_from DATE NULL,
    date_to DATE NULL,
    status VARCHAR(20) NOT NULL,
    total_records INT NOT NULL DEFAULT 0,
    processed_records INT NOT NULL DEFAULT 0,
    filename VARCHAR(255) NULL,
    completed_at DATETIME NULL,
    task_type VARCHAR(50) NOT NULL
);</textarea></pre>
</div>
<p>On the Add page, show <strong>date_from</strong>, <strong>date_to</strong>, and <strong>task_type</strong>. Configure <strong>task_type</strong> as a Lookup wizard with the fixed values <strong>orders</strong> and <strong>transactions</strong>. Keep the worker-managed fields off the Add and Edit pages.</p>
<h3>2. Queuing a new export</h3>
<p>Open <strong>Exports Add page &#8211; Before record added</strong> and add:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$allowedTypes = array("orders", "transactions");

if (!in_array($values["task_type"], $allowedTypes, true)) {
    $message = "Select a valid export type.";
    return false;
}

$values["created_at"] = date("Y-m-d H:i:s");
$values["status"] = "Pending";
$values["total_records"] = 0;
$values["processed_records"] = 0;
$values["filename"] = null;
$values["completed_at"] = null;

return true;</textarea></pre>
</div>
<p>The user only creates a queue record. No export data is generated during the Add request.</p>
<h3>3. Adding the scheduled worker</h3>
<p>Create <strong>cron.php</strong> in the generated application folder. This example uses MySQL syntax and processes up to 100,000 rows during each run. Change <strong>$recordsPerRun</strong> if the server needs smaller batches.</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
<?php

if (PHP_SAPI !== "cli") {
    http_response_code(403);
    exit("This worker must be run from the command line.");
}

require_once __DIR__ . "/include/dbcommon.php";

set_time_limit(300);

$recordsPerRun = 100000;
$progressUpdateInterval = 1000;

$exportTypes = array(
    "orders" => array(
        "table" => "orders",
        "dateField" => "order_date",
        "headers" => array(
            "Order ID",
            "Order Date",
            "Customer",
            "Amount"
        ),
        "fields" => array(
            "id",
            "order_date",
            "customer",
            "amount"
        )
    ),
    "transactions" => array(
        "table" => "transactions",
        "dateField" => "transaction_date",
        "headers" => array(
            "Transaction ID",
            "Transaction Date",
            "Customer Name",
            "Customer Email",
            "Transaction Type",
            "Amount",
            "Status",
            "Reference"
        ),
        "fields" => array(
            "id",
            "transaction_date",
            "customer_name",
            "customer_email",
            "transaction_type",
            "amount",
            "status",
            "reference"
        )
    )
);

$rs = DB::Query(
    "SELECT * FROM exports " .
    "WHERE status IN ('Pending', 'Processing') " .
    "ORDER BY id LIMIT 1"
);
$export = $rs->fetchAssoc();

if (!$export) {
    echo "No exports to process.\n";
    exit;
}

$exportId = (int)$export["id"];
$taskType = trim((string)$export["task_type"]);

if (!isset($exportTypes[$taskType])) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Invalid task type.\n");
}

$definition = $exportTypes[$taskType];
$sourceTable = $definition["table"];
$dateField = $definition["dateField"];
$alreadyProcessed = (int)$export["processed_records"];

DB::Update(
    "exports",
    array("status" => "Processing"),
    array("id" => $exportId)
);

$where = array();

if (!empty($export["date_from"])) {
    $where[] = DB::PrepareSQL(
        $dateField . " >= ':1'",
        $export["date_from"] . " 00:00:00"
    );
}

if (!empty($export["date_to"])) {
    $where[] = DB::PrepareSQL(
        $dateField . " <= ':1'",
        $export["date_to"] . " 23:59:59"
    );
}

$whereSql = count($where)
    ? " WHERE " . implode(" AND ", $where)
    : "";

if ($alreadyProcessed === 0) {
    $countRs = DB::Query(
        "SELECT COUNT(*) AS cnt FROM " . $sourceTable . $whereSql
    );
    $countData = $countRs->fetchAssoc();
    $totalRecords = (int)$countData["cnt"];

    DB::Update(
        "exports",
        array("total_records" => $totalRecords),
        array("id" => $exportId)
    );
} else {
    $totalRecords = (int)$export["total_records"];
}

$exportFolder = getabspath("exports");

if (!is_dir($exportFolder) && !mkdir($exportFolder, 0755, true)) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Cannot create the exports folder.\n");
}

$csvFilename = "export_" . $exportId . ".csv";
$csvPath = $exportFolder . DIRECTORY_SEPARATOR . $csvFilename;
$file = fopen($csvPath, $alreadyProcessed === 0 ? "w" : "a");

if (!$file) {
    DB::Update(
        "exports",
        array("status" => "Failed"),
        array("id" => $exportId)
    );
    exit("Cannot open the export file.\n");
}

if ($alreadyProcessed === 0) {
    fputcsv($file, $definition["headers"]);
}

$sql = "SELECT * FROM " . $sourceTable . $whereSql;
$sql .= " ORDER BY id";
$sql .= " LIMIT " . (int)$recordsPerRun;
$sql .= " OFFSET " . (int)$alreadyProcessed;

$rs = DB::Query($sql);
$processedThisRun = 0;

while ($row = $rs->fetchAssoc()) {
    $csvRow = array();

    foreach ($definition["fields"] as $field) {
        $csvRow[] = $row[$field];
    }

    fputcsv($file, $csvRow);
    $processedThisRun++;

    if ($processedThisRun % $progressUpdateInterval === 0) {
        DB::Update(
            "exports",
            array(
                "processed_records" =>
                    $alreadyProcessed + $processedThisRun
            ),
            array("id" => $exportId)
        );
    }
}

fclose($file);

$totalProcessed = $alreadyProcessed + $processedThisRun;
$update = array("processed_records" => $totalProcessed);

if ($totalProcessed >= $totalRecords) {
    $zipFilename = "export_" . $exportId . "_"
        . bin2hex(random_bytes(12)) . ".zip";
    $zipPath = $exportFolder . DIRECTORY_SEPARATOR . $zipFilename;
    $zip = new ZipArchive();

    if (
        $zip->open(
            $zipPath,
            ZipArchive::CREATE | ZipArchive::OVERWRITE
        ) !== true
    ) {
        $update["status"] = "Failed";
        DB::Update("exports", $update, array("id" => $exportId));
        exit("Cannot create the ZIP file.\n");
    }

    $zip->addFile($csvPath, $csvFilename);
    $zip->close();

    if (!file_exists($zipPath)) {
        $update["status"] = "Failed";
        DB::Update("exports", $update, array("id" => $exportId));
        exit("The ZIP file was not created.\n");
    }

    unlink($csvPath);
    $update["status"] = "Completed";
    $update["filename"] = $zipFilename;
    $update["completed_at"] = date("Y-m-d H:i:s");
} else {
    $update["status"] = "Processing";
}

DB::Update("exports", $update, array("id" => $exportId));

echo "Processed " . $processedThisRun . " records.\n";</textarea></pre>
</div>
<p>The worker uses an allowlist in <strong>$exportTypes</strong>, so a value stored in <strong>task_type</strong> cannot become an arbitrary table name. Each run resumes at <strong>processed_records</strong>. The status changes to &#8216;Completed&#8217; only after the ZIP file has been created.</p>
<p>The PHP Zip extension must be enabled. The web-server account also needs write permission to the generated application&#8217;s <strong>exports</strong> folder.</p>
<h3>4. Scheduling the worker</h3>
<p>Schedule the generated application&#8217;s <strong>cron.php</strong> with the PHP command-line executable. For example, a Linux cron job that runs every minute can use:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
* * * * * /usr/bin/php /path/to/application/cron.php</textarea></pre>
</div>
<p>Use the equivalent scheduled-task command on Windows. Do not configure overlapping runs. The script rejects web requests, so it cannot be started by visiting its URL.</p>
<h3>5. Displaying progress on the List page</h3>
<p>Add <strong>status</strong>, <strong>total_records</strong>, <strong>processed_records</strong>, <strong>filename</strong>, and <strong>completed_at</strong> to the Exports List page.</p>
<p>Open <strong>Exports List page &#8211; JavaScript OnLoad</strong> and add:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
setInterval(function() {
    if ($("div.modal-dialog").length) {
        return;
    }

    location.reload();
}, 10000);</textarea></pre>
</div>
<p>The page refreshes every ten seconds, but it does not interrupt a user who has an Add, Edit, or View popup open.</p>
<h3>6. Adding the download link</h3>
<p>Open the <strong>filename</strong> field&#8217;s View as settings, select <strong>Custom</strong>, and add:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
if ($data["status"] === "Completed" && $value) {
    $filename = basename($value);
    $url = "exports/" . rawurlencode($filename);
    $caption = htmlspecialchars(
        "Download",
        ENT_QUOTES,
        "UTF-8"
    );

    $value = '<a href="' . $url . '" download>'
        . $caption
        . '</a>';
} else {
    $value = "";
}</textarea></pre>
</div>
<p>Rebuild the project, submit an export request, and run the scheduled worker. The record should move from &#8216;Pending&#8217; to &#8216;Processing&#8217;, the processed count should increase after each batch, and the download link should appear after the ZIP file is complete.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/10/02/process-large-csv-exports-in-background-batches/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Experimenting with MCP Support in PHPRunner</title>
		<link>https://xlinesoft.com/blog/2026/09/10/experimenting-with-mcp-support-in-phprunner/</link>
					<comments>https://xlinesoft.com/blog/2026/09/10/experimenting-with-mcp-support-in-phprunner/#comments</comments>
		
		<dc:creator><![CDATA[admin]]></dc:creator>
		<pubDate>Thu, 10 Sep 2026 20:53:17 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3498</guid>

					<description><![CDATA[The Model Context Protocol, or MCP, provides a standard way for AI assistants to connect to applications, discover available tools, and work with live data. We created an early MCP prototype to explore how this technology could work inside a generated PHPRunner application. Watch the PHPRunner MCP demonstration on YouTube Download the PHPRunner MCP sample project MCP tools in PHPRunner The prototype can expose standard operations for enabled application tables, including: &#8211; Listing and searching records &#8211; Retrieving individual records &#8211; Adding and updating records...<span class="clearfix clearfix-post"></span><a href="https://xlinesoft.com/blog/2026/09/10/experimenting-with-mcp-support-in-phprunner/" class="more-link">Continue Reading <span class="screen-reader-text">"Experimenting with MCP Support in PHPRunner"</span> <span class="meta-nav">&#8594;</span></a>]]></description>
										<content:encoded><![CDATA[<p>The Model Context Protocol, or MCP, provides a standard way for AI assistants to connect to applications, discover available tools, and work with live data.</p>
<p>We created an early MCP prototype to explore how this technology could work inside a generated PHPRunner application.</p>
<p><a href="https://youtu.be/EJun7UB42PM"><i class="fas fa-link" style="solid"></i> Watch the PHPRunner MCP demonstration on YouTube</a></p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/09/MCP_Server_20260910173848622.zip"><i class="fas fa-link" style="solid"></i> Download the PHPRunner MCP sample project</a></p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28-1024x576.png" alt="" width="810" height="456" class="alignnone size-large wp-image-3500" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28-1024x576.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28-600x338.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28-768x432.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28-1536x864.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/exec-7b54001f-8aa2-45f5-9883-81b9f5a19c28.png 1672w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a><br />
<span id="more-3498"></span></p>
<p><strong>MCP tools in PHPRunner</strong></p>
<p>The prototype can expose standard operations for enabled application tables, including:<br />
&#8211; Listing and searching records<br />
&#8211; Retrieving individual records<br />
&#8211; Adding and updating records<br />
&#8211; Deleting records</p>
<p>The available tools depend on the application configuration and permissions associated with the supplied API key.</p>
<p>An AI assistant can process requests such as “Show me the last five orders” or “Find all products in the Pastry category,” select the appropriate tool, and return the results in a readable format.</p>
<p><strong>Authentication and permissions</strong></p>
<p>This initial prototype uses API-key authentication. OAuth and a separate authorization server are not required.</p>
<p>The MCP layer works with the PHPRunner application layer responsible for permissions, validation, events, and data access. This is especially important for operations that change data. An API key can allow an assistant to view records without granting permission to add, update, or delete them.</p>
<p><strong>Custom MCP actions</strong></p>
<p>Applications often include workflows that require more than a single database operation. Custom MCP actions can represent these higher-level business processes.<br />
Multiple actions can be defined in an mcp_actions.php file. Each action describes its parameters, required permissions, behavior, and PHP handler.</p>
<p>The sample application includes actions for:<br />
&#8211; Producing an inventory summary<br />
&#8211; Receiving inventory<br />
&#8211; Discontinuing or reactivating products<br />
&#8211; Marking orders as shipped<br />
&#8211; Creating a customer and complete order<br />
&#8211; Increasing prices within a category</p>
<p>For example, creating a customer and order requires coordinated changes to several tables. A custom action can complete the entire workflow from one request.</p>
<p>MCP opens some interesting possibilities for conversational queries, application automation, reporting, and multi-step business workflows. Let us know which PHPRunner workflows you would like to access through an AI assistant.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/09/10/experimenting-with-mcp-support-in-phprunner/feed/</wfw:commentRss>
			<slash:comments>2</slash:comments>
		
		
			</item>
		<item>
		<title>Add a Custom Row Actions Menu to a Data Grid</title>
		<link>https://xlinesoft.com/blog/2026/09/05/add-a-custom-row-actions-menu-to-a-data-grid/</link>
					<comments>https://xlinesoft.com/blog/2026/09/05/add-a-custom-row-actions-menu-to-a-data-grid/#comments</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Sat, 05 Sep 2026 16:42:34 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3484</guid>

					<description><![CDATA[Create a compact per-record actions menu in PHPRunner and ASPRunner.NET with View, Edit, Copy, related-record navigation, permission-checked Delete, and room for custom actions.]]></description>
										<content:encoded><![CDATA[<p>List pages often contain several actions for every record. Displaying each action as a separate button can make the grid unnecessarily wide, especially on smaller screens.</p>
<p>In this tutorial, we will add one menu button to each grid row. The menu will open View, Edit, Copy, and related-record pages, process Delete with confirmation, and include an Archive placeholder for project-specific code. The same approach works in PHPRunner and ASPRunner.NET.</p>
<p><img loading="lazy" decoding="async" class="alignnone size-full wp-image-3485" src="https://xlinesoft.com/blog/wp-content/uploads/2026/09/row-actions-menu-data-grid.png" alt="Custom row actions menu on a data grid" width="1160" height="625" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/09/row-actions-menu-data-grid.png 1160w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/row-actions-menu-data-grid-600x323.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/row-actions-menu-data-grid-1024x552.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/09/row-actions-menu-data-grid-768x414.png 768w" sizes="auto, (max-width: 1160px) 100vw, 1160px" /></p>
<p><span id="more-3484"></span></p>
<h3>1. Adding the row actions button</h3>
<p>Open the required List page in <strong>Page Designer</strong> and switch the grid to Advanced Grid mode. Select <strong>Insert &#8211; Custom button &#8211; New button</strong>, then place the button inside the grid so every record receives its own copy.</p>
<p>Clear the button label, select the bars icon, and set its Item ID to <strong>row_actions</strong>. The Item ID is important because the JavaScript uses it to find the button belonging to the current record.</p>
<p>This example uses the <strong>customers</strong> table, <strong>id</strong> as its key field, and <strong>CustomerID</strong> to open related orders. Replace those names where necessary. The table must have a key field selected on the Choose pages screen.</p>
<p>Keep the standard Edit button in the grid with the Item ID <strong>grid_edit</strong>. The menu will trigger that button, preserving the Edit behavior configured in Page Designer, including popup mode.</p>
<h3>2. Building the menu in Client Before</h3>
<p>Open the custom button code and add the following JavaScript to <strong>Client Before</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
$(".phprunner-row-actions-menu").remove();

var btn = pageObj.getItemButton("row_actions", rowData.id);

var menu = $(
    '<div class="phprunner-row-actions-menu">' +
        '<div class="row-action" data-action="view">View</div>' +
        '<div class="row-action" data-action="edit">Edit</div>' +
        '<div class="row-action" data-action="orders">View orders</div>' +
        '<div class="row-action" data-action="delete">Delete</div>' +
        '<div class="row-action" data-action="copy">Create a copy</div>' +
        '<div class="row-action" data-action="archive">Archive</div>' +
    '</div>'
);

$("body").append(menu);

var pos = btn.offset();
var top = pos.top;
var left = pos.left + btn.outerWidth() + 4;

if (
    pos.top - $(window).scrollTop() + menu.outerHeight()
        > $(window).height()
) {
    top = pos.top - menu.outerHeight() + btn.outerHeight();
}

if (
    left + menu.outerWidth()
        > $(window).scrollLeft() + $(window).width()
) {
    left = pos.left - menu.outerWidth() - 4;
}

menu.css({
    top: top,
    left: left
});

menu.on("click", ".row-action", function(event) {
    event.stopPropagation();

    var action = $(this).data("action");
    menu.remove();

    switch (action) {
        case "view":
            Runner.displayPopup({
                url: Runner.pages.getUrl("customers", "view")
                    + "?editid1="
                    + encodeURIComponent(rowData.keys[0]),
                width: 800,
                height: 600,
                header: "View customer"
            });
            break;

        case "edit":
            pageObj.getItemButton(
                "grid_edit",
                rowData.id
            ).click();
            break;

        case "orders":
            var url = Runner.pages.getUrl("orders", "list");
            url += "?masterkey1="
                + encodeURIComponent(
                    row.getFieldValue("CustomerID")
                )
                + "&mastertable=customers";
            location.href = url;
            break;

        case "delete":
            swal("Would you like to delete this record?", {
                buttons: {
                    cancel: "No",
                    proceed: {
                        text: "Yes, delete it",
                        value: "proceed"
                    }
                }
            }).then(function(value) {
                if (value === "proceed") {
                    params["action"] = "delete";
                    submit();
                }
            });
            break;

        case "copy":
            location.href = Runner.pages.getUrl(
                "customers",
                "add"
            ) + "?copyid1="
                + encodeURIComponent(rowData.keys[0]);
            break;

        case "archive":
            // Add the code that archives the current record here.
            break;
    }
});

var closeTimer;

menu.on("mouseleave", function() {
    closeTimer = setTimeout(function() {
        menu.remove();
    }, 300);
});

menu.on("mouseenter", function() {
    clearTimeout(closeTimer);
});

return false;</textarea></pre>
</div>
<p>The button and the current record are identified through <strong>rowData.id</strong> and <strong>rowData.keys</strong>. If the menu would extend beyond the bottom or right side of the browser window, the positioning code moves it to the opposite side of the button.</p>
<p>The Copy action opens the Add page in Copy mode. This example has one key field, so it uses <strong>copyid1</strong>. Tables with composite keys also require <strong>copyid2</strong>, <strong>copyid3</strong>, and so on.</p>
<h3>3. Processing Delete on the server</h3>
<p>The Delete action is the only menu command that changes the database. Add the appropriate code to the custom button&#8217;s <strong>Server</strong> section.</p>
<p>The code verifies the current user&#8217;s Delete permission and obtains the record from the server-side button context. It does not trust a record ID supplied by the browser.</p>
<p><strong>PHP</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="php" name="mshighlighter" >
$result["action"] = "";

if ($params["action"] === "delete") {
    $rights = Security::getPermissions("customers");

    if (empty($rights["D"])) {
        $result["message"] =
            "You do not have permission to delete this record.";
    } else {
        $record = $button->getCurrentRecord();

        if (!$record) {
            $result["message"] = "The record could not be found.";
        } else {
            $sql = DB::PrepareSQL(
                "DELETE FROM customers WHERE id=:1",
                $record["id"]
            );

            if (DB::Exec($sql)) {
                $result["action"] = "delete";
            } else {
                $result["message"] =
                    "The record could not be deleted.";
            }
        }
    }
}</textarea></pre>
</div>
<p><strong>C#</strong></p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
result["action"] = "";

if (Convert.ToString(parameters["action"]) == "delete")
{
    dynamic rights = Security.getPermissions("customers");

    if (!XVar.Pack(rights["D"]))
    {
        result["message"] =
            "You do not have permission to delete this record.";
    }
    else
    {
        dynamic record = button.getCurrentRecord();

        if (!XVar.Pack(record))
        {
            result["message"] = "The record could not be found.";
        }
        else
        {
            dynamic sql = DB.PrepareSQL(
                "DELETE FROM customers WHERE id=:1",
                record["id"]
            );

            if (XVar.Pack(DB.Exec(sql)))
            {
                result["action"] = "delete";
            }
            else
            {
                result["message"] =
                    "The record could not be deleted.";
            }
        }
    }
}</textarea></pre>
</div>
<p>Replace <strong>customers</strong> and <strong>id</strong> with the actual table and key field. If the database prevents deleting records that have related rows, either configure the required cascade behavior or replace Delete with the business rule appropriate for your application.</p>
<h3>4. Refreshing the List page in Client After</h3>
<p>Add the following JavaScript to <strong>Client After</strong>:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
if (result["message"]) {
    swal(result["message"]);
}

if (result["action"] === "delete") {
    pageObj.reload({
        a: "reload"
    });
}</textarea></pre>
</div>
<p>The grid reloads only after the server confirms that the record was deleted.</p>
<h3>5. Styling the actions menu</h3>
<p>Open <strong>Style Editor</strong>, click &#8216;Modify CSS&#8217;, and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
.phprunner-row-actions-menu {
    position: absolute;
    z-index: 10000;
    min-width: 180px;
    padding: 6px 0;
    background: #fff;
    border: 1px solid #ddd;
    border-radius: 6px;
    box-shadow: 0 4px 14px rgba(0, 0, 0, 0.15);
}

.phprunner-row-actions-menu .row-action {
    padding: 8px 14px;
    cursor: pointer;
    white-space: nowrap;
}

.phprunner-row-actions-menu .row-action:hover {
    background: #f5f5f5;
}</textarea></pre>
</div>
<p>After rebuilding the project, open the Customers List page and test the menu on several rows. Confirm that View opens in a popup, Edit follows the configured Edit-button behavior, Copy opens a prefilled Add page, related orders open for the correct customer, and Delete refreshes the grid only after confirmation.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/09/05/add-a-custom-row-actions-menu-to-a-data-grid/feed/</wfw:commentRss>
			<slash:comments>1</slash:comments>
		
		
			</item>
		<item>
		<title>Add Search Boxes to Individual Dashboard Grids</title>
		<link>https://xlinesoft.com/blog/2026/09/01/add-search-boxes-to-individual-dashboard-grids/</link>
					<comments>https://xlinesoft.com/blog/2026/09/01/add-search-boxes-to-individual-dashboard-grids/#comments</comments>
		
		<dc:creator><![CDATA[Sergey Kornilov]]></dc:creator>
		<pubDate>Tue, 01 Sep 2026 16:21:43 +0000</pubDate>
				<category><![CDATA[ASP.NET]]></category>
		<category><![CDATA[PHP]]></category>
		<category><![CDATA[Tutorials]]></category>
		<guid isPermaLink="false">https://xlinesoft.com/blog/?p=3481</guid>

					<description><![CDATA[Add independent search boxes to dashboard grid elements in PHPRunner and ASPRunner.NET using JavaScript, AJAX grid reloads, and responsive CSS.]]></description>
										<content:encoded><![CDATA[<p>PHPRunner and ASPRunner.NET dashboards can display several grids on one page. In this tutorial, we will add a separate search box to each grid so users can filter Customers, Products, or Employees without affecting the other dashboard elements.</p>
<p>The solution uses one reusable JavaScript function and a small amount of Custom CSS. The same code works in PHPRunner and ASPRunner.NET.</p>
<p><a href="https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search.png"><img loading="lazy" decoding="async" src="https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search-1024x453.png" alt="" width="810" height="358" class="alignnone size-large wp-image-3482" srcset="https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search-1024x453.png 1024w, https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search-600x266.png 600w, https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search-768x340.png 768w, https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search-1536x680.png 1536w, https://xlinesoft.com/blog/wp-content/uploads/2026/08/scr_dashboard_search.png 1843w" sizes="auto, (max-width: 810px) 100vw, 810px" /></a></p>
<p><span id="more-3481"></span></p>
<h3>1. Preparing the dashboard</h3>
<p>Create a dashboard and add the grid elements that need their own search boxes. This example uses the <strong>customers</strong>, <strong>products</strong>, and <strong>employees</strong> data sources.</p>
<p>You will pass these data source names to the search function in section 3. Use the names as they appear in your project, not the visible dashboard panel captions.</p>
<h3>2. Adding the dashboard grid search function</h3>
<p>Open <strong>Events → custom_function.js</strong> and add the following JavaScript:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
function addDashboardGridSearch(dashboard, tableName) {
    var dashboardElement = dashboard.elements.find(function(element) {
        return element.table === tableName;
    });

    if (!dashboardElement) {
        console.warn("Dashboard table not found: " + tableName);
        return;
    }

    var containerId =
        "dashelement_" + dashboardElement.elementName + dashboard.id;
    var container = document.getElementById(containerId);

    if (!container) {
        console.warn("Dashboard container not found: " + containerId);
        return;
    }

    if (container.getAttribute("data-grid-search-initialized")) {
        return;
    }

    container.setAttribute("data-grid-search-initialized", "1");

    var searchValue = "";

    function search(input) {
        searchValue = input.value.trim();

        var parameters = {
            goto: 1
        };

        if (searchValue) {
            parameters.qs = searchValue;
        } else {
            parameters.a = "return";
            parameters.qs = "";
        }

        dashboard.loadPage(
            dashboardElement,
            {
                baseParams: parameters
            }
        );
    }

    function insertSearchBox() {
        var heading = container.querySelector(".panel-heading");
        var counter = container.querySelector(
            "[data-itemtype='details_found']"
        );

        if (!heading || !counter) {
            return;
        }

        if (heading.querySelector(".dashboard-grid-search")) {
            return;
        }

        var searchBox = document.createElement("div");
        var input = document.createElement("input");
        var clearButton = document.createElement("button");

        searchBox.className = "dashboard-grid-search";

        input.type = "search";
        input.className = "form-control input-sm";
        input.placeholder = "Search";
        input.value = searchValue;
        input.autocomplete = "off";
        input.setAttribute("aria-label", "Search " + tableName);

        clearButton.type = "button";
        clearButton.className = "btn btn-default btn-sm";
        clearButton.innerHTML = "&times;";
        clearButton.title = "Clear search";
        clearButton.setAttribute(
            "aria-label",
            "Clear " + tableName + " search"
        );

        searchBox.appendChild(input);
        searchBox.appendChild(clearButton);

        heading.classList.add("dashboard-grid-search-heading");
        counter.parentNode.classList.add("dashboard-grid-search-count");

        heading.insertBefore(searchBox, counter.parentNode);

        [
            "click",
            "mousedown",
            "mouseup",
            "touchstart"
        ].forEach(function(eventName) {
            searchBox.addEventListener(eventName, function(event) {
                event.stopPropagation();
            });
        });

        input.addEventListener("keydown", function(event) {
            if (event.key !== "Enter" && event.keyCode !== 13) {
                return;
            }

            event.preventDefault();
            event.stopPropagation();
            search(input);
        });

        clearButton.addEventListener("click", function(event) {
            event.preventDefault();
            event.stopPropagation();

            input.value = "";
            search(input);
        });
    }

    insertSearchBox();

    new MutationObserver(insertSearchBox).observe(
        container,
        {
            childList: true,
            subtree: true
        }
    );
}</textarea></pre>
</div>
<p>The function finds one dashboard element by its data source name and adds a search control to that element&#8217;s panel heading. Pressing Enter passes the search phrase in <strong>qs</strong> and reloads only that grid. The clear button removes the stored search.</p>
<p>Dashboard grids replace their markup after an AJAX refresh. The <strong>MutationObserver</strong> restores the search box after that replacement while preserving the current search phrase.</p>
<h3>3. Enabling search for the required grids</h3>
<p>Open <strong>Events → Dashboard → Dashboard page → JavaScript OnLoad</strong> and call the function once for each grid:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="javascript" name="mshighlighter" >
addDashboardGridSearch(pageObj, "customers");
addDashboardGridSearch(pageObj, "products");
addDashboardGridSearch(pageObj, "employees");</textarea></pre>
</div>
<p>Replace these values with the data source names used by your dashboard. Omit any dashboard element that should not have an individual search box.</p>
<h3>4. Styling the search boxes</h3>
<p>Open <strong>Style Editor</strong>, click &#8216;Modify CSS&#8217;, and add the following code:</p>
<div class="my-syntax-highlighter">
<pre><textarea id="mshighlighter" class="mshighlighter" language="" name="mshighlighter" >
.function-dashboard .panel-heading.dashboard-grid-search-heading {
    display: flex;
    align-items: center;
    gap: 8px;
    flex-wrap: nowrap;
}

.function-dashboard
.panel-heading.dashboard-grid-search-heading
> .rnr-dbebrick {
    float: none !important;
    white-space: nowrap;
}

.function-dashboard .dashboard-grid-search {
    display: flex;
    flex: 0 1 180px;
    min-width: 120px;
    margin-left: auto;
}

.function-dashboard .dashboard-grid-search .form-control {
    height: 30px;
    min-width: 0;
    padding: 4px 8px;
    font-size: 12px;
    border-radius: 4px 0 0 4px;
}

.function-dashboard .dashboard-grid-search .btn {
    height: 30px;
    padding: 4px 9px;
    font-size: 16px;
    line-height: 16px;
    border-radius: 0 4px 4px 0;
}

.function-dashboard .dashboard-grid-search-count {
    flex: 0 0 auto;
    margin-left: 0;
    white-space: nowrap;
    font-size: 12px;
}

@media (max-width: 900px) {
    .function-dashboard
    .panel-heading.dashboard-grid-search-heading {
        flex-wrap: wrap;
    }

    .function-dashboard .dashboard-grid-search {
        order: 3;
        flex-basis: 100%;
        margin-left: 28px;
    }

    .function-dashboard .dashboard-grid-search-count {
        margin-left: auto;
    }
}</textarea></pre>
</div>
<p>The desktop layout places the search box beside the record counter. Below 900 pixels, it moves to a separate line so the panel heading remains usable on smaller screens.</p>
<p>After rebuilding the project, open the Dashboard page and enter a phrase in one of the search boxes. Press Enter and confirm that only the corresponding grid is filtered. Click the clear button to restore the full grid, then repeat the test after collapsing and expanding the dashboard panel.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://xlinesoft.com/blog/2026/09/01/add-search-boxes-to-individual-dashboard-grids/feed/</wfw:commentRss>
			<slash:comments>1</slash:comments>
		
		
			</item>
	</channel>
</rss>
