Create alphabetical pagination with PHP MySQL

The alphabetical pagination searches the records according to the first character in a specific column.

You can either manually fix the characters from A-Z or use the database table column value to create the list.

In this demonstration, I list all available character from a field in the Table and show the total number of records with it. Filter the record whenever a character is being pressed from the list.

Create alphabetical pagination with PHP MySQL


  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. CSS
  5. Conclusion

1. Table structure

I am using users table.

CREATE TABLE `users` (
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL

2. Configuration

Create a new config.php file.

Completed Code


$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());

3. HTML and PHP

Show alphabetical list and whenever the user clicked on it then filter the record.

  • Create list of alphabets

Select the first character from the name column in the users Table and count total records on a character.

On the bases of the result create <li> elements and add <a > where use firstCharacter to create source.

  • Alphabetical search

Use $_GET['char'] value to create the SELECT query and list records.

Completed Code

<div id="content">

 <!-- Alphabets Sort -->
 <ul class="sort">
 echo '<li ><a href="index.php" '; 
 if( !isset($_GET['char']) ){
  echo ' class="active" ';
 echo ' >All</a></li>';

 // Select Alphabets and total records
 $alpha_sql = "select DISTINCT LEFT(name , 1) as firstCharacter,
  ( select count(*) from users where LEFT(name , 1)= firstCharacter ) AS counter 
  from users 
  order by name asc";
 $result_alpha = mysqli_query($con,$alpha_sql);

 while($row_alpha = mysqli_fetch_array($result_alpha) ){
  $firstCharacter = $row_alpha['firstCharacter'];
  $counter = $row_alpha['counter'];
  echo '<li ><a href="?char='.$firstCharacter.'" '; 
  if( isset($_GET['char']) && $firstCharacter == $_GET['char'] ){
   echo ' class="active" ';
  echo ' >'.$firstCharacter.' ('.$counter.')</a></li>';

 <table width="100%" id="userstable" border="1" >
  <tr class="tr_header">

  // selecting rows
  $sql = "SELECT * FROM users where 1"; 
  if( isset($_GET['char']) ){
   $sql .= " and LEFT(name,1)='".$_GET['char']."' ";
  $sql .=" ORDER BY name ASC";
  $result = mysqli_query($con,$sql);

  $sno = 1;
  while($fetch = mysqli_fetch_array($result)){
   $name = $fetch['name'];
   $username = $fetch['username'];
   $email = $fetch['email'];
   <td align='center'><?php echo $sno; ?></td>
   <td align='center'><?php echo $username; ?></td>
   <td align='center'><?php echo $name; ?></td>
   <td align='center'><?php echo $email; ?></td>
   $sno ++;


4. CSS

 border-collapse: collapse;
 max-width: 800px;

/* Numeric */
 list-style: none;
.sort li{
 float: left;
 border: 1px solid #000;
 padding: 5px 7px;
 margin-right: 10px;
 border-radius: 3px;
.sort li a{
 text-decoration: none;

 color: red !important;

5. Conclusion

The user can browse the records according to the first character within the specific column.

You don’t need to add A-Z characters manually the script will get the available characters from the database table and count total records available on that character.