Reduce Search Time in MySQL Text Search using FULLTEXT Index and MATCH .. AGAINST

Recently I have been working in a project which is having millions of records in MySQL database and I need to search the table with a given text against a title field. As usual I wrote a query using LIKE command and got the desired output, but when I checked the performance factor I was shocked to see the delay in getting the results from the MySQL sever. So I started to do some experiments to improve the performance of searching text in MySQL where millions of records are present. Using MySQL FULLTEXT MATCH AGAINST is the solution we are looking into.

After some research on the topic I found indexing the MySQL can increase the search performance and reduce the time for query execution. But soon I realized indexing will not make the much difference in performance when LIKE command is used. So I kept of researching.

Then I come across the FULLTEXT indexing in MySQL for text searching. I have created a test db with about 8 million records with two columns with same data in each record. One column is normal and the other one is FULLTEXT indexed. The result was amazing.

Above you can see  the results. Column text1 is normal column and text2 is FULLTEXT indexed column. You may have noted in FULLTEXT indexed column I am not using like but instead I am using MATCH … AGAINST command. This is command used to search in FULLTEXT indexed column.

Checking the results and the time for execution you can see the improvement in performance. In repeated random search I was getting about 60 times better performance using the FULLTEXT index. MATCH … AGAINST can be used in different modes as per your requirement.

The only drawback I noted in using MATCH .. AGAINST command is unlike LIKE command MATCH against cannot use wildcard in the prefix of the searched text. That mean LIKE ‘%text’ cannot be done using MATCH command.

Yii Framework 2.0 Preview Rleased

Yii  development team has released the first preview of Yii Framework 2.0. The code is available at at GitHub. Development of version 2.0 has been started 2 year ago and the complete code has been rewritten.

Yii 2.0 is still under heavy development, so it is not yet able to use for production. Yii was the most popular framework on 2012.

You may keep an eye on the following Yii 2 resources:

Generate Array of Months and Years for Credit Card Expiry Dropdowns

This is a simple but frequently required bit of code. I guess most of the developers hard code the dropdown data for credit card expiry fields. Here are two simple functions which will generate the required array of data for population expiry month and expiry year dropdowns.

Function getMonths() will return an array of 12 Months and getYears() will generate array of 10 years from current year.

I hope this is be a quick simple help for you guys.

Convert Excel Sheet into PHP Array using PHPExcel

I was playing with PHPExcel library for a few days. PHPExcel is very good library to deal with Excel files in PHP.

PHPExcel is a library written in pure PHP and providing a set of classes that allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML.

PHPExcel offers wide range of functionality which cannot be desccribed in a single article. So I thought giving multiple article about different functionality provided by the PHPExcel library. Now I would like to point out one of the simple and might be a very helpful funtion provided in the PHPExcel class which allows  us to convert an Excel sheet into a PHP Array with a few simple lines of codes. Once we have the Excel data in a PHP array I hope everybody know how to deal with the data in array in the way you want to process it.

PHPExcel has several classes inside it to handle different objects. Inorder to handle a worksheet we need to use PHPExcel_Worksheet. This class provide a funtion called toArray() which can be used to convert an Excel worksheet into a php array.

There are some arguments which can be passed to this function to customize the output array. Here is the signature of the function with the argument list

There are some other functions which are related to toArray function. They are namedRangeToArray() and rangeToArray(). These function can be used to convert a specific range of data inside a sheet to an array. In these functions we have to specify the Range of cells (i.e. “A1:B10”), or just one cell (i.e. “A1”), to convert that data into an array.

Sort Array Based on Second Level Key on 2 Dimensional Array

There are several occasions in my development where I need to sort a 2D array based on a key in second level array. Unfortunately I couldn’t find a suitable built-in PHP function for this purpose. PHP built-in function array_multisort is the only function which can be used for this purpose, but there are limitations for this function.

Here I am introducing a function aasort which takes a two dimensional associative array and another array of keys with which we want to sort the array. This function seems very handy in several situations like sorting database records.

I am providing some examples of sorting done using aasort function. In the following example I am sorting a 2D PHP array with multiple keys in the second level array.


Generate PDF from HTML code in PHP using mPDF

Recently I came across a requirement where I need to generate a pdf file in PHP. There are several PHP libraries which can be used to generate PDF. But after a little research I found an interesting library, mPDF which can convert HTML code to PDF keeping the structure.

mPDF is a smart library which consider the CSS attached to the HTML.  Not only CSS, mPDF takes care of almost all the HTML tags like, form tags, tables, images, lists etc..

Here is a basic example of using mPDF to generate PDF for a simple HTML page.

Wondering only this much? Yes, it is as simple as that, include the library, create the object and invoke the WriteHTML function passing the HTML you want to convert into PDF. Output function will render the generated PDF in the browser.

Here is another example in which we can see the mPDF consifering the CSS along with the HTML

And here is one more example in which you can email the PDF generated using mPDF directly.

More detailed examples are given here. I hope this helps you some where in your development.

If you need any kind of assistance regarding generating PDF using mPDF, I am happy to invest my time for you, Feel free drop a comment.