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.

Demo Download

Table structure
Configuration
HTML and PHP
CSS
Conclusion

1. Table structure

I am using users table.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

Completed Code

<?php

$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">
 <?php
 include("config.php");
 ?>

 <!-- Alphabets Sort -->
 <ul class="sort">
 
 <?php
 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>';
 }
 ?>
 </ul><br><br>

 <table width="100%" id="userstable" border="1" >
  <tr class="tr_header">
   <th>S.no</th>
   <th>Username</th>
   <th>Name</th>
   <th>Email</th>
  </tr>
  <?php

  // 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'];
  ?>
  <tr>
   <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>
  </tr>
  <?php
   $sno ++;
  }
 ?>
 </table>

</div>

4. CSS

#userstable{
 border-collapse: collapse;
 max-width: 800px;
}

/* Numeric */
.sort{
 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:black;
}

.active{
 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.

Contents

1. Table structure

2. Configuration

3. HTML and PHP

4. CSS

5. Conclusion