Elasticsearch is an open-source full-text search engine. It allows you to store and search data in real time. The search time in Elasticsearch is considerably faster than SQL. You can also search for a phrase, and the engine will give you the results within seconds depending on how large the Elasticsearch database is.
Suppose you are running a custom CMS on a PHP hosting, and its database has become too large, and as a result, its search time is slower than before. In this care, not only is Elasticsearch the best tool to increase the site search time, but it also helps the users search the complete text within your website quickly.
In this article, I will elaborate Elasticsearch in the context of a CMS. I will teach you how to connect your existing MySQL database with Elasticsearch and perform CRUD queries and perform a search from Elasticsearch database. I will be using the same Elasticsearch API “elasticsearch/elasticsearch” which I used to connect PHP with Elasticsearch in my previous article.
Let’s get started.
Host PHP Websites with Ease [Starts at $10 Credit]
- Free Staging
- Free backup
- PHP 8.0
- Unlimited Websites
Step 1: Create Custom Cluster and Node
Let’s create the custom cluster and node first. From a security perspective, it is a good practice to have a unique name for the cluster and node. Go to your Elasticsearch folder, open the config folder and then open elasticsearch.yml file in an editor. Name the cluster and node as:
Cluster.name = blog Node.name = articles
Save the file. Now let us create a class which will perform CRUD and search operation in the cluster.
Step 2: Create CRUD Functions
I’ll create a separate class for handling CRUD operations in PHP and MySQL and name this class searchelastic.
<?php
require 'vendor/autoload.php';
class SearchElastic
{
private $elasticclient = null;
public function __construct()
{
$this->elasticclient = Elasticsearch\ClientBuilder::create()->build();
}
First, I loaded the elasticsearch/elasticsearch API library and then I created a private variable which will handle the connection of Elasticsearch inside the class. Next, I created a constructor so that whenever the class is called, a connection with Elasticsearch is created automatically. First, let’s create a function for Mapping data types to the fields in Elasticsearch:
public function Mapping(){
$params = [
'index' => 'articles',
'body' => [
'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'
],
]
]
]
]
];
$this->elasticclient->indices()->create($params);
}
Now we need a function that will gather all the data from MySQL database and save it in Elasticsearch database. Let’s create it using the following code:
public function InsertData($conn)
{
$con = $conn;
$client = $this->elasticclient;
$sql = "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 ";
$result = $con->query($stmt);
$params = null;
while ($row = $result->fetch_assoc())
{
$params['body'][] = array(
'index' => array(
'_index' => 'articles',
'_type' => 'article',
'_id' => $row['article_id'],
) ,
);
$params['body'][] = ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ];
}
$this->Mapping();
$responses = $client->bulk($params);
return true;
}
In this function, I first called a MySQL database connection and performed a query which gathers all the articles from our database along with user names who published these articles.
You might also like: How To Connect MySQL Database With PHP Websites
Next, we save it in an array $params[‘body’][]. Note that we are keeping the same ID as MySQL database which will help us in updating or deleting the data. Once entire data is fetched, I have mapped the data types by calling the Mapping() function. Next, I saved the array in Elasticsearch by calling $client->bulk($params);. bulk() is used to save bulk data. This function needs to be called only once when connecting MySQL database to Elasticsearch so that all the data that is saved in MySQL will be saved in Elasticsearch.
The functions I will create below will be recalled every time a new post is added, updated or deleted.
Let’s create a function which will add the newly created article in the Elasticsearch database now:
public function InsertNode($articleid, $con)
{
$conn = $con;
$client = $this->elasticclient;
$stmt = "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 = $articleid";
$result = $con->query($stmt);
$params = null;
while ($row = $result->fetch_assoc()) {
$params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['url'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];
}
$responses = $client->index($params);
return true;
}
This function has two parameters; the ID of the newly created article, and a MySQL connection string. The function first fetches the data and then indexes it in Elasticsearch.
Now, let’s create a function for Update and Delete as well. The function will be similar to the one above except an Elasticsearch API function that will be changed.
public function UpdateNode($articleid, $con)
{
$conn = $con;
$client = $this->elasticclient;
$stmt = "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 = $articleid";
$result = $con->query($stmt);
$params = null;
while ($row = $result->fetch_assoc()) {
$params = ['index' => 'articles', 'type' => 'article', 'id' => $row['article_id'], 'body' => ['article_name' => $row['article_name'], 'article_content' => $row['article_content'], 'article_url' => $row['article_id'], 'category_name' => $row['category_name'], 'username' => $row['username'], 'date' => $row['dates'], 'article_img' => $row['img'], ]];
}
$responses = $client->update($params);
return true;
}
public function DeleteNode($id)
{
$client = $this->elasticclient;
$params = ['index' => 'articles', 'type' => 'article', 'id' => $id, ];
$responses = $client->delete($params);
return true;
}
Now that the CRUD functionality for Elasticsearch has been successfully created, this class will be called whenever update or delete operations are carried out for any post. For example, when a new post is inserted into the MySQL database, I need to call InsertNode() in order to insert the post in Elasticsearch Database.
Now we’ll create a function which will search the user query in Elasticsearch data.
You Might Also Like: MySQL Performance Tuning for Query Optimization
Step 3: Search the Elasticsearch Data
Searching the Elasticsearch database is simple. The process requires sending user query to Elasticsearch, which will then return the result for that query. Here’s the code to make a function for search in elasticsearch class:
public function Search($query)
{
$client = $this->elasticclient;
$result = array();
$i = 0;
$params = ['index' => 'articles', 'type' => 'article', 'body' => ['query' => ['match' => ['article_content' => $query], ], ], ];
$query = $client->search($params);
$hits = sizeof($query['hits']['hits']);
$hit = $query['hits']['hits'];
$result['searchfound'] = $hits;
while ($i < $hits) {
$result['result'][$i] = $query['hits']['hits'][$i]['_source'];
$i++;
}
return $result;
}
Elasticsearch search() takes an array in which the index and query is submitted. Learn more about Elasticsearch search() here.
Supercharged Managed PHP Hosting – Improve Your PHP App Speed by 300%
Conclusion
This article gave you a comprehensive step by step guide to making Elasticsearch as your custom search engine for MySQL powered PHP sites. I have created a CMS which uses Elasticsearch as the default site search, which is open source and can be found on my GitHub.
If you have any questions about the implementation and integration of Elasticsearch with MySQL in custom PHP websites, just leave a comment below and we’ll get back promptly!
Ahmed Khan
Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works.
