DEV Community: Ahmed Khan

Convert MySQL with Elasticsearch Using MySQLWithElasticsearch Package

Ahmed Khan — Tue, 07 Feb 2017 10:47:03 +0000

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.

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.
To update an existing record, use updateNode($id = null, $con).
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.

Smart Guide On Creating a Complete PHP Package Using Composer

Ahmed Khan — Fri, 13 Jan 2017 12:15:18 +0000

Composer is a dependency manager for PHP created by Nils Adermann, Jordi Boggiano and other community contributors. With Composer, developers could develop code that include components from several libraries and packages. Composer installs all required libraries and packages automatically, and thus the code does not break because of the absence of a required code component.

Another important use of Composer is the creation of a self-contained package that contains all the required code components in one place. Thus, instead of pulling code from several locations and resources, the developer has to include a single package and focus solely on implementation of the logic rather than worrying about external code resources.

The most common usage of the Composer is the following single command that installs a particular package:

$ composer install 'packagename:version'

Package Creation Using Composer

Composer is also used to create custom packages from open source packages and/or private packages. These custom packages could then be installed using the above mentioned command. In this article, I will demonstrate the package creation capability of Composer by creating a package for Twitter search API. I will then register it and then install it on my server.

Before starting, make sure that Composer is installed on the local system.

Git Repo Creation

The first step is to create a public Git repo. Go to the GitHub account and create a new repository with .gitignore file. Next, add the following folder and file to .gitignore file. ( in some instances, these items are auto-generated)

composer.phar
/vendor/

Once the repo has been created, clone it to the local system. The next step is the creation of a TwitterSearch class, which will be based on Twitter API.

The TwitterSearch Class

I will base the Twitter PHP-SDK on PSR-4 namespacing. Before getting started with the class creation, run the following command in the local Git repository folder to install Guzzle HTTP Client for handling Twitter request and response.

$composer require "guzzlehttp/guzzle:^6.2";

In the local Git folder create a new folder and name it Twitter. Next, in this folder, create a new file and name it Base.php. Open this file in your code editor and add the following code to it:

namespace Twitter;
use GuzzleHttp\Client;
use GuzzleHttp\Exception\RequestException;
use GuzzleHttp\Psr7\Request;

/**
*
*/
class Base
{

    const API_URL = "https://api.twitter.com/1.1";

    protected $client;
    protected $token;
    protected $tokensecret;
    protected $accesstoken;
    public function __construct()
    {
        $this->client = new \GuzzleHttp\Client();
    }

    public function setToken($token,$secret)
    {
        $this->token = $token;
        $this->tokensecret = $secret;
    }

    protected function prepareAccessToken()
    {
        try{
            $url = "https://api.twitter.com/oauth2/token";
            $value = ['grant_type' => "client_credentials"
            ];
            $header = array('Authorization'=>'Basic ' .base64_encode($this->token.":".$this->tokensecret),
            "Content-Type"=>"application/x-www-form-urlencoded;charset=UTF-8");
            $response = $this->client->post($url, ['query' => $value,'headers' => $header]);
            $result = json_decode($response->getBody()->getContents());

            $this->accesstoken = $result->access_token;
        }
        catch (RequestException $e) {
            $response = $this->statusCodeHandling($e);
            return $response;
        }
    }
    protected function callTwitteAPIr($method,$request,$post = [])
    {
        try{
            $this->prepareAccessToken();
            $url = self::API_URL . $request;
            $header = array('Authorization'=>'Bearer ' . $this->accesstoken);
            $response = $this->client->request($method,$url, array('query' => $post,'headers' => $header));
            return json_decode($response->getBody()->getContents());
        } catch (RequestException $e) {
            $response = $this->StatusCodeHandling($e);
            return $response;
        }
    }
    protected function statusCodeHandling($e)
    {
        $response = array("statuscode" => $e->getResponse()->getStatusCode(),
        "error" => json_decode($e->getResponse()->getBody(true)->getContents()));
        return $response;
    }
}

This is the base class that is responsible for the generation of access tokens for Twitter API and API related requests.

I will now explain some aspects of this class.

First I initialized the namespace, and then the Guzzle classes. Next, I created a constant for Twitter API URL, various variables, and a constructor that call Guzzle\Client.

const API_URL = "https://api.twitter.com/1.1";

    protected $client;
    protected $token;
    protected $tokensecret;
    protected $accesstoken;
    public function __construct()
    {
        $this->client = new \GuzzleHttp\Client();
    }

Next, I created functions for preparing the access token and calling the user-generated Twitter API requests.

public function setToken($token,$secret)
    {
        $this->token = $token;
        $this->tokensecret = $secret;
    }

Next, I created functions for preparing the access token and calling the user-generated Twitter API requests.

  protected function prepareAccessToken()
    {
        try{
            $url = "https://api.twitter.com/oauth2/token";
            $value = ['grant_type' => "client_credentials"
            ];
            $header = array('Authorization'=>'Basic ' .base64_encode($this->token.":".$this->tokensecret),
            "Content-Type"=>"application/x-www-form-urlencoded;charset=UTF-8");
            $response = $this->client->post($url, ['query' => $value,'headers' => $header]);
            $result = json_decode($response->getBody()->getContents());

            $this->accesstoken = $result->access_token;
        }
        catch (RequestException $e) {
            $response = $this->statusCodeHandling($e);
            return $response;
        }
    }
    protected function callTwitter($method,$request,$post = [])
    {
        try{
            $this->prepareAccessToken();
            $url = self::API_URL . $request;
            $header = array('Authorization'=>'Bearer ' . $this->accesstoken);
            $response = $this->client->request($method,$url, array('query' => $post,'headers' => $header));
            return json_decode($response->getBody()->getContents());
        } catch (RequestException $e) {
            $response = $this->StatusCodeHandling($e);
            return $response;
        }
    }
    protected function statusCodeHandling($e)
    {
        $response = array("statuscode" => $e->getResponse()->getStatusCode(),
        "error" => json_decode($e->getResponse()->getBody(true)->getContents()));
        return $response;
    }

In addition, I have created a function for handling the error codes.

Now create a new folder in the local Git repo folder and name it Search. In this folder create a new file, name it Search.php and paste the following code in it:

namespace Twitter\Search;
/**
 * 
 */
class Search extends \Twitter\Base
{

    public function search($value)
    {
        try{
            $url = "/search/tweets.json";
            $response = $this->callTwitter("get",$url,$value);
            return $response;
        } catch (RequestException $e) {
            $response = $this->StatusCodeHandling($e);
            return $response;
        }
    }    
}

Let me explain the code in some detail.

First, I gave a namespace for the class and then extend it to the Base class. Next, I created a function Search() to perform search via Twitter Search API. Search() accepts the variable $value that contains the parameters of the search query as defined in the Twitter API.

public function search($value)
    {
        try{
            $url = "/search/tweets.json";
            $response = $this->callTwitter("get",$url,$value);
            return $response;
        } catch (RequestException $e) {
            $response = $this->StatusCodeHandling($e);
            return $response;
        }
    }

Next, I will add the namespace in composer.json file that will be included in the autoload.php. Open composer.json file and add the following lines to it:

,
"autoload": {
        "psr-4": {
            "Twitter\\" : "Twitter/"
        }
    }

The new composer.json file will look like this:

{
    "require": {
        "guzzlehttp/guzzle": "^6.2"
    },
    "autoload": {
        "psr-4": {
            "Twitter\\" : "Twitter/"
        }
    }
}

Now run the following command.

$ composer dump-autoload

Get the Keys

The next step is to get the consumer key and the consumer secret key for Twitter API.

To get the keys, login to https://apps.twitter.com/. Create a new application in order to get the consumer keys.

Test the Project

Now that I have all the necessary components of the project, I could now test them to make sure that everything is working properly.

In the root of the Git folder, create a new file with the name index.php, and paste the following code in it:

include "vendor/autoload.php";

use Twitter\Search\Search;

$search = new Search();
$search->setTokken("afmowdkHQTZVZWMxx0dh5Bz4r","fuJ4HNNTkI27qZEFdv3g4MEMXdVoZHUIVXCwmDbqXBRWMrEQAU");
$value = ["q" => "ahmed khan"];
echo "<pre>";
print_r($search->search($value));
echo "</pre>";

Now run index.php on the localhost. If you get the result array, things are working correctly!

Now that the project is completed and working perfectly, I will now convert it into a Composer package.

Create the Composer Package

Open composer.json and replace the code with the following:

{
    "name": "ahmed.khan/twittersdkphp", //naming our package
    "description": "A PHP-SDK for Twitter Search API", // Description about package
    "keywords": ["twitter search", "search"], // Keywords
    "type": "package", //Since it's a package  we define its type package
    "require": {
        "guzzlehttp/guzzle": "^6.2"
    },
    "license": "MIT",
    "authors": [{ //Your information
        "name": "Ahmed Khan", 
        "email": "ahmedkhan_847@hotmail.com"
    }],
    "autoload": {
        "psr-4": {
            "Twitter\\": "Twitter/"
        }
    },
    "version": "1.0.0", //Version of the package
    "minimum-stability": "dev" //Stability

}

All the schema can be found here. Now push all the code to the Git repository. Once it is done, go to https://packagist.org/. Login with GitHub and then click Submit in the top menu. Paste the URL of the Git repository and click the Check button.

Wait for the verification process to finish and then click the Submit button.

Once done, the following message will appear:

"The packagist is not auto-updated"

In order to resolve this issue, go to the GitHub account and the page to the settings of the related repository. Click Integrations & Services > Add Service, find and click Packagist.

You might be prompted to enter your GitHub password. In the next window, add your Packagist username and token. Check the Active box and then click the Add service button.

Once the service has been added, click the Test service button.

Now refresh the page and observe that the error is gone!

Now that the package has been created, I will now test it!

Test the Composer Package

Now on a separate machine or a server, run the following command to install the package via Composer.

$ composer require "ahmedkhan847/twittersdkphp:dev-master"

You will notice that the package is being installed.

Conclusion

In this tutorial, I created a Composer package based on Twitter Search API. I also submitted the package to Packagist, and then tested it by installing the package via Composer. You can find this complete package on Githuband on Packagist. If you face any issue during the process, leave a comment below.

Hi, I'm Ahmed Khan

Ahmed Khan — Tue, 03 Jan 2017 14:56:14 +0000

I have been coding for 3 years.

You can find me on GitHub as ahmedkhan847

I live in Karachi, Pakistan.

I work for Cloudways

I mostly program in these languages: C#,PHP,JS,HTML,CSS.

I am currently learning more about PHP and its framework.

Nice to meet you.