Thursday 13 October 2016

How to Make Alphabetic Pagination in PHP with Mysql



Hello friends in this tutorial we are going to discuss how can we create alphabetic pagination or sorting of data by using php script with mysql database. If you are looking for alphabetical pagination tutorial in php programming language then it is the best tutorial for learning this type of things. Alphabetic Pagination means we can filter data based on alphabetical character. By using Alphabetic pagination we can quick way to filter data from mysql table by using click on alphabet link and by clicking on that alphabet we can get filter data that start with that alphabet. For alphabet pagination first we have to define table column, based on that table column data will be filtered. Suppose in we have define student name table column, then when we have click on a alphabet link then we can get student data whose name start with a alphabet. This way we can easily filter data based on alphabet data. Here when we have click on any alphabetical pagination link then url redirect to index page with one url variable and from that variable we have filter mysql data based on that url variable and we have load that filter data under html table. This we can easily filter records based on it's alphabetical order. An alphabetical pagination also an index page of any bunch of records. So we can get any records based on it's alphabetical order. The main advantage of alphabetical pagination we can easily filter large amount of data based on it's alphabetical order. So friends this is my tutorial on how to create alphabetical pagination in pagination script with mysql database.



Source Code


index.php


 <?php  
 $connect = mysqli_connect('localhost', 'root', '', 'testing');  
 $char = '';  
 if(isset($_GET["char"]))  
 {  
      $char = $_GET["char"];  
      $char = preg_replace('#[^a-z]#i', '', $char);  
      $query = "SELECT * FROM tbl_student WHERE student_name LIKE '$char%'";  
 }  
 else  
 {  
      $query = "SELECT * FROM tbl_student ORDER BY student_id";  
 }  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | How to Create Alphabetic Pagination in PHP with Mysql</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container" style="width:1100px;">  
                <h2 align="center">How to Create Alphabetic Pagination in PHP with Mysql</h2>  
                <h3 align="center">Student Data</h3>                 
                <br /><br />  
                <div class="table-responsive">  
                     <div align="center">  
                     <?php  
                          $character = range('A', 'Z');  
                          echo '<ul class="pagination">';  
                          foreach($character as $alphabet)  
                          {  
                               echo '<li><a href="index.php?character='.$alphabet.'">'.$alphabet.'</a></li>';  
                          }  
                          echo '</ul>';  
                     ?>  
                     </div>  
                     <table class="table table-bordered">  
                          <tr>  
                               <th width="20%">ID</th>  
                               <th width="50%">Student Name</th>  
                               <th width="30%">Student Phone</th>  
                          </tr>  
                          <?php  
                          if(mysqli_num_rows($result) > 0)  
                          {  
                               while($row = mysqli_fetch_array($result))  
                               {  
                          ?>  
                          <tr>  
                               <td><?php echo $row["student_id"]; ?></td>  
                               <td><?php echo $row["student_name"]; ?></td>  
                               <td><?php echo $row["student_phone"]; ?></td>  
                          </tr>  
                          <?php  
                               }  
                          }  
                          else  
                          {  
                          ?>  
                          <tr>  
                               <td colspan="3" align="center">Data not Found</td>  
                          </tr>  
                          <?php  
                          }  
                          ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 </html>  

Database - tbl_student


 --  
 -- Table structure for table `tbl_student`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_student` (  
  `student_id` int(11) NOT NULL AUTO_INCREMENT,  
  `student_name` varchar(250) NOT NULL,  
  `student_phone` varchar(20) NOT NULL,  
  PRIMARY KEY (`student_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;  
 --  
 -- Dumping data for table `tbl_student`  
 --  
 INSERT INTO `tbl_student` (`student_id`, `student_name`, `student_phone`) VALUES  
 (1, 'Pauline S. Rich', '412-735-0224'),  
 (2, 'Sarah C. White', '320-552-9961'),  
 (3, 'Samuel L. Leslie', '201-324-8264'),  
 (4, 'Norma R. Manly', '478-322-4715'),  
 (5, 'Kimberly R. Castro', '479-966-6788'),  
 (6, 'Elaine R. Davis', '701-685-8912'),  
 (7, 'Concepcion S. Gardner', '607-829-8758'),  
 (8, 'Patricia J. White', '803-789-0429'),  
 (9, 'Michael M. Bothwell', '214-585-0737'),  
 (10, 'Ronald C. Vansickle', '630-571-4107'),  
 (11, 'Clarence A. Rich', '904-459-3747'),  
 (12, 'Elizabeth W. Peterson', '404-380-9481'),  
 (13, 'Renee R. Hewitt', '323-350-4973'),  
 (14, 'John K. Love', '337-229-1983'),  
 (15, 'Teresa J. Rincon', '216-394-6894'),  
 (16, 'Erin S. Huckaby', '503-284-8652'),  
 (17, 'Brian A. Handley', '989-304-7122'),  
 (18, 'Michelle A. Polk', '540-232-0351'),  
 (19, 'Wanda M. Brown', '718-262-7466'),  
 (20, 'Phillip A. Hatcher', '407-492-5727'),  
 (21, 'Dennis J. Terrell', '903-863-5810'),  
 (22, 'Britney F. Johnson', '972-421-6933'),  
 (23, 'Rachelle J. Martin', '920-397-4224'),  
 (24, 'Leila E. Ledoux', '615-425-9930'),  
 (25, 'Darrell A. Fields', '708-887-1913'),  
 (26, 'Linda D. Carter', '909-386-7998'),  
 (27, 'Melva J. Palmisano', '630-643-8763'),  
 (28, 'Jessica V. Windham', '513-807-9224'),  
 (29, 'Karen T. Martin', '847-385-1621'),  
 (30, 'Jack K. McDonough', '561-641-4509'),  
 (31, 'John M. Williams', '508-269-9346'),  
 (32, 'Amelia W. Davis', '347-537-8052'),  
 (33, 'Gertrude W. Lawrence', '510-702-7415'),  
 (34, 'Michael L. Harris', '252-219-4076'),  
 (35, 'Casey A. Groves', '810-334-9674'),  
 (36, 'James H. Wilson', '865-259-6772'),  
 (37, 'James A. Wesley', '443-217-1859'),  
 (38, 'Armando C. Gay', '716-252-9230'),  
 (39, 'James M. Duarte', '402-840-0541'),  
 (40, 'Jason E. West', '360-610-7730'),  
 (41, 'Gloria H. Saucedo', '205-861-3306'),  
 (42, 'Paul T. Moody', '914-683-4994'),  
 (43, 'Sandra L. Williams', '310-335-1336'),  
 (44, 'Elaine T. Deville', '626-513-8306'),  
 (45, 'Robyn L. Spangler', '754-224-7023'),  
 (46, 'Sam A. Pino', '806-823-5344'),  
 (47, 'Joseph H. Marble', '201-917-2804'),  
 (48, 'Mark M. Bassett', '206-592-4665'),  
 (49, 'Edgar M. Billy', '978-365-0324'),  
 (50, 'Connie M. Yang', '815-288-5435');  

11 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Perfect!

    I think Alphabetic Pagination plugin has been developed with the same concept. It's a great contribution to opensource community.

    https://wordpress.org/plugins/alphabetic-pagination/

    ReplyDelete
  3. Alphabetic Pagination Plugin using this technique perfectly.
    https://wordpress.org/plugins/alphabetic-pagination/

    ReplyDelete
  4. what if data are too long and want to display them by pages like in https://wpvulndb.com/themes

    ReplyDelete
  5. Very efficient for database with less than 100 people
    With hundreds of data, Ajax with PHP using text input is better and would produce more direct results. I just discovered this site and you're pretty amazing , thanks for your effort. The voice doesnt matter.

    ReplyDelete
  6. not running because of $_GET["char"] replace both $_GET["char"] on top with $_GET["character"] then its running so i waste 30 minutes for it :/

    ReplyDelete