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


Contents

  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. CSS
  5. 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.

Related Post

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  

One Comment

  1. Ismail said:

    Thanks for the script. I love it 🙂

    October 15, 2017
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *