Build Local Semantic Search with Ollama and MariaDB – Xlinesoft Blog

Semantic search does not have to send text to an external embeddings service or calculate similarity in PHP. In this tutorial, we will use ollama.com to generate embeddings locally and MariaDB to store and compare the vectors.

This is a local alternative to our earlier PHPRunner semantic search tutorial. The example searches support tickets by meaning and also checks new tickets for possible semantic duplicates before saving them.

Ollama runs the embedding model on the same computer as the generated application. MariaDB’s VECTOR type stores each 768-value embedding, while VEC_DISTANCE_COSINE() finds the closest records. The ticket text and search phrases remain on the local system.

1. Installing Ollama and the embedding model

Install Ollama from ollama.com/download. This example uses EmbeddingGemma, which requires Ollama 0.11.10 or later.

Open a command prompt and download the model:


ollama pull embeddinggemma

Ollama exposes its local API at http://127.0.0.1:11434. Keep Ollama running on the machine that hosts the generated PHPRunner application. PHP also needs the cURL extension enabled.

This tutorial requires MariaDB 11.7 or later because it uses the native VECTOR type and VEC_DISTANCE_COSINE() function.

2. Adding local vector fields

The example continues with the support_tickets table from the previous tutorial. Create a copy for the Ollama version, then add the local vector and hash fields:


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;

Add support_tickets_ollama to the PHPRunner project. On its SQL Query screen, add a calculated field named semantic_query:


SELECT
    support_tickets_ollama.*,
    '' AS semantic_query
FROM support_tickets_ollama

Do not display embedding_local, embedding_hash_local, or semantic_query on the generated pages. Make semantic_query searchable so its normal search condition can be replaced by the semantic-search result IDs.

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

3. Adding the Ollama embedding function

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


if (!function_exists("devclubOllamaEmbedding")) 
    function devclubOllamaEmbedding($text)
     count($vector) !== 768) 
            throw new RuntimeException(
                "Expected 768-dimensional embedding."
            );
        

        foreach ($vector as $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;

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.

4. Generating embeddings for existing tickets

Open the support_tickets_ollama List page in Page Designer and add a button named ‘Generate embeddings’. Open its Server event and add the following code:


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: " . (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 
} catch (Throwable $e) 
    $result["message"] =
        "Embedding generation failed: " . $e->getMessage();

Open the button’s Client After event and display the returned progress message:


ctrl.setMessage(result["message"]);

The button processes up to 100 records each time it is clicked. Continue until the message reports that all tickets have local embeddings.
MariaDB requires an indexed vector column to be NOT NULL. After all existing records have embeddings, make the column required and create a cosine vector index:


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;

The index uses the same cosine distance function as the semantic-search query. Without a matching cosine index, MariaDB performs a full table scan.

5. Replacing basic search with local semantic search

Open the support_tickets_ollama List page – Before process event and add this code:


$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  count($vector) !== 768) 
                throw new RuntimeException(
                    "Expected a 768-dimensional embedding."
                );
            

            foreach ($vector as $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"]);

The event sends the search phrase to local Ollama, then MariaDB compares the returned vector with embedding_local. The 30 closest ticket IDs replace the calculated field’s normal search condition. Repeating the same query within five minutes uses the session cache.

6. Sorting tickets by similarity

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


$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"] < 300 &&
    !empty($cache["ids"])
) 
    $ids = array_map("intval", $cache["ids"]);

    $strOrderBy = " ORDER BY FIELD(ticket_id, " .
        implode(",", $ids) . ")";

[/php]

The 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.

<h3>7. Preventing possible duplicate tickets</h3>

Semantic 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:

[php]
try 
    $subject = trim((string)($values["subject"] ?? ""));
    $description = trim((string)($values["description"] ?? ""));

    if ($subject === ""  catch (Throwable $e) 
    $message = "Cannot add ticket: " . $e->getMessage();
    return false;

The event checks the three closest existing tickets. A cosine distance of zero means identical direction, so the code converts distance to similarity with 1 – distance. The 0.93 threshold is an experimental starting point; test it against your own records and adjust it to balance missed duplicates and false warnings.

8. Refreshing the vector after an edit

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


try  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;

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.

Rebuild the application and use the ‘Generate embeddings’ button until every existing ticket has a local vector. A natural-language phrase in the List page’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.

PakarPBN

A Private Blog Network (PBN) is a collection of websites that are controlled by a single individual or organization and used primarily to build backlinks to a “money site” in order to influence its ranking in search engines such as Google. The core idea behind a PBN is based on the importance of backlinks in Google’s ranking algorithm. Since Google views backlinks as signals of authority and trust, some website owners attempt to artificially create these signals through a controlled network of sites.

In a typical PBN setup, the owner acquires expired or aged domains that already have existing authority, backlinks, and history. These domains are rebuilt with new content and hosted separately, often using different IP addresses, hosting providers, themes, and ownership details to make them appear unrelated. Within the content published on these sites, links are strategically placed that point to the main website the owner wants to rank higher. By doing this, the owner attempts to pass link equity (also known as “link juice”) from the PBN sites to the target website.

The purpose of a PBN is to give the impression that the target website is naturally earning links from multiple independent sources. If done effectively, this can temporarily improve keyword rankings, increase organic visibility, and drive more traffic from search results.

Streaming Film

Nonton Film gratis

Jasa Backlink

Download Anime Batch

Build Local Semantic Search with Ollama and MariaDB – Xlinesoft Blog

1. Installing Ollama and the embedding model

2. Adding local vector fields

3. Adding the Ollama embedding function

4. Generating embeddings for existing tickets

5. Replacing basic search with local semantic search

6. Sorting tickets by similarity

8. Refreshing the vector after an edit

PakarPBN

By Thebespokeappcompany

You Missed

Build Local Semantic Search with Ollama and MariaDB – Xlinesoft Blog

Notion Forms: How to send Gravity Forms entries to Notion

Brand new release: HubSpot 3.0.5

Track referrals and reward partners

Build Local Semantic Search with Ollama and MariaDB – Xlinesoft Blog

1. Installing Ollama and the embedding model

2. Adding local vector fields

3. Adding the Ollama embedding function

4. Generating embeddings for existing tickets

5. Replacing basic search with local semantic search

6. Sorting tickets by similarity

8. Refreshing the vector after an edit

PakarPBN

By Thebespokeappcompany

Related Post

Notion Forms: How to send Gravity Forms entries to Notion

Brand new release: HubSpot 3.0.5

Track referrals and reward partners

You Missed

Build Local Semantic Search with Ollama and MariaDB – Xlinesoft Blog

Notion Forms: How to send Gravity Forms entries to Notion

Brand new release: HubSpot 3.0.5

Track referrals and reward partners