How to add Alphabetical pagination on the page

Question

I'm new to php & developing a page where i want to fetch data from db & the pagination should be in alphabetical format. i.e A|B|C| .... On click of A it should display names starting with A only. Currently i'm just using LIMIT function. Please Help !

<?php
     $host="localhost"; // Host name 
     $username="root"; // Mysql username 
     $password=""; // Mysql password 
     $db_name="testmra"; // Database name  
     // Connect to server and select databse.
     $conn=mysqli_connect($host,$username,$password) or die("cannot connect"); 
     mysqli_select_db($conn,$db_name);
     $sql = "SELECT * FROM newuser WHERE role='User' ORDER BY name asc LIMIT 15";
     $query = mysqli_query($conn,$sql);

     echo "<table border='1' id='mytable' width='100%'>
         <tr><th colspan='9' align='center'><h2>User Details</h2></th></tr>
         <tr bgcolor='grey'>
             <th width='25%'>Full Name</th>
             <th width='25%'>Department</th>
             <th width='25%'>EmailId</th>
             <th width='25%'>Username</th>
         </tr>";
        while($row = mysqli_fetch_array($query))
        {
          echo "<tr>";
          echo "<td align='center'>" . $row['name']. "</td>";
          echo "<td align='center'>" . $row['department']. "</td>";
          echo "<td align='center'>" . $row['emailid'] . "</td>";
          echo "<td align='center'>" . $row['username'] . "</td>";
          echo "</tr>";
        }
     echo "</table>";
     mysqli_close($conn);   
?>

I didn't downvote you, but I am tempted to do this because you omitted so much critical information needed to give a solid answer. — Tim Biegeleisen, Jul 15 '15 at 05:18
You omitted any information about the `newuser` table, including which column needs to be sorted alphabetically, for starters. — Tim Biegeleisen, Jul 15 '15 at 05:22

Shashikant Chauhan · Accepted Answer · 2015-07-15T06:30:33.653

1

    <?php
     $host="localhost"; // Host name 
     $username="root"; // Mysql username 
     $password=""; // Mysql password 
     $db_name="testmra"; // Database name  
     // Connect to server and select databse.

     $conn=mysqli_connect($host,$username,$password) or die("cannot connect"); 
     mysqli_select_db($conn,$db_name);
     if(isset($_GET['get'])){
       $get = mysqli_real_escape_string($_GET['get']);
     }else{
      $get = '';
     }
     $sql = "SELECT * FROM newuser WHERE role='User' AND name LIKE '$get%' ORDER BY name asc LIMIT 15";
     $query = mysqli_query($conn,$sql);

     echo "<table border='1' id='mytable' width='100%'>
         <tr><th colspan='9' align='center'><h2>User Details</h2></th></tr>
         <tr bgcolor='grey'>
             <th width='25%'>Full Name</th>
             <th width='25%'>Department</th>
             <th width='25%'>EmailId</th>
             <th width='25%'>Username</th>
         </tr>";
        while($row = mysqli_fetch_array($query))
        {
          echo "<tr>";
          echo "<td align='center'>" . $row['name']. "</td>";
          echo "<td align='center'>" . $row['department']. "</td>";
          echo "<td align='center'>" . $row['emailid'] . "</td>";
          echo "<td align='center'>" . $row['username'] . "</td>";
          echo "</tr>";
        }
     echo "</table>";
     mysqli_close($conn);   
?>
<?php
$alphas = range('A', 'Z');
 foreach ($alphas as $key) {?>
<a href="answer.php?get=<?php  echo $key  ?>"><?php echo $key ?></a>
<?php }?>

using this you will get every alphabets so call it using ajax and you will get your alphabets using get method. Make new page answer.php for this code.

edited Jul 15 '15 at 06:30

answered Jul 15 '15 at 05:25

Shashikant Chauhan

444
4
13

Sir i just edited the code & it's working fine ! Thank You – Fresher Jul 15 '15 at 05:55
Sir just 1 question i.e How can i just call data starting with A whenever page is called ? Because right now it's showing all the entries from the database when the page is called – Fresher Jul 15 '15 at 06:11
Please check it your query in MYSQL "SELECT * FROM newuser WHERE role='User' AND name LIKE 'A%' ORDER BY name asc LIMIT 15" ; You will get more detail here for like statement for mysql http://www.tutorialspoint.com/mysql/mysql-like-clause.htm – Shashikant Chauhan Jul 15 '15 at 06:12
Sir LIMIT will display first 15 rows from db & it's displaying data starting with B & c as well – Fresher Jul 15 '15 at 06:15
LIKE 'A%' is only displaying values with A on click of any other alphabet – Fresher Jul 15 '15 at 06:16
are you getting $Get value in sql or not? – Shashikant Chauhan Jul 15 '15 at 06:23
$get = mysqli_real_escape_string($_GET['get']); I forget to write underscore. – Shashikant Chauhan Jul 15 '15 at 06:47
Sir i edited that long time back. I want to display names starting with A when the page is load & then display names according to the alphabets selected – Fresher Jul 15 '15 at 06:53
can we get any solution for my query regarding the loading of page & other alphabet ? – Fresher Jul 15 '15 at 07:28
you can call it using ajax. – Shashikant Chauhan Jul 15 '15 at 07:44
How can i do it using AJAX ? – Fresher Jul 15 '15 at 08:28

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

I wanted to add this as a comment to the accepted answer but I don't have enough reputation to do so yet, sorry!

The answer is good, but I would strongly discourage placing variables inside a SQL statement, despite escaping the string before inserting it, it's still a potential risk.

StackOverflow: Preventing SQL injection

The above link explains in excellent detail how to deal with this matter properly. A very worthwhile read for anyone involved in SQL at any level.

edited May 23 '17 at 10:30

Community

1
1

answered Jul 15 '15 at 06:48

Darren H

910
8
24

Can you check correctly? i used mysqli_real_escape_string. so its prevent mysql injections. – Shashikant Chauhan Jul 15 '15 at 07:07
[Why prepared statements are better than escaping](http://stackoverflow.com/questions/4771984/are-dynamic-mysql-queries-with-sql-escaping-just-as-secure-as-prepared-statements) – Darren H Jul 15 '15 at 07:14
Ankit, I'd recommend leaving Shashikant's answer as the accepted answer as it is a full working solution. Mine is just a security ammendment. An up vote would be nice if you feel it is useful but it is not a full answer so should not be accepted – Darren H Jul 15 '15 at 07:21
yes i know that @darren thanks for suggesting issues about sql injections. – Shashikant Chauhan Jul 15 '15 at 07:22
@DarrenH vote up is not possible because i don't have enough reputation. – Fresher Jul 15 '15 at 07:26

How to add Alphabetical pagination on the page

2 Answers2