PHP Classes

PHP Elastic Search for MySQL: Index and search MySQL records with Elastic Search

Recommend this page to a friend!
  Info   View files Example   View files View files (22)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStar 58%Total: 558 All time: 5,457 This week: 555Up
Version License PHP version Categories
mysqlwithelasticsear 8Custom (specified...5.6PHP 5, Databases, Searching
Description 

Author

This class can index and search MySQL records with Elastic Search.

It takes configuration of MySQL table fields and queries that the class uses to retrieve the data from the database to index with Elastic Search.

The class takes a connection object of the MySQLi class and inserts the data to index.

It can also perform searches for given keywords and returns the matching records that were previously indexed.

Innovation Award
PHP Programming Innovation award nominee
February 2017
Number 11


Prize: One copy of the Zend Studio
MySQL has a good full text search engine built-in but when you try to use full text searches and at the same time have the application updating the database, it may be very slow because updating the indexes slow down the rest of the database accesses.

It is often faster to index the data to be searched with a separate database.

Elastic search is an independent engine for indexing and searching data using a REST API.

This class can index and search MySQL records using Elastic Search. This way you can provide a full text search on data in a MySQL database without overloading the main MySQL server.

Manuel Lemos
Picture of Ahmed Khan
Name: Ahmed Khan <contact>
Classes: 9 packages by
Country: Pakistan Pakistan
Age: ???
All time rank: 244934 in Pakistan Pakistan
Week rank: 420 Up8 in Pakistan Pakistan Up
Innovation award
Innovation award
Nominee: 3x

Example

<?php
require "vendor/autoload.php";

use
SyncMySql\SyncMySQL;
use
SyncMySql\Connection\MySQLiConnection;
use
SearchElastic\Search;
use
ElasticSearchClient\Mapping;

// $con = new mysqli('localhost','root','','laravel');

// $mysqls = new MySQLiConnection();
$connection = new \PDO('mysql:host=localhost;dbname=laravel;','root', '');
$sync = new Search();
$sync->setIndex("blog");
$sync->setType("user");
$sync->setSearchColumn("name");
// $sync->setConnection($mysqls);
echo print_r($sync->search("ahmed khan"));
// echo print_r($sync->updateNode($con,'users',21));
// echo $sync->insertNode([
// "id" => 5,
// "name0" => "abc",
// "design" => "pattern"
// ]);
  
// $search = new Search();
// $search->setIndex("abc");
// $search->setType("asd");
// $search->setSearchColumn("name");
// echo print_r($search->search("ahmed"));

// $client = new Mapping();
// // $map=;
// $mapping = ['index' => 'abc3',
// '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'
// ],
// ]
// ]
// ]
// ]
// ];
// echo print_r($client->deleteMapping("abc"));

$connection = new PDO('mysql:host=localhost;dbname=laravel;','root', '');

print_r($connection);


Details

Connect MySQL With Elasticsearch using PHP

A small library to connect MySQL with Elasticsearch. Use it to sync data and do full text search. Working example for release1 can be found on my article How To Setup Elasticsearch With MySQL

Click here to find the API documentation for v2

Downloading the latest release

Clone the library

git clone -b release2 https://github.com/ahmedkhan847/mysqlwithelasticsearch

Now, run composer install to install the required dependencies.

Or use composer to install complete package.

composer require ahmedkhan847/mysqlwithelasticsearch:2.*

What's in release2?

In release2 package if fully redesign. Now you don't need to pass $config file to constructor. You can set index, type, sql query, sql connection dyamically. Even now you can create your own function for searching in Elasticsearch. Let's see how you can achieve the following:

Mapping in Elasticsearch

<?php
require "vendor/autoload.php";
use ElasticSearchClient\Mapping;

$mapping = new Mapping();
$map = ['index' => 'blog',
        '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'
                        ],
                    ]
                ]
            ]
        ]
       ];
$mapping->createMapping($map);

Indexing All MySQL data in Elasticsearch

<?php
require "vendor/autoload.php";
include "config.php";
use SyncMySql\SyncMySql;
$elastic = new SyncMySql();
$connection = new \PDO('mysql:host=localhost;dbname=laravel;', 'root', '');
$sync->setIndex("blog");
$sync->setType("users");
//Where 1st param is the database connection and 2nd param is tableName
$sync->insertAllData($connection, "users");
echo '<pre>';
print_r($result);
echo '</pre>';

By default it is using "SELECT * FROM tablename", use 'id' as a default id column for table and elasticsearch. It is using PDO connection to fetch the data by default. If you want elasticsearch to fetch data using mysqli connection you can also use that you just need to set the connection to SyncMySql\Connection\MySQLiConnection or write your own by implementing SyncMySql\Connection. Also you can change the select query but don't forget to define an id column in it. Let's see how you can do it.

Indexing All MySQL data in Elasticsearch using MySqli Connection

<?php
require "vendor/autoload.php";

use SyncMySql\SyncMySql;
use SyncMySql\Connection\MySQLiConnection;
$sync = new SyncMySql();
$connection = new \mysqli('localhost', 'root', '', 'laravel');
$sync->setIndex("blog");
$sync->setType("article");
$sync->setConnection(new MySQLiConnection());
$sync->setSqlQuery("SELECT id,title,body FROM posts");
//Now you don't need to pass the tablename'
$sync->insertAllData($connection);
echo '<pre>';
print_r($result);
echo '</pre>';

Indexing Single Data in Elasticsearch

<?php
require "vendor/autoload.php";

use SyncMySql\SyncMySql;
use SyncMySql\Connection\MySQLiConnection;
$sync = new SyncMySql();
$connection = new \mysqli('localhost', 'root', '', 'laravel');
$sync->setIndex("blog");
$sync->setType("article");
$sync->setConnection(new MySQLiConnection());
$result = $sync->insertNode($connection,21,"users");
echo '<pre>';
print_r($result);
echo '</pre>';

If you have want to define you own query then:

<?php
require "vendor/autoload.php";

use SyncMySql\SyncMySql;

$sync = new SyncMySql();
$connection = new \PDO('mysql:host=localhost;dbname=laravel;', 'root', '');
$sync->setIndex("blog");
$sync->setType("article");
$sync->setSqlQuery("SELECT id,title,body FROM posts");
//Now you don't need to pass the tablename'
$result = $sync->insertNode($connection,21);
echo '<pre>';
print_r($result);
echo '</pre>';

Updating in Elasticsearch

<?php
require "vendor/autoload.php";

use SyncMySql\SyncMySql;

$sync = new SyncMySql();
$connection = new \PDO('mysql:host=localhost;dbname=laravel;', 'root', '');
$sync->setIndex("blog");
$sync->setType("article");
$result = $sync->updateNode($connection,21,"users");
echo '<pre>';
print_r($result);
echo '</pre>';

Using same technique as we do for insert you can add your own select query using setSqlQuery().

Deleting data from Elasticsearch

<?php
require "vendor/autoload.php";

use SyncMySql\SyncMySql;

$sync = new SyncMySql();
$sync->setIndex("blog");
$sync->setType("article");
$result = $sync->deleteNode(21);
echo '<pre>';
print_r($result);
echo '</pre>';

Searching in Elasticsearch

<?php
require "vendor/autoload.php";

use SearchElastic\Search;

$search = new Search();
$search->setIndex("blog");
$search->setType("user");
$search->setSearchColumn("name");
$result = $search->search("ahmed khan");
echo '<pre>';
print_r($result);
echo '</pre>';

Creating your own search class for Elasticsearch

In order to write your own search you should extends it from SearchAbstract class and complete the public function search($query) in it.

<?php
namespace SearchElastic;

//Extends your class from SearchAbstract
use SearchElastic\SearchAbstract\SearchAbstract;

class CustomPostSearch extends SearchAbstract
{
    /
     * Write your own search method
     *
     * @param  string  $query
     * @return Result from elasticsearch
     */
    public function search($query)
    {
        $this->validate($query);
        //get the elasticsearch client from the base class
        $client = $this->client->getClient();
        $result = array();
        /Write your own query/
        $params = [
            //you can add your index directly here or use this function if you are planning to set it on runtime $search->setIndex("blog") and then use $this->client->getIndex() to get index
                'index' => "blog",
            //you can add your type directly here or use this function if you are planning to set it on runtime using $search->setType("post") and then use $this->client->getIndex()    
                'type'  => "post", 
                'body'  => [
                    'query' => [
                        'match' => [ "post" => $query],
                    ],
                ],
            ];
        $query  = $client->search($params);
        // you can use the base method to extract result from the search or return the result directly    
        return  $this->extractResult($query); 
    }
}

If you want contribute, fork master branch.


  Files folder image Files  
File Role Description
Files folder imagesrc (4 directories)
Files folder imagetests (4 files)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file composer.lock Data Auxiliary data
Accessible without login Plain text file index.php Example Example script
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  src  
File Role Description
Files folder imageElasticSearchClient (2 files)
Files folder imageMySQLWithElasticsearchExceptions (2 files)
Files folder imageSearchElastic (1 file, 1 directory)
Files folder imageSyncMySql (2 files, 1 directory)

  Files folder image Files  /  src  /  ElasticSearchClient  
File Role Description
  Plain text file ElasticSearchClient.php Class Class source
  Plain text file Mapping.php Class Class source

  Files folder image Files  /  src  /  MySQLWithElasticsearchExceptions  
File Role Description
  Plain text file SearchException.php Class Class source
  Plain text file SyncMySqlExceptions.php Class Class source

  Files folder image Files  /  src  /  SearchElastic  
File Role Description
Files folder imageSearchAbstract (1 file)
  Plain text file Search.php Class Class source

  Files folder image Files  /  src  /  SearchElastic  /  SearchAbstract  
File Role Description
  Plain text file SearchAbstract.php Class Class source

  Files folder image Files  /  src  /  SyncMySql  
File Role Description
Files folder imageConnection (4 files)
  Plain text file SyncMySql.php Class Class source
  Plain text file SyncMySqlYii.php Class Class source

  Files folder image Files  /  src  /  SyncMySql  /  Connection  
File Role Description
  Plain text file Connection.php Class Class source
  Plain text file MySQLiConnection.php Class Class source
  Plain text file PDOConnection.php Class Class source
  Plain text file YiiConnection.php Class Class source

  Files folder image Files  /  tests  
File Role Description
  Plain text file ElasticSearchClientTest.php Class Class source
  Plain text file MappingTest.php Class Class source
  Plain text file SearchTest.php Class Class source
  Plain text file SyncMySqlTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:558
This week:0
All time:5,457
This week:555Up
 User Ratings  
 
 All time
Utility:75%StarStarStarStar
Consistency:75%StarStarStarStar
Documentation:66%StarStarStarStar
Examples:66%StarStarStarStar
Tests:-
Videos:-
Overall:58%StarStarStar
Rank:1518