PHP/MySQL List By Alphabet

This simple tutorials will show you how to create a alphabet index to grabs data from a mysql database.
Let's go on...

1. The alphabet index - how to create a cool one?

The first thing is list character from a to
z. How to do this? In a I've read, the author did it by doing a loop like

for ($i=$start ;$i<=$end; $i++);

Where $start and $end is the ASCII code of a
and z. However, It's sometimes quite hard to remember this value. Actually, I
cannot at the moment ;) So I solve the problem like this:

$list[]='0-9';
for ($ch=ord('a');$ch<=ord('z');$ch++)
{
$list[]=chr($ch);
}

Now that we have the list for the index. The next step is
simple. You will define the number of items per row, for example:

define('num_per_row','6');

The open a table, lop through the character set.
Check if an item is the first cell -> open the row, or the last cell -> close a
row:

$num_rows=count($list);
define ("TB_COLOR_LIGHT","#EEF7FA");
define ("TB_COLOR_DARK","#DBE9EF");
$width=(100/num_per_row).'%';
echo '<table bgcolor="'.TB_COLOR_DARK.'" width="100%">';
for ($i=0;$i<$num_rows;$i++)
{
if (0==$i%num_per_row)
{
echo '<tr align="center" valign="middle">'; // open the row
}
$url=append_sid('your_result_page&type=abc&keys='.$list[$i]);
echo '<td bgcolor="'.TB_COLOR_LIGHT.'" height="20" width="'.$width.'" nowrap>';
if (is_song_abc($list[$i])) // show only necessary items, see bellow
{
echo'<a href="'.$url.'"><b>'.$list[$i].'</b></a></td>';
}
else
{
echo $list[$i];
}
echo '</td>';
if ((num_per_row-1)==$i%num_per_row) // close the row
{
echo '</tr>';
}
}
echo '</table>';

2. The search code

The function is_abc($item) is to check if there is result which begin
with that item, for example, we search song_name from table TB_SONGS

function is_song_abc($keywords)
{
$where="song_name REGEXP '^[$keywords]'";
$sql ='SELECT count(*) as total
FROM '.TB_SONGS."
WHERE $where";
$result = sql_query($sql);
return mysql_result($result,'total');
}

The search code is totally similiar, you can
select what ever you want from the database with the WHERE condition is:

$where="song_name REGEXP '^[$keywords]'";

3. Conclusion

This tutorial is simple, yet useful to who new to mysql database search. In the next article, I intend to discuss more about mysql database search problem and some solution.