Handling a Keyword Search with PHP/MySQL (MATCH/AGAINST MySQL Function)

For the longest time I used the MySQL ‘LIKE’ statement style for searching by keywords, which it is probably the most reliable and fastest option. But today I’m going to talk about another option you have to create a more effective keyword search and get the result from MySQL order by keywords relevance.

For this tutorial you will require (unless you work directly in your web hosting):

  1. xampp or any other Web Server installed in your system. (Apache/MySQL/PHP).
  2. PHPMyAdmin – Browser Based PHP MySQL Administrator.
  3. Some knowledge in PHP and MySQL is a plus but not a most.
  4. Variable: $input (variable that holds the user request), $string ($input exploded in an array), $sql (the SQL request string variable).

Alright let’s first create a table in MySQL to test the code:

Creating Table MySQL

CREATE TABLE IF NOT EXISTS `cds` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR(45) NULL ,
`year` VARCHAR(45) NULL ,
`tags` VARCHAR(255) NULL ,
PRIMARY KEY (`id`),
FULLTEXT (tags(255))
)
ENGINE = MyISAM;

Final Structure Result

Notice that I created an FULLTEXT Index using the `tags` column. This is going to be our search indexed column.

Final CREATE Result Hightlights

Now let’s populate the table `cds` with some information:

Inserting Information MySQL

INSERT INTO `cds` (`id`, `title`, `year`, `tags`)
VALUES (NULL, 'What about Now', '1996', 'Christian,The Kry,Rock,Alternative,1996'),
(NULL, 'The Singles', '2000', 'Pop,80''s,Rock,Hard Rock,Corey Hart,2000'),
(NULL, '18 ''til I die', '1996', 'Bryan Adams,Pop,1996,Hard Rock'),
(NULL, 'Almas Unidas', '2002', 'Cristiano,Perucho,Christian,Rock,Pop,Ballad'),
(NULL, 'Falta Amor', '1992', 'Pop,Maná,Latino,Rock');

With PHPMyAdmin installed you only need to select and Schema then select SQL to run both this codes. You can run it both at once all each by itself. If you run both at once then make sure CREATE is the first code and there’s a semicolon (;) after line ENGINE = MyISAM.

Previously, when I made a search with a list of keywords I had to explode a string line in an array then run a dynamic list of LIKE statements, for example:

<?php
//exploding user input into an array
$list = explode(' ',$input);
$sql = "SELECT * FROM `cds` WHERE";
$sqlOption = array();
//--
//creating tags keywords search
foreach($list as  $keyword){
$sqlOption[] = "tags LIKE '%".addslashes($keyword)."%'";
}
//--
// joining the array to look something like
// tags LIKE 'keyword1' OR tags LIKE 'keyword2' etc...
$sql .= " ".join(' OR ',$sqlOption).";";
?>

If the user input would’ve be something like "1996 80's Rock" the previous SQL would looks like:

SELECT * FROM cds WHERE tags LIKE '%1996%' OR tags LIKE '%80\'s%' OR tags LIKE '%Rock%';

Even though this way is faster in some cases it doesn’t give you the option to order the results by relevance of the keywords entered. Now you might ask, how can I do that? Well, you can make a couple lines of codes with PHP to do this, all you can just use the MATCH/AGAINST functionality included with MySQL. Let see the code:

<?php
//join the previous array $list this time
//using the + sign that means that tags must include
//and * that's like % in the LIKE statement
$string = join('* +',$list);
//--
//We are using now the MATCH AGAINST function from MySQL
$sql = "SELECT *,MATCH(tags) AGAINST(+$string*) AS `relevance` FROM `cds` WHERE MATCH(tags) AGAINST('+$string*' IN BOOLEAN MODE) ORDER BY relevance DESC;";
?>

As you see this code is a little bit cleaner, but this is not why I prefer it. Look closer the SQL statement, I created a column called relevance holding the result from the MATCH/AGAINST statement, then run it again as the filter for the search but this time using IN BOOLEAN MODE which will determine a value from 0 to 1 in relevance, the most precise results will be closer to 1 and the less relevant will be closer to 0, meaning the most relevant will be in top because we set the ORDER BY as DESC (descendant).

MATCH/AGAINST SQL Statement

MATCH/AGAINST Results

The downside is that multiple columns search tend to be a hassle, even just for the slightest mistake won’t work, sometimes throw INDEX errors even though there are FULLTEXT indexes set by each column. Remember, any column that you would like to search need to have a FULLTEXT index in order the MATCH/AGAINST statement to work. Meaning that might slow down performance or increase bandwidth. The recommended way to do it is to create a column or an indexed table to which you run the code against.

By the way, this is just basic functionality, remember to always secure your code with whatever extension you use, PDO, MySQLi, etc…

If you would like more information about this functionality from MySQL you can visit:

  1. MySQL Match/Against Order by relevance
  2. MySQL – FULLTEXT BOOLEAN
  3. MySQL Indexing and Searching

Well, I hope this would help anyone… HAPPY CODING!

How to Avoid Having Sensitive Data Exposed

There are few reasons why you have to have sensitive information inside a class, but the one in the top of these reasons is because many hosting services does not allow you to have access to your root folder or even to your php.ini file.

This is a big problem specially if you have a customer that’s not willing to invest much in a website. Shared Hostings are the cheapest options but not all of them allow you access, as I said before, to a root folder that’s not exposed to the public.

When you creating a class there is a simple way where you can protect sensitive data with the variables. As I explained in Controlling Your Variables In A PHP Class, there are ways to create variables that are protected from being changed if it is not through a method, there are also a way to avoid exposed information when an object is created. For a basic tutorial about classes read PHP Basic: How To Create A PHP Class.

As most of you know, specially those who wants to harm your website, when you create an object and you hold this object in a variable, every variable is exposed, except those who properly set up, but regular protected variables are exposed as well as any other variable that’s not set as private.

Constants are the worst option to have sensitive data on it. Constants are not exposed in the object but are accessible from the object, for example:

Creating a more secure class

The above code will output:

Create a more secure class 002

So, as you see this is not a good way to save your sensitive data. Then, What can you do to protect your data? There are many ways you can protect your data, but none of them can guaranteed you to be 99.9% secure, or even 80%. But if you have no other choice you have to deal with it, or at least until your contract with your hosting company finishes. When this does happens I would recommend to move to a hosting service that allow your access to root folders, as Blue Host does.

For the moment this are some ideas you can use:

Secure Your Classes

Make a folder in your public root folder specific for your classes and secure it with an .htaccess file, if your system is Apache based; Most inexpensive web hosting are, due to those hosting are based in an opensource server as Apache (Linux/Debian). For a good .htaccess and .htpasswd generator visit Dynamic Drive website, I’ve been using it for years. This option just add a certain security shield, but because you don’t have access to the root folder you need to put this files together inside the folder you want to protect, unless your hosting service at least provide you with your root system URL, allowing you to target another folder other than the same folder your storing the .htaccess file. I will explain this in more details in another tutorial.

Use Protected Static Variable More Often

The variables you use in your class should be planned carefully, which variables are going to be exposed? (public variable), which ones are going to have limited access? (protected or private variable), and which ones should not be visible only by the app? (protected static variables).

More Secure PHP Class 003

In the illustration above I set up two protected variables then see how they are exposed when you call the object and echo it:

More Secure PHP Class 004

It is obvious that this is not a very effective way to save sensitive information, so we need to get some other measures. This measures are simple but effective in most cases. Let’s modify a little bit our code:

Setting up a static secured variableNow that we have our first protected static variable let see how it is exposed:

The static protected variable is not exposed!

And as you can see this type of variable is not visible, and can only be used inside the class. But let’s try to echo this value to see what happens:

Trying to echo protected static values

We have added an echo to our code, but this is the result:

An Internal Server Error is thrown by the browser

Obviously PHP will throw an error because protected methods cannot be used outside the class. But let’s set an ini_set() to display the errors, to see what type of error is PHP throwing:

Exposing the errors

I added ini_set(‘display_errors’,’On’) to change this variable in the php.ini file, this way we can see the error as in displayed by PHP. Let’s see the error:

The returned error

As you can see, we cannot used protected methods outside the class, because they are not exposed. They just hold data to be used by the class, you can use this data to set a value of a public variable if you like, this if you want to exposed this variable outside the box:

Exposing static values using public variables

Then I can use this public variable to echo the value of the static variable. Probably this will never happen but is just an example how to exposed this values if you need to. But before we need to remove lines 34 and 35 so the previous error is not shown. After doing that we can see the result.

Exposing a static value with a public method

With the result, let’s move along. Not only variables can be static. One thing you need to understand is that static objects inside your class cannot be manipulated by public functions. To accomplish this outside the function you need to create a public function that execute a static protected function. As I said, you probably will never want to do that, but it is the same principle to manipulate static data inside the class… so let’s get our hands dirty:

Manipulating variables outside the class

In the code above I’m manipulating the value of the public variable $message to hold the same value of the constant PHPCONSTANT, when I run the code this value is exposed by the $message variable.

The $message variable exposing constant value

Now, if we trying to do this with the static variable we going to have an error like before, because we are trying to use a protected object outside the class… not good!

Trying to use protected method

Throwing another error

Manipulating Static Data

If you really need to use or manipulate an static outside the class, which makes no sense, then your will have to make a clever code able to manipulate these values, but because the values are set outside the class you should always protect your application from injection attempts, here I will only use htmlspecialchars() function, that might stop some Cross Scripting but you need something more clever than that, believe me.

Setting up functions

In this code I created 3 new functions, changeStatic() is the actual function that will update the value of the static variable, as you see I’m using the dynamic variable method that can be accomplished like:

${$variable} = $value;

The same method you use to create a PHP variable, starting with the dollar sign ($), then you will enclosed a variable which hold a value that it is the name of the variable to be set is curly brackets ({ }), I will be setting up the $password static variable. The dynamic variable will be set by the $name variable in the function and the $value variable obviously will hold the new value to be set.

The setStatic() function then is the one that’s going to be executed outside the class, as you see I filtering the $name and the $value variables in this function. As I said, this is a lousy method to protect your variable but will clean any html tags that are sent through the function. For a more advanced method to process exposed I/O data check this whitepaper from Symantec.

Then I created also another public function that will pass the new $password value set by these two function to the public $message variable, called showStatic(). With all this done, then we can use the public functions outside the class to set internal values.

Calling method outside the class

When executing the function, we are setting the $name variable with a string value of password and the $value variable with another string value of 2535. Then we are going to execute the second public function showStatic() where we set the $name value with static variable name that we want to expose, in this case ‘password’. This is the result.

Exposing new value

As you can see, the phpClass Object is not exposing any of those values, the static methods and variables can only be executed inside the class if they are protected and can only be exposed if you allow it. This gives you more control of your variables and allow your to develop more secure pieces of reusable codes and objects with PHP.

Resources: In this tutorial I’m using WebMatrix the free web developing tool from Microsoft as my IDE and IE 10 for browser.

Happy Coding!