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!

The Best Free Design Software in the Web.

Start designing can be a little hassle, specially for students. Of course, the cost for an Art certifications are way over prized in the US. For example, an Associate Degree in Full Sail, Florida – Maybe the best option if you want to have hands on into new technology, can cost $80,000.00. If you don’t find a job among the thousands of students that graduate from any Art division you will start your career with an outstanding monthly bill, or a little monthly bill with no income. If you are lucky to find a job after you graduate don’t even think to pay the minimum they suggest.

Anyway, that’s another story. But if you are desiring to start designing and developing your skills but you don’t have over $2,000.00 to buy an Adobe Creative Suite, then you don’t have to quit or be disappointed. There’s a lot a free software out there for any design skill you have.

I will start with the one I fall in love with… Inkscape.

Inkscape (Windows and Debian – Linux)

Inkscape

Inkscape is the free version of the so famous Adobe® Illustrator or the old Macromedia® Freehand, and can be compared with Corel Draw. This Vector Image editor is so powerful that counts with tools that Illustrator lacks. The ability to create degradations to transparency without the needs to specify a masking is a boom. Hey… For a free software this is more that you can expect. Has everything you need to start designing like the professionals. Best to design icons, logos and web images. It natively saves in SVG format. What this means? That your images can be viewed in a web browser, no more cranky pixelated images. At the moment, there is no version for Mac. But if you have the money to buy a Mac then you probably have the money to get Corel Draw ($400 up) or Adobe® Illustrator ($500 up).

Gimp2 (Win, Mac and Debian – Linux)

Gimp2

Gimp has been awarded many times like the best Image Manipulation Software available as open source. Has been around for many years and has implemented many tools over they years that make it a very competitive options against it main rival Adobe® Photoshop. Though it lacks of many amazing tools available in the expensive counterpart, like 3D tools and 3D rendering, Gimp2 is far more advance that many other commercial Pixel CAD in the industry. With a lot of plug-ins available for it you can achieve almost all of the most needed task for Professional Offset or Digital Printing. Also, one tool that makes Gimp2 unique is the capability to create seamless texture for 3D images and icon creations with just few clicks. Something that’s not available for Photoshop out of the Box.

Blender (Win, Mac and Debian – Linux)

Blender

Even though Gimp2 does not count with the integration of 3D tools as Photoshop, these tools are available with the really state of the art 3D Design CAD Blender. As with Inkscape and Gimp2, Blender is a powerful open source software that has all the tools you need to create really advance 3D still images as a full length 3D movie. Tools like live rendering, texture manipulation, vector capabilities and node effects, Blender is definitely a most have software. Downside, is really complicated to learn but as with Inkscape and Gimp2, there’s a large community of users always willing to help, well, not all of them are so graceful, but at the end, they help. One site that you really need to subscribe to, if you are ready to get your hand dirty into 3D design with blender is http://blenderguru.com from Andrew Price

Now that you have cover all the graphic needs, then you need way to publish them. Either in the web or on paper, there are many free software to choose from.

Desktop and Web Development

To develop for web there are many software you can use, even with a simple text editor like notepad (Windows) or text edit (Ubuntu) you might want to have the power of code coloring.

Notepad2 (Windows)

Probably the first option in a long list, is simple, easy to use and have code coloring for many file extensions like: HTML, JavaScript, CSS, PHP, SQL, Python and more. Only for Windows Environment.

Notepad++ (Windows)

Notepad++

Based in the same source of Notepad2 but with a lot of plug-ins and ad-ons. Is a little more advanced than Notepad2 but only available for Windows Environment.

Microsoft WebMatrix (Windows)

Microsoft WebMatrix

Amazing but true, a free web development tool from Microsoft, WebMatrix is probably one of the best options for web developing with support for MySQL and MSSQL as well, code coloring and code hints. Has support for the .NET environment languages as well as PHP open source language.

Microsoft Visual Studio Express for Web (Windows)

Visual Studio Express for Web

A more powerful tool than WebMatrix but does not comes with support for PHP natively, you might be able to implement the support with a plug-in, I’m not so sure, but it has all you need to develop HTML5, JavaScript and CSS website.

Web developers want to create sites that are interesting, attractive, and interactive. Visual Studio Express 2012 for Web makes web development accessible to any developer. It provides the tools and resources to build and test HTML5, CSS3, and JavaScript code, and to deploy on web servers or to the cloud with Windows Azure. This will get you Visual Studio Express 2012 for Web, ASP.NET MVC 3, ASP.NET MVC 4, IIS Express 8.0, Web Deploy v3, and a lot of other new features. Tell your friends! Note: Installing this product usually downloads between 450-550MB and will take 15-45 minutes to install depending on your machine configuration. ~ Microsoft.com/web

NetBeans IDE (Windows, Mac and Debian – Linux)

Netbeans IDE

The best bet to develop to for C++, Java and PHP will be Netbeans IDE, a powerful software that now includes full support for HTML5, is even integrated with the Chrome Browser where you can test your web application in different formats like Desktop, Mobile and Tablets. And as you see is available for all main platforms.

MonoDevelop IDE (Windows, Mac and Debian – Linux)

MonoDevelop

If you are a Mac developer that wanting to target the 80% of Windows user, MonoDevelop is a powerful IDE that allow you to develop .NET application in any main platform. ASP.NET, C#, C++ and many other languages only supported in the Windows .NET Environment are available to you with this free open source project.

MonoDevelop is an IDE primarily designed for C# and other .NET languages. MonoDevelop enables developers to quickly write desktop and ASP.NET Web applications on Linux, Windows and Mac OSX. MonoDevelop makes it easy for developers to port .NET applications created with Visual Studio to Linux and to maintain a single code base for all platforms. ~ MonoDevelop.com

Office Software

This is not the end of the line. You might want to publish your projects as PDF formats or as Microsoft® Word Documents you will need to count with an Office Software family. For the last 15 years Open Office has been the free solution to MS Office or Mac iWork.

Apache Open Office (Windows, Mac and Debian – Linux)

Apache Open Office

This software has been handed over through many distributors, originally from the former Sun Microsystems, then Oracle and finally Apache. You can get this amazing group of tools from the Apache Foundation Website. The software is develop by volunteers. That’s why this project is completely free.

Apache OpenOffice is developed 100% by volunteers. Apache does not pay for developers, for translators, for QA, for marketing, for UI, for support, etc. Of course, we’re happy to accept donations to the Apache Software Foundation, to keep our servers runnings and for similar ovehead expenses. But our products are developed entirely by volunteers. ~ OpenOffice.org

This project includes software to create Spreadsheets, Documents, Databases, Vector Images and more.

One Last Thing

The only left here is your imagination. As you see these are a few in a vast catalog of free software. These are the ones that has last for years, all of them has been tested by Designer’s Gate, and most of them are still in my computer because they count with some tools that other most expensive version lack of. Some of them uses Java platform if you are concern with that then this should be one thing you have to consider. Other than that, these tools will allow you to start your career with a push of a very competitive free toolbox so you can start making a little money to pay off your already initiated credit report.

Hope all these tool help developing your imagination… Happy Coding!