October 18, 2026

Add Semantic Search to a PHPRunner Application

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 ‘customers having access or login problems’ can find tickets about rejected credentials, unauthorized sessions, or account access even when they do not contain the exact search phrase.

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.

Semantic Search in Action

Let’s try searching for “Employees are being denied entry to the company portal.”

Notice that the results include tickets about failed logins, incorrect credentials, authentication errors, and users being redirected back to the sign-in page.

These tickets describe similar problems using different terminology. A traditional keyword search might miss some of them because the search phrase doesn’t contain words such as “password,” “login,” or “authentication.”

Semantic search recognizes the relationship between these descriptions and returns relevant tickets based on their meaning, not just matching words.

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.

How the search works:

Each ticket’s subject and description are converted into an embedding vector.
The vector is stored in the database with the ticket.
The user’s search phrase is converted into a vector with the same embedding model.
The generated PHP application calculates cosine similarity between the query vector and the stored ticket vectors.
The most relevant tickets are returned in similarity order.

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’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.

1. Creating the support tickets table

Create the following MySQL table and add it to the PHPRunner project.

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`)
);

The optional sample SQL download creates this table and adds 500 support tickets with their precomputed embeddings.

support_tickets_500_with_embeddings

Open the table’s SQL Query screen and add a calculated field named semantic_query:

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

The calculated field gives PHPRunner a search condition that we can replace with the IDs returned by semantic search. Do not display semantic_query or the four embedding fields on the generated pages. On the List page, keep fields such as subject and description visible so readers can evaluate the results.

Turn off ‘Highlight search results’. Semantic matches often use different words, so ordinary keyword highlighting is not useful here.

2. Adding the embedding functions

Create an API key at platform.openai.com/api-keys. In the code below, replace YOUR_OPENAI_API_KEY with that key. The key is stored in a server-side session variable and is never sent to browser-side JavaScript.

Open Events – Global events – After App Init and add the following code:

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")
        );
    }
}

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.

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 After App Init.

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.

3. Generating an embedding when a ticket is added

Open the support_tickets Add page – Before record added event and add this code:

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;

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 false, so the ticket is not added with a missing or partial embedding.

4. Refreshing the embedding when a ticket is edited

Open the support_tickets Edit page – Before record updated event and add this code:

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;

The API is called only when subject or description changes. Editing another field keeps the current embedding. If the API request fails, returning false prevents the update, so the previous ticket text and embedding remain unchanged.

5. Populating embeddings for existing records

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.

Copy the supplied populate_embeddings.php file to the root of the generated PHPRunner application, next to support_tickets_list.php. Open the file in a browser and click ‘Start / Resume’. It processes 20 tickets per request, skips current embeddings, and continues until all records are ready.

The utility allows access only from localhost or by a PHPRunner administrator. Delete it from the generated application after the initial population is complete.

6. Replacing the basic search with semantic search

Open the support_tickets List page – Before process event and add the following code:

$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);
}

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.

7. Sorting tickets by similarity

The previous event filters the grid to the 30 matching IDs. MySQL still needs an explicit sort expression to display them in similarity order.

Open the support_tickets List page – Before SQL query event and add this code:

$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) . ")";
}

The MySQL FIELD() 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.

8. Displaying semantic-search errors

Open the support_tickets List page – Before display event and add this code:

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"
        )
    );
}

Rebuild the application, populate the existing embeddings, and enter a natural-language phrase in the List page’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.

Add Semantic Search to a PHPRunner Application

1. Creating the support tickets table

2. Adding the embedding functions

3. Generating an embedding when a ticket is added

4. Refreshing the embedding when a ticket is edited

5. Populating embeddings for existing records

6. Replacing the basic search with semantic search

7. Sorting tickets by similarity

8. Displaying semantic-search errors

Leave a Reply

Sign up for the free email newsletter for news about our
database publishing software, tips, and more.

Resources

Company

1. Creating the support tickets table

2. Adding the embedding functions

3. Generating an embedding when a ticket is added

4. Refreshing the embedding when a ticket is edited

5. Populating embeddings for existing records

6. Replacing the basic search with semantic search

7. Sorting tickets by similarity

8. Displaying semantic-search errors

Related posts:

Leave a Reply