This tutorial is aimed to help you how to make an alphabetical search using MySQL. Many sites use it these day and we will show you how do they do it.

Let’s suppose you have a business directory and you want to display the listings that are starting with a specific letter or a non-letter character (numeric, etc).

Let’s start with the non-alphabetic selection in case you need to select listings starting with a number or a non-alphanumeric character. Here’s the command:

SELECT * FROM `listings` WHERE Left(listing_name, 1) REGEXP '^[^a-z]+$';

The regular expressions are used here. The MySQL command selects all listings that have a first non-alphabetical character.

Now let’s move on with the alphabetical selection.

We will choose the letter ‘A’ for our example. Here’s the command that will select all listings starting with ‘A’.

SELECT * FROM `listings` WHERE Left(listing_name, 1) = 'a'; -- case insensitive

You can replace ‘A’ with any letter you wish.

Congratulations! Now you know how to perform an alphabetical search with MySQL.

Hello,

This function is useful to highlight words from a simple non-html text. For instance, the search results from your site can be highlighted.

<?php
// Credits: http://www.bitrepository.com/
function hightlight($str, $keywords = '')
{
$keywords = preg_replace('/\s\s+/', ' ', strip_tags(trim($keywords))); // filter

$style = 'highlight';
$style_i = 'highlight_important';

/* Apply Style */

$var = '';

foreach(explode(' ', $keywords) as $keyword)
{
$replacement = "<span class='".$style."'>".$keyword."</span>";
$var .= $replacement." ";

$str = str_ireplace($keyword, $replacement, $str);
}

/* Apply Important Style */

$str = str_ireplace(rtrim($var), "<span class='".$style_i."'>".$keywords."</span>", $str);

return $str;
}

Don’t forget to define your style in the same file or in a separate one (recommended)!

.highlight
{
background: #CEDAEB;
}

.highlight_important
{
background: #F8DCB8;
}

Usage Example:

$str = 'The PHP development team would like to announce the immediate availability of a new PHP version.
The PDT project provides a PHP Development Tools framework for the Eclipse platform.
PHP is especially suited for Web development and can be embedded into HTML.
There are many development tools for PHP.';

$keywords = 'PHP development'; 

// tags from $keywords are automatically stripped
$string = hightlight($str, $keywords); 

echo $string;
?>

As you can see the full string is highlighted with a different color than the words containing it.

Here’s how our example will look like:

IMPORTANT: This function only works for clean text, without any HTML in it (tags, escaping characters).

Your comments and suggestions are welcomed. Feel free to post them!
Good luck!