Convert MySQL with Elasticsearch Using MySQLWithElasticsearch Package

Elasticsearch is a very popular full text search component that could be easily integrated in projects of various descriptions. Top rated websites (Wall Street Journal is a good example!) use Elasticsearch for website wide search.

Developers often connect Elasticsearch with MySQL using Java Database Connectivity (JDBC). However, in this tutorial, I will use a PHP package mysqlwithelasticsearch to connect an existing project with Elasticsearch and then carry out a full text search.

Please note that the code in this tutorial is applicable only to Elasticsearch version 2 or higher!

Installing and Configuring the Package

Create a new folder in the local web folder and name it elasticsearch. Next use Composer to install the package:

composer require ahmedkhan847/mysqlwithelasticsearch:dev-release1

Wait for the process to finish.

In the root folder, create a new file and name it config.php. Paste the following code in this file:

<?php
$config['index'] = "blog";
$config['type'] ="article";
/*Fields for Elasticsearch same as you are using in your mysql table*/
$config['mappings'] = [
    'article' => [
        'properties' => [
            'id' => [
                'type' => 'integer'
            ],
            'article_name' => [
                'type' => 'string'
            ],
            'article_content' => [
                'type' => 'string'
            ],
            'article_url' => [
                'type' => 'string'
            ],
            'category_name' => [
                'type' => 'string'
            ],
            'username' => [
                'type' => 'string'
            ],
            'date' => [
                'type' => 'date',
                'format' => 'dd-MM-yyyy'
            ],
            'article_img' => [
                'type' => 'string'
            ],
        ]
    ]
];
/* SQL query to insert all the data useful when you are using insertAllData($conn) function */
$config['sqlalldata'] = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id ";
/* SQL query to insert or update single data for insertNode($id = null, $con) where id will concated on runtime passed in parameter*/
$config['sqlsingledata'] = "SELECT articles.article_id,articles.article_name,articles.article_content,articles.img,articles.url,categories.category_name,CONCAT(users.u_fname,' ',users.u_lname) AS username,DATE_FORMAT(articles.date,'%d-%m-%Y') AS dates FROM article INNER JOIN users ON users.user_id = article.user_Id INNER JOIN articles ON articles.article_id = article.article_id INNER JOIN categories ON categories.category_id = articles.category_id WHERE articles.article_id =";

/*Replace your table row id field name here*/
//$config['rowid'] = "article_id";

/*Where to search*/
$config['search']['match'] = "article_content";
?>

In this config file, I first defined the index and type for Elasticsearch. Next, I set up the mapping for Elasticsearch. I, then inserted the SQL queries that determine which data to fetch.Finally, I defined the column I need to perform full text search.

Now that I have created the configuration file for the package, I will next connect it with Elasticsearch.

Fetching Data in Elasticsearch From MySQL

Create a new file and name it index.php.

For this file, I will first fetch all the data from MySQL for Elasticsearch. Before proceeding further, make sure that Elasticsearch is running. Now paste the following code in the index.php file:

<?php
require "vendor/autoload.php";
include "config.php";
use SearchElastic\SearchElastic;

$elastic = new SearchElastic($config);
$con = new mysqli("localhost","root","","cms");
$result = $elastic->insertAllData($con);

echo '<pre>';
print_r($result);
echo '</pre>';
?>

In the above code, I first created an instance of the package and then passed it the config file. Next, I created a connection to MySQL and pass it to the function insertAllData(). This function fetches all the data from the MySQL using the query that I defined in the config file, and save all the data in Elasticsearch. When the above query is executed, all the appropriate data is extracted from the MySQL and saved in Elasticsearch.

Searching MySQL

Once the data has been fetched from MySQL to Elasticsearch, I will now perform a search in it.

To do this, replace the current code in index.php with the following code:

<?php
require "vendor/autoload.php";
include "config.php";
use SearchElastic\SearchElastic;

$elastic = new SearchElastic($config);
$result = $elastic->search("working with node js");
echo "<pre>";
print_r($result);
echo "</pre>";
?>

In the above code, I performed a full text search in Elasticsearch.

CUD Elasticsearch Operations

What if you want to add data into Elasticsearch every time it is added in MySQL? mysqlwithelasticsearch provides a function that you could use to perform CUD (Create Update & Delete) operations on Elasticsearch data.

1. To add a new single data item in Elasticsearch, use insertNode($id = null, $con) function, where $id is id of the data item to be inserted in Elasticsearch and $con is the database connection.
2. To update an existing record, use updateNode($id = null, $con).
3. To delete an existing node, use deleteNode($id).

Conclusion:

In this article, I introduced mysqlwithelasticsearch package that connects MySQL with Elasticsearch. I also demonstrated how to use this package for implementing a search function. I also highlighted how to implement CUD operations using the package.