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.