{"id":3537,"date":"2026-10-29T16:32:16","date_gmt":"2026-10-29T21:32:16","guid":{"rendered":"https:\/\/xlinesoft.com\/blog\/?p=3537"},"modified":"2026-10-30T16:49:38","modified_gmt":"2026-10-30T21:49:38","slug":"build-local-semantic-search-with-ollama-and-mariadb","status":"publish","type":"post","link":"https:\/\/xlinesoft.com\/blog\/2026\/10\/29\/build-local-semantic-search-with-ollama-and-mariadb\/","title":{"rendered":"Build Semantic Search with Ollama and MariaDB"},"content":{"rendered":"<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>\n<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>\n<p><a href=\"https:\/\/xlinesoft.com\/blog\/wp-content\/uploads\/2026\/10\/Semantic-Search-Workflow-with-PHPRunner-and-MariaDB.png\"><img loading=\"lazy\" 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=\"auto, (max-width: 810px) 100vw, 810px\" \/><\/a><br \/>\n<!--more--><\/p>\n<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>\n<h3>1. Installing Ollama and the embedding model<\/h3>\n<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>\n<p>Open a command prompt and download the model:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"\" name=\"mshighlighter\" >\r\nollama pull embeddinggemma<\/textarea><\/pre>\n<\/div>\n<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>\n<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>\n<h3>2. Adding local vector fields<\/h3>\n<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>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"sql\" name=\"mshighlighter\" >\r\nCREATE TABLE support_tickets_ollama LIKE support_tickets;\r\n\r\nINSERT INTO support_tickets_ollama\r\nSELECT * FROM support_tickets;\r\n\r\nALTER TABLE support_tickets_ollama\r\n    ADD COLUMN embedding_local VECTOR(768) NULL,\r\n    ADD COLUMN embedding_hash_local CHAR(64) NULL;<\/textarea><\/pre>\n<\/div>\n<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>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"sql\" name=\"mshighlighter\" >\r\nSELECT\r\n    support_tickets_ollama.*,\r\n    '' AS semantic_query\r\nFROM support_tickets_ollama<\/textarea><\/pre>\n<\/div>\n<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>\n<p>Turn off &#8216;Highlight search results&#8217;. Semantic matches often use different words, so ordinary keyword highlighting is not useful here.<\/p>\n<h3>3. Adding the Ollama embedding function<\/h3>\n<p>Open <strong>Events &#8211; Global events &#8211; After App Init<\/strong> and add this code:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\nif (!function_exists(\"devclubOllamaEmbedding\")) {\r\n    function devclubOllamaEmbedding($text)\r\n    {\r\n        if (!function_exists(\"curl_init\")) {\r\n            throw new RuntimeException(\r\n                \"PHP cURL extension is required.\"\r\n            );\r\n        }\r\n\r\n        $payload = json_encode(array(\r\n            \"model\" => \"embeddinggemma:latest\",\r\n            \"input\" => $text\r\n        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);\r\n\r\n        if ($payload === false) {\r\n            throw new RuntimeException(\"Cannot encode Ollama request.\");\r\n        }\r\n\r\n        $ch = curl_init(\"http:\/\/127.0.0.1:11434\/api\/embed\");\r\n\r\n        if ($ch === false) {\r\n            throw new RuntimeException(\"Cannot initialize cURL.\");\r\n        }\r\n\r\n        curl_setopt_array($ch, array(\r\n            CURLOPT_POST => true,\r\n            CURLOPT_POSTFIELDS => $payload,\r\n            CURLOPT_HTTPHEADER => array(\r\n                \"Content-Type: application\/json\"\r\n            ),\r\n            CURLOPT_RETURNTRANSFER => true,\r\n            CURLOPT_CONNECTTIMEOUT => 5,\r\n            CURLOPT_TIMEOUT => 90\r\n        ));\r\n\r\n        $response = curl_exec($ch);\r\n        $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);\r\n        $error = curl_error($ch);\r\n        curl_close($ch);\r\n\r\n        if ($response === false) {\r\n            throw new RuntimeException(\r\n                \"Ollama connection failed: \" . $error\r\n            );\r\n        }\r\n\r\n        $data = json_decode($response, true);\r\n\r\n        if ($httpCode !== 200) {\r\n            $detail = is_array($data)\r\n                ? ($data[\"error\"] ?? \"Unknown error\")\r\n                : \"Unknown error\";\r\n\r\n            throw new RuntimeException(\r\n                \"Ollama HTTP \" . $httpCode . \": \" . $detail\r\n            );\r\n        }\r\n\r\n        $vector = $data[\"embeddings\"][0] ?? null;\r\n\r\n        if (!is_array($vector) || count($vector) !== 768) {\r\n            throw new RuntimeException(\r\n                \"Expected 768-dimensional embedding.\"\r\n            );\r\n        }\r\n\r\n        foreach ($vector as $number) {\r\n            if (!is_numeric($number) ||\r\n                !is_finite((float)$number)) {\r\n                throw new RuntimeException(\r\n                    \"Invalid number in embedding.\"\r\n                );\r\n            }\r\n        }\r\n\r\n        $json = json_encode(\r\n            $vector,\r\n            JSON_PRESERVE_ZERO_FRACTION\r\n        );\r\n\r\n        if ($json === false) {\r\n            throw new RuntimeException(\r\n                \"Cannot encode embedding.\"\r\n            );\r\n        }\r\n\r\n        return $json;\r\n    }\r\n}<\/textarea><\/pre>\n<\/div>\n<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>\n<h3>4. Generating embeddings for existing tickets<\/h3>\n<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>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ntry {\r\n    $batchSize = 100;\r\n    $tickets = array();\r\n    $inputs = array();\r\n\r\n    $rs = DB::Query(\r\n        \"SELECT ticket_id, subject, description \" .\r\n        \"FROM support_tickets_ollama \" .\r\n        \"WHERE embedding_local IS NULL \" .\r\n        \"ORDER BY ticket_id LIMIT \" . (int)$batchSize\r\n    );\r\n\r\n    if (!$rs) {\r\n        throw new RuntimeException(DB::LastError());\r\n    }\r\n\r\n    while ($row = $rs->fetchAssoc()) {\r\n        $text = \"title: \" . (string)$row[\"subject\"] .\r\n            \" | text: \" . (string)$row[\"description\"];\r\n\r\n        $tickets[] = array(\r\n            \"id\" => (int)$row[\"ticket_id\"],\r\n            \"text\" => $text\r\n        );\r\n\r\n        $inputs[] = $text;\r\n    }\r\n\r\n    if (!$tickets) {\r\n        $result[\"message\"] =\r\n            \"All tickets already have local embeddings.\";\r\n    } else {\r\n        if (!function_exists(\"curl_init\")) {\r\n            throw new RuntimeException(\r\n                \"PHP cURL extension is required.\"\r\n            );\r\n        }\r\n\r\n        $payload = json_encode(array(\r\n            \"model\" => \"embeddinggemma:latest\",\r\n            \"input\" => $inputs\r\n        ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);\r\n\r\n        if ($payload === false) {\r\n            throw new RuntimeException(\"Cannot encode request.\");\r\n        }\r\n\r\n        $ch = curl_init(\"http:\/\/127.0.0.1:11434\/api\/embed\");\r\n\r\n        curl_setopt_array($ch, array(\r\n            CURLOPT_POST => true,\r\n            CURLOPT_POSTFIELDS => $payload,\r\n            CURLOPT_HTTPHEADER => array(\r\n                \"Content-Type: application\/json\"\r\n            ),\r\n            CURLOPT_RETURNTRANSFER => true,\r\n            CURLOPT_CONNECTTIMEOUT => 5,\r\n            CURLOPT_TIMEOUT => 120\r\n        ));\r\n\r\n        $response = curl_exec($ch);\r\n        $httpCode = (int)curl_getinfo(\r\n            $ch, CURLINFO_HTTP_CODE\r\n        );\r\n        $curlError = curl_error($ch);\r\n        curl_close($ch);\r\n\r\n        if ($response === false) {\r\n            throw new RuntimeException(\r\n                \"Cannot connect to Ollama: \" . $curlError\r\n            );\r\n        }\r\n\r\n        $data = json_decode($response, true);\r\n\r\n        if ($httpCode !== 200) {\r\n            $detail = $data[\"error\"] ??\r\n                substr($response, 0, 200);\r\n\r\n            throw new RuntimeException(\r\n                \"Ollama HTTP \" . $httpCode . \": \" . $detail\r\n            );\r\n        }\r\n\r\n        $vectors = $data[\"embeddings\"] ?? null;\r\n\r\n        if (!is_array($vectors) ||\r\n            count($vectors) !== count($tickets)) {\r\n            throw new RuntimeException(\r\n                \"Unexpected number of embeddings returned.\"\r\n            );\r\n        }\r\n\r\n        $updated = 0;\r\n\r\n        foreach ($tickets as $i => $ticket) {\r\n            $vector = $vectors[$i];\r\n\r\n            if (!is_array($vector) || count($vector) !== 768) {\r\n                throw new RuntimeException(\r\n                    \"Invalid vector for ticket \" . $ticket[\"id\"]\r\n                );\r\n            }\r\n\r\n            foreach ($vector as $number) {\r\n                if (!is_numeric($number) ||\r\n                    !is_finite((float)$number)) {\r\n                    throw new RuntimeException(\r\n                        \"Invalid vector number for ticket \" .\r\n                        $ticket[\"id\"]\r\n                    );\r\n                }\r\n            }\r\n\r\n            $jsonVector = json_encode(\r\n                $vector,\r\n                JSON_PRESERVE_ZERO_FRACTION\r\n            );\r\n\r\n            if ($jsonVector === false) {\r\n                throw new RuntimeException(\r\n                    \"Cannot encode vector for ticket \" .\r\n                    $ticket[\"id\"]\r\n                );\r\n            }\r\n\r\n            $hash = hash(\"sha256\", $ticket[\"text\"]);\r\n\r\n            $sql = DB::PrepareSQL(\r\n                \"UPDATE support_tickets_ollama \" .\r\n                \"SET embedding_local = VEC_FromText(':1'), \" .\r\n                \"embedding_hash_local = ':2' \" .\r\n                \"WHERE ticket_id = :3\",\r\n                $jsonVector,\r\n                $hash,\r\n                $ticket[\"id\"]\r\n            );\r\n\r\n            if (!DB::Exec($sql)) {\r\n                throw new RuntimeException(\r\n                    \"Saving ticket \" . $ticket[\"id\"] .\r\n                    \" failed: \" . DB::LastError()\r\n                );\r\n            }\r\n\r\n            $updated++;\r\n        }\r\n\r\n        $countRs = DB::Query(\r\n            \"SELECT COUNT(*) AS remaining \" .\r\n            \"FROM support_tickets_ollama \" .\r\n            \"WHERE embedding_local IS NULL\"\r\n        );\r\n\r\n        if (!$countRs) {\r\n            throw new RuntimeException(DB::LastError());\r\n        }\r\n\r\n        $countRow = $countRs->fetchAssoc();\r\n        $remaining = (int)$countRow[\"remaining\"];\r\n\r\n        $result[\"message\"] =\r\n            \"Generated \" . $updated . \" embeddings. \" .\r\n            $remaining . \" tickets remaining.\";\r\n    }\r\n} catch (Throwable $e) {\r\n    $result[\"message\"] =\r\n        \"Embedding generation failed: \" . $e->getMessage();\r\n}<\/textarea><\/pre>\n<\/div>\n<p>Open the button&#8217;s <strong>Client After<\/strong> event and display the returned progress message:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"javascript\" name=\"mshighlighter\" >\r\nctrl.setMessage(result[\"message\"]);<\/textarea><\/pre>\n<\/div>\n<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 \/>\nMariaDB 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>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"sql\" name=\"mshighlighter\" >\r\nALTER TABLE support_tickets_ollama\r\n    MODIFY COLUMN embedding_local VECTOR(768) NOT NULL;\r\n\r\nCREATE VECTOR INDEX idx_support_tickets_embedding\r\n    ON support_tickets_ollama (embedding_local)\r\n    M=8 DISTANCE=cosine;<\/textarea><\/pre>\n<\/div>\n<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>\n<h3>5. Replacing basic search with local semantic search<\/h3>\n<p>Open the <strong>support_tickets_ollama List page &#8211; Before process<\/strong> event and add this code:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\n$srch = SearchClause::getSearchObject(\"support_tickets_ollama\");\r\n$phrase = trim((string)$srch->getAllFieldsSearchValue());\r\n\r\nif ($phrase !== \"\") {\r\n    $key = hash(\"sha256\", \"embeddinggemma:latest\\n\" . $phrase);\r\n    $cache = $_SESSION[\"DEVCLUB_LOCAL_SEARCH_CACHE\"] ?? null;\r\n    $ids = null;\r\n\r\n    unset($_SESSION[\"DEVCLUB_LOCAL_SEARCH_ERROR\"]);\r\n\r\n    if (\r\n        is_array($cache) &&\r\n        isset($cache[\"key\"], $cache[\"time\"], $cache[\"ids\"]) &&\r\n        $cache[\"key\"] === $key &&\r\n        time() - (int)$cache[\"time\"] < 300\r\n    ) {\r\n        $ids = $cache[\"ids\"];\r\n    }\r\n\r\n    if ($ids === null) {\r\n        try {\r\n            if (strlen($phrase) > 1000) {\r\n                throw new RuntimeException(\"Search text is too long.\");\r\n            }\r\n\r\n            $payload = json_encode(array(\r\n                \"model\" => \"embeddinggemma:latest\",\r\n                \"input\" => \"task: search result | query: \" . $phrase\r\n            ), JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);\r\n\r\n            if ($payload === false) {\r\n                throw new RuntimeException(\"Cannot encode Ollama request.\");\r\n            }\r\n\r\n            $ch = curl_init(\"http:\/\/127.0.0.1:11434\/api\/embed\");\r\n\r\n            if ($ch === false) {\r\n                throw new RuntimeException(\"Cannot initialize cURL.\");\r\n            }\r\n\r\n            curl_setopt_array($ch, array(\r\n                CURLOPT_POST => true,\r\n                CURLOPT_POSTFIELDS => $payload,\r\n                CURLOPT_HTTPHEADER => array(\r\n                    \"Content-Type: application\/json\"\r\n                ),\r\n                CURLOPT_RETURNTRANSFER => true,\r\n                CURLOPT_CONNECTTIMEOUT => 5,\r\n                CURLOPT_TIMEOUT => 60\r\n            ));\r\n\r\n            $response = curl_exec($ch);\r\n            $httpCode = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);\r\n            $curlError = curl_error($ch);\r\n            curl_close($ch);\r\n\r\n            if ($response === false) {\r\n                throw new RuntimeException(\r\n                    \"Ollama connection failed: \" . $curlError\r\n                );\r\n            }\r\n\r\n            $data = json_decode($response, true);\r\n\r\n            if ($httpCode !== 200) {\r\n                $detail = is_array($data)\r\n                    ? ($data[\"error\"] ?? \"Unknown error\")\r\n                    : \"Unknown error\";\r\n\r\n                throw new RuntimeException(\r\n                    \"Ollama HTTP \" . $httpCode . \": \" . $detail\r\n                );\r\n            }\r\n\r\n            $vector = $data[\"embeddings\"][0] ?? null;\r\n\r\n            if (!is_array($vector) || count($vector) !== 768) {\r\n                throw new RuntimeException(\r\n                    \"Expected a 768-dimensional embedding.\"\r\n                );\r\n            }\r\n\r\n            foreach ($vector as $number) {\r\n                if (!is_numeric($number) ||\r\n                    !is_finite((float)$number)) {\r\n                    throw new RuntimeException(\r\n                        \"Invalid query embedding value.\"\r\n                    );\r\n                }\r\n            }\r\n\r\n            $vectorJson = json_encode(\r\n                $vector,\r\n                JSON_PRESERVE_ZERO_FRACTION\r\n            );\r\n\r\n            if ($vectorJson === false) {\r\n                throw new RuntimeException(\"Cannot encode query vector.\");\r\n            }\r\n\r\n            $sql = DB::PrepareSQL(\r\n                \"SELECT ticket_id FROM support_tickets_ollama \" .\r\n                \"ORDER BY VEC_DISTANCE_COSINE(\" .\r\n                \"embedding_local, VEC_FromText(':1')) \" .\r\n                \"LIMIT 30\",\r\n                $vectorJson\r\n            );\r\n\r\n            $rs = DB::Query($sql);\r\n\r\n            if (!$rs) {\r\n                throw new RuntimeException(\r\n                    \"MariaDB query failed: \" . DB::LastError()\r\n                );\r\n            }\r\n\r\n            $ids = array();\r\n\r\n            while ($row = $rs->fetchAssoc()) {\r\n                $ids[] = (int)$row[\"ticket_id\"];\r\n            }\r\n\r\n            $_SESSION[\"DEVCLUB_LOCAL_SEARCH_CACHE\"] = array(\r\n                \"key\" => $key,\r\n                \"time\" => time(),\r\n                \"ids\" => $ids\r\n            );\r\n        } catch (Throwable $e) {\r\n            $_SESSION[\"DEVCLUB_LOCAL_SEARCH_ERROR\"] =\r\n                $e->getMessage();\r\n\r\n            unset($_SESSION[\"DEVCLUB_LOCAL_SEARCH_CACHE\"]);\r\n            $ids = array();\r\n        }\r\n    }\r\n\r\n    $condition = $ids\r\n        ? \"ticket_id IN (\" .\r\n            implode(\",\", array_map(\"intval\", $ids)) . \")\"\r\n        : \"1=0\";\r\n\r\n    $srch->setSearchSQL(\"semantic_query\", $condition);\r\n} else {\r\n    unset($_SESSION[\"DEVCLUB_LOCAL_SEARCH_ERROR\"]);\r\n}<\/textarea><\/pre>\n<\/div>\n<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>\n<h3>6. Sorting tickets by similarity<\/h3>\n<p>Open the <strong>support_tickets_ollama List page &#8211; Before SQL query<\/strong> event and add this code:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\n$srch = SearchClause::getSearchObject(\"support_tickets_ollama\");\r\n$phrase = trim((string)$srch->getAllFieldsSearchValue());\r\n\r\n$cache = $_SESSION[\"DEVCLUB_LOCAL_SEARCH_CACHE\"] ?? null;\r\n$key = hash(\"sha256\", \"embeddinggemma:latest\\n\" . $phrase);\r\n\r\nif (\r\n    $phrase !== \"\" &&\r\n    is_array($cache) &&\r\n    isset($cache[\"key\"], $cache[\"time\"], $cache[\"ids\"]) &&\r\n    $cache[\"key\"] === $key &&\r\n    time() - (int)$cache[\"time\"] < 300 &#038;&#038;\r\n    !empty($cache[\"ids\"])\r\n) {\r\n    $ids = array_map(\"intval\", $cache[\"ids\"]);\r\n\r\n    $strOrderBy = \" ORDER BY FIELD(ticket_id, \" .\r\n        implode(\",\", $ids) . \")\";\r\n}\r\n[\/php]\r\n\r\nThe search query filters the grid to the matching IDs. MariaDB's <strong>FIELD()<\/strong> function preserves the similarity order so the strongest match appears first.\r\n\r\n<h3>7. Preventing possible duplicate tickets<\/h3>\r\n\r\nSemantic similarity can also be checked before a record is added. Open the <strong>support_tickets_ollama Add page - Before record added<\/strong> event and add this code:\r\n\r\n[php]\r\ntry {\r\n    $subject = trim((string)($values[\"subject\"] ?? \"\"));\r\n    $description = trim((string)($values[\"description\"] ?? \"\"));\r\n\r\n    if ($subject === \"\" || $description === \"\") {\r\n        $message = \"Subject and description are required.\";\r\n        return false;\r\n    }\r\n\r\n    $text = \"title: \" . $subject .\r\n        \" | text: \" . $description;\r\n\r\n    $hash = hash(\"sha256\", $text);\r\n    $vectorJson = devclubOllamaEmbedding($text);\r\n\r\n    $sql = DB::PrepareSQL(\r\n        \"SELECT ticket_id, subject, \" .\r\n        \"VEC_DISTANCE_COSINE(\" .\r\n        \"embedding_local, VEC_FromText(':1')) AS distance \" .\r\n        \"FROM support_tickets_ollama \" .\r\n        \"WHERE embedding_hash_local IS NOT NULL \" .\r\n        \"ORDER BY VEC_DISTANCE_COSINE(\" .\r\n        \"embedding_local, VEC_FromText(':2')) \" .\r\n        \"LIMIT 3\",\r\n        $vectorJson,\r\n        $vectorJson\r\n    );\r\n\r\n    $rs = DB::Query($sql);\r\n\r\n    if (!$rs) {\r\n        throw new RuntimeException(DB::LastError());\r\n    }\r\n\r\n    $duplicateThreshold = 0.93;\r\n\r\n    while ($row = $rs->fetchAssoc()) {\r\n        if ($row[\"distance\"] === null) {\r\n            continue;\r\n        }\r\n\r\n        $similarity = 1.0 - (float)$row[\"distance\"];\r\n\r\n        if ($similarity >= $duplicateThreshold) {\r\n            $message =\r\n                \"Possible duplicate ticket found. \" .\r\n                \"Ticket ID: \" . $row[\"ticket_id\"] .\r\n                \". Subject: \" . $row[\"subject\"] .\r\n                \". Similarity: \" .\r\n                number_format($similarity, 3) .\r\n                \". Please review before adding.\";\r\n\r\n            return false;\r\n        }\r\n    }\r\n\r\n    $sqlValues[\"embedding_local\"] = DB::PrepareSQL(\r\n        \"VEC_FromText(':1')\",\r\n        $vectorJson\r\n    );\r\n\r\n    $sqlValues[\"embedding_hash_local\"] = DB::PrepareSQL(\r\n        \"':1'\",\r\n        $hash\r\n    );\r\n\r\n    return true;\r\n} catch (Throwable $e) {\r\n    $message = \"Cannot add ticket: \" . $e->getMessage();\r\n    return false;\r\n}<\/textarea><\/pre>\n<\/div>\n<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>\n<h3>8. Refreshing the vector after an edit<\/h3>\n<p>Open the <strong>support_tickets_ollama Edit page &#8211; Before record updated<\/strong> event and add this code:<\/p>\n<div class=\"my-syntax-highlighter\">\n<pre><textarea id=\"mshighlighter\" class=\"mshighlighter\" language=\"php\" name=\"mshighlighter\" >\r\ntry {\r\n    $subject = array_key_exists(\"subject\", $values)\r\n        ? (string)$values[\"subject\"]\r\n        : (string)($oldvalues[\"subject\"] ?? \"\");\r\n\r\n    $description = array_key_exists(\"description\", $values)\r\n        ? (string)$values[\"description\"]\r\n        : (string)($oldvalues[\"description\"] ?? \"\");\r\n\r\n    $text = \"title: \" . trim($subject) .\r\n        \" | text: \" . trim($description);\r\n\r\n    $hash = hash(\"sha256\", $text);\r\n    $ticketId = (int)$keys[\"ticket_id\"];\r\n\r\n    $sql = DB::PrepareSQL(\r\n        \"SELECT embedding_hash_local \" .\r\n        \"FROM support_tickets_ollama \" .\r\n        \"WHERE ticket_id = :1\",\r\n        $ticketId\r\n    );\r\n\r\n    $rs = DB::Query($sql);\r\n\r\n    if (!$rs) {\r\n        throw new RuntimeException(DB::LastError());\r\n    }\r\n\r\n    $row = $rs->fetchAssoc();\r\n\r\n    if (!$row) {\r\n        throw new RuntimeException(\"Ticket not found.\");\r\n    }\r\n\r\n    $existingHash = (string)(\r\n        $row[\"embedding_hash_local\"] ?? \"\"\r\n    );\r\n\r\n    if ($existingHash !== $hash) {\r\n        $vectorJson = devclubOllamaEmbedding($text);\r\n\r\n        $sqlValues[\"embedding_local\"] = DB::PrepareSQL(\r\n            \"VEC_FromText(':1')\",\r\n            $vectorJson\r\n        );\r\n\r\n        $sqlValues[\"embedding_hash_local\"] = DB::PrepareSQL(\r\n            \"':1'\",\r\n            $hash\r\n        );\r\n    }\r\n\r\n    return true;\r\n} catch (Throwable $e) {\r\n    $message = \"Cannot update ticket: \" . $e->getMessage();\r\n    return false;\r\n}<\/textarea><\/pre>\n<\/div>\n<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>\n<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>\n","protected":false},"excerpt":{"rendered":"<p>Add local semantic search and duplicate detection to a PHPRunner application using Ollama embeddings and MariaDB vector functions.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3537","post","type-post","status-publish","format-standard","hentry","category-php-category"],"_links":{"self":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/3537","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/comments?post=3537"}],"version-history":[{"count":6,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/3537\/revisions"}],"predecessor-version":[{"id":3545,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/posts\/3537\/revisions\/3545"}],"wp:attachment":[{"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/media?parent=3537"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/categories?post=3537"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlinesoft.com\/blog\/wp-json\/wp\/v2\/tags?post=3537"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}