CODEWITHSUNDEEP
phpmysql
Fresher
Fresher

Reputation: 23

How to add Alphabetical pagination on the page

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);   
?>

Upvotes: 0

Views: 1801

Answers (2)

Darren H
Darren H

Reputation: 902

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.

Upvotes: 1

Shashikant Chauhan
Shashikant Chauhan

Reputation: 444

    <?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.

Upvotes: 1

Related Questions