The autocomplete functionality shows the suggestion list according to the entered value. The user can select a value from the list.
With jQuery UI you can easily add autocomplete widget to the input element. Navigate to the suggestion list either by mouse or keyboard arrow keys.
It has the various options that allow customizing the widget.
You can fix the suggestion source value while initializing in this case whenever the user typed any character then it searches the value within the given source value or you can make it dynamic with AJAX.
In this tutorial, I show how you can add jQuery UI autocomplete on the textbox and select single or multiple values from the suggestion list.

1. Table structure

I am using users table in the example.

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

2. Configuration

Create a new config.php file.

<?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. Download and Include

• Download jQuery and jQuery UI libraries.
• With jQuery and include jQuery UI library script and CSS files in the <head> section or end of </body>.

 <!-- Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

<!-- jQuery UI -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

4. HTML

Created 4 input boxes –

• The first textbox to add autocomplete widget.
• The second is used to show selected user id from the suggestions list.
• The third textbox to add multiple autocomplete.
• The forth textbox to show selected multiple user ids.

Completed Code

<table>
  <tr>
    <td>Single selection</td>
    <td><input type='text' id='autocomplete' ></td>
  </tr>

  <tr>
    <td>Selected User id</td>
    <td><input type='text' id='selectuser_id' /></td>
  </tr>

  <tr>
    <td>Multiple Selection</td>
    <td><input type='text' id='multi_autocomplete' ></td>
  </tr>

  <tr>
    <td>Selected User ids</td>
    <td><input type='text' id='selectuser_ids' /></td>
  </tr>

</table>

5. PHP

Create a new fetchData.php file.
On the basis of POST search value fetch records from the users Table. Declare an Array variable $response and initialize it with an associative array where pass the  $row['id'] in value key and  $row['name'] in label key.
Completed Code

<?php

include "config.php";

if(isset($_POST['search'])){
 $search = $_POST['search'];

 $query = "SELECT * FROM users WHERE name like'%".$search."%'";
 $result = mysqli_query($con,$query);

 $response = array();
 while($row = mysqli_fetch_array($result) ){
   $response[] = array("value"=>$row['id'],"label"=>$row['name']);
 }

 echo json_encode($response);
}

exit;

NOTE – Remove value key from the array if you only want to work it name e.g. $response[] = array("label"=>$row['name']);

---

6. jQuery

Initialize
Create 2 functions

• split() – Return an array by splitting the value.
• extractLast() – Select the last value from array.

Fetch source
Item Selection
Completed Code

$( function() {

 // Single Select
 $( "#autocomplete" ).autocomplete({
  source: function( request, response ) {
   // Fetch data
   $.ajax({
    url: "fetchData.php",
    type: 'post',
    dataType: "json",
    data: {
     search: request.term
    },
    success: function( data ) {
     response( data );
    }
   });
  },
  select: function (event, ui) {
   // Set selection
   $('#autocomplete').val(ui.item.label); // display the selected text
   $('#selectuser_id').val(ui.item.value); // save selected id to input
   return false;
  }
 });

 // Multiple select
 $( "#multi_autocomplete" ).autocomplete({
    source: function( request, response ) {
                
      var searchText = extractLast(request.term);
      $.ajax({
         url: "fetchData.php",
         type: 'post',
         dataType: "json",
         data: {
           search: searchText
         },
         success: function( data ) {
           response( data );
         }
       });
    },
    select: function( event, ui ) {
        var terms = split( $('#multi_autocomplete').val() );
                
        terms.pop();
                
        terms.push( ui.item.label );
                
        terms.push( "" );
        $('#multi_autocomplete').val(terms.join( ", " ));

        // Id
        terms = split( $('#selectuser_ids').val() );
                
        terms.pop();
                
        terms.push( ui.item.value );
                
        terms.push( "" );
        $('#selectuser_ids').val(terms.join( ", " ));

        return false;
     }
           
 });

});
function split( val ) {
   return val.split( /,\s*/ );
}
function extractLast( term ) {
   return split( term ).pop();
}

NOTE – Remove select option if you have not return value key from the AJAX file.

8. Conclusion

The autocomplete widget gives suggestions to the user while searching value in the input element.
In the example, I customize the widget selection and store the selection value in the element for further processing.
You can learn more about this plugin from here.
If you found this tutorial helpful then don't forget to share.

The infinite page scroll you have seen on the social networking sites – Facebook, Twitter or on e-commerce websites where the new content is automatically loaded when scrolling down to the web page.
For implementing this functionality I am jQuery to detect page scroll when the user reaches the end of the web page then send an AJAX request to load new content.

1. Table structure

I am using posts table.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `link` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

<?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 & PHP

I set the number of rows to fetch at a time to 3 ($rowperpage=3).
Count the total number of rows and fetch first 3 records from MySQL Database table and create a layout.
Created two hidden fields –

• The <input id='row'> store current row position.
• The <input id='allcount'> store total number of rows.

Using both values when sending AJAX request from jQuery.
Completed Code

<div class="container">

<?php

 include "config.php";

 // Row per page
 $rowperpage = 3;

 // counting total number of posts
 $allcount_query = "SELECT count(*) as allcount FROM posts";
 $allcount_result = mysqli_query($con,$allcount_query);
 $allcount_fetch = mysqli_fetch_array($allcount_result);
 $allcount = $allcount_fetch['allcount'];

 // select first 3 posts
 $query = "select * from posts order by id asc limit 0,$rowperpage ";
 $result = mysqli_query($con,$query);

 while($row = mysqli_fetch_array($result)){

   $id = $row['id'];
   $title = $row['title'];
   $content = $row['description'];
   $shortcontent = substr($content, 0, 160)."...";
   $link = $row['link'];
 ?>

   <div class="post" id="post_<?php echo $id; ?>">
     <h1><?php echo $title; ?></h1>
     <p>
       <?php echo $shortcontent; ?>
     </p>
     <a href="'.$link.'" target="_blank" class="more">More</a>
   </div>

 <?php
 }
 ?>

 <input type="hidden" id="row" value="0">
 <input type="hidden" id="all" value="<?php echo $allcount; ?>">

</div>

4. PHP

Create a new fetch_data.php file. Using this file when sending AJAX request from jQuery.
From here fetch next 3 records from posts table according to $_POST['row'] value.
Return a HTML string.
Completed Code

<?php

// configuration
include 'config.php';

$row = $_POST['row'];
$rowperpage = 3;

// selecting posts
$query = 'SELECT * FROM posts limit '.$row.','.$rowperpage;

$result = mysqli_query($con,$query);

$html = '';

while($row = mysqli_fetch_array($result)){
  $id = $row['id'];
  $title = $row['title'];
  $content = $row['description'];
  $shortcontent = substr($content, 0, 160)."...";
  $link = $row['link'];
  // Creating HTML structure
  $html .= '<div id="post_'.$id.'" class="post">';
  $html .= '<h1>'.$title.'</h1>';
  $html .= '<p>'.$shortcontent.'</p>';
  $html .= '<a href="'.$link.'" target="_blank" class="more">More</a>';
  $html .= '</div>';

}

echo $html;

5. CSS

.container{
 width: 55%;
 margin: 0 auto;
 border: 0px solid black;
 padding: 10px 0px;
}

/* post */
.post{
 width: 97%;
 min-height: 200px;
 padding: 5px;
 border: 1px solid gray;
 margin-bottom: 15px;
}

.post h1{
 letter-spacing: 1px;
 font-weight: normal;
 font-family: sans-serif;
}


.post p{
 letter-spacing: 1px;
 text-overflow: ellipsis;
 line-height: 25px;
}

/* more link */
.more{
 color: blue;
 text-decoration: none;
 letter-spacing: 1px;
 font-size: 16px;
}

6. jQuery

Add scroll event handler on the window to detect page scroll.
For finding whether scrollbar reaches to bottom or not using following method return value –

• $(window).scrollTop() – This gives number of pixels the user has scroll the webpage.
• $(document).height() – Return height of browser viewport.
• $(window).height() – Return height of HTML document.

To get the number of pixels to the bottom subtract $(document).height() - $(window).height().

var position = $(window).scrollTop();
var bottom = $(document).height() - $(window).height();

If position == bottom then finds next row value and if it is not greater than allcount value then send AJAX request where pass row value as data and on successful callback append response.

Completed Code

$(document).ready(function(){
 
 $(window).scroll(function(){
 
  var position = $(window).scrollTop();
  var bottom = $(document).height() - $(window).height();

  if( position == bottom ){

   var row = Number($('#row').val());
   var allcount = Number($('#all').val());
   var rowperpage = 3;
   row = row + rowperpage;

   if(row <= allcount){
    $('#row').val(row);
    $.ajax({
     url: 'fetch_details.php',
     type: 'post',
     data: {row:row},
     success: function(response){
      $(".post:last").after(response).show().fadeIn("slow");
     }
    });
   }
  }

 });
 
});

8. Conclusion

This is another type of pagination where the user doesn’t have to press any key or a button to load new content.
It will automatically load up content whenever the user reaches the bottom of the web page while the page scroll.

If you found this tutorial helpful then don't forget to share.

If you know how to add or remove record to MySQL database table with jQuery AJAX then it’s been a lot easier for you to do with AngularJS.
Using PHP for handling requests and return a response.
In the demonstration, I create a form for entering new records and show the list of records in the table layout with a delete button.

1. Table structure

I am using users table in the tutorial example.

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

2. Configuration

Create a new config.php file.

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

Define ng-app='myapp' and ng-controller='userCtrl' on <div>.
Created two table layouts –

1. First, contains 3 input element with ng-model directive and a button with ng-click directive.
2. On the second layout, define ng-repeat directive on <tr> to show the list of users using AngularJS. Added Delete button following with user record and define ng-click="remove(index,userid.id)".

Completed Code

<div ng-app='myapp' ng-controller="userCtrl">
 <!-- New entry -->
 <table>
  <tr>
   <td>First Name</td>
   <td><input type='text' id='txt_fname' ng-model='fname'></td>
  </tr>
  <tr>
   <td>Last Name</td>
   <td><input type='text' id='txt_lname' ng-model='lname'></td>
  </tr>
  <tr>
   <td>Username</td>
   <td><input type='text' id='txt_uname' ng-model='uname'></td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type='button' id='but_save' value='Save' ng-click="add()" ></td>
  </tr>
 </table>

 <!-- User list -->
 <table border="1">
  <tr>
   <th>First name</th>
   <th>Last name</th>
   <th>Username</th>
   <th>&nbsp;</th>
  </tr>
 
  <tr ng-repeat="user in users">
   <td>{{user.fname}}</td>
   <td>{{user.lname}}</td>
   <td>{{user.username}}</td>
   <td><input type='button' ng-click='remove($index,user.id);' value='Delete'></td>
  </tr>
 
 </table>
</div>

4. PHP

Create a addremove.php file.
Read POST data and executing operations according to the $request_type value  –

• request_type = 1 (List) – Return all list of users in JSON format.
• request_type = 2 (Insert) – Check if the username already exists in the users table if not exists then insert a new record and return a JSON response.
• request_type = 3 (Delete)- Check if userid exists or not. If exists then delete a record from users table.

Completed code

<?php

include 'config.php';

$data = json_decode(file_get_contents("php://input"));

$request_type = $data->request_type;

// Get all records
if($request_type == 1){
  $stmt = $con->prepare("SELECT * FROM users");
  $stmt->execute();
  $result = $stmt->get_result();
  $data = array();
  if($result->num_rows > 0){
     while($row = $result->fetch_assoc()) {
            $data[] = array("id"=>$row['id'],"fname"=>$row['fname'],"lname"=>$row['lname'],"username"=>$row['username']); 
     }
  }
    
  $stmt->close();
  echo json_encode($data);
  exit;
}

// Insert record
if($request_type == 2){
  $fname = $data->fname;
  $lname = $data->lname;
  $username = $data->uname;

  // Check username already exists
  $stmt = $con->prepare("SELECT * FROM users WHERE username=?");
  $stmt->bind_param('s',$username);
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();
  $return_arr = array();
  if($result->num_rows == 0){

    // Insert
    $insertSQL = "INSERT INTO users(fname,lname,username ) values(?,?,?)";
    $stmt = $con->prepare($insertSQL);
    $stmt->bind_param("sss",$fname,$lname,$username);
    $stmt->execute();

    $lastinsert_id = $stmt->insert_id;
    if($lastinsert_id > 0){
       $return_arr[] = array("id"=>$lastinsert_id,"fname"=>$fname,"lname"=>$lname,"username"=>$username);
    }
    $stmt->close();
  }
  echo json_encode($return_arr);
  exit;
}

// Delete record
if($request_type == 3){
  $userid = $data->userid;
  
  // Check userid exists
  $stmt = $con->prepare("SELECT * FROM users WHERE id=?");
  $stmt->bind_param('i',$userid);
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();

  if($result->num_rows > 0){

    // Delete
    $deleteSQL = "DELETE FROM users WHERE id=?";
    $stmt = $con->prepare($deleteSQL);
    $stmt->bind_param("i",$userid);
    $stmt->execute();
    $stmt->close();

    echo 1;
  }else{ 
    echo 0;
  }

  exit;
}

5. Script

• Send $http request from the controller to get all users records where pass request_type: 1 as data. Initialize $scope.users with response data.
• On Add button click, pass entered input values as data in $http post request and set request_type: 2.  Push response.data[0] value in $scope.users if response.data is not empty.
• On delete button click, send userid from $http and set request_type: 3. Remove record from $scope.users using splice() if response.data == 1.

Completed code

<script>
var fetch = angular.module('myapp', []);

fetch.controller('userCtrl', ['$scope', '$http', function ($scope, $http) {

 // Get all records
 $http({
  method: 'post',
  url: 'addremove.php',
  data: {request_type:1},

 }).then(function successCallback(response) {
  $scope.users = response.data;
 });

 // Add new record
 $scope.add = function(){
  $http({
   method: 'post',
   url: 'addremove.php',
   data: {fname:$scope.fname,lname:$scope.lname,uname:$scope.uname,request_type:2},
  }).then(function successCallback(response) {
     if(response.data.length > 0)
        $scope.users.push(response.data[0]);
     else
        alert('Record not inserted.');
  });
 }

 // Remove record
 $scope.remove = function(index,userid){
 
  $http({
   method: 'post',
   url: 'addremove.php',
   data: {userid:userid,request_type:3},
  }).then(function successCallback(response) {
     if(response.data == 1)
        $scope.users.splice(index, 1);
     else
        alert('Record not deleted.');
  }); 
 }
 
}]);

</script>

6. Conclusion

In this tutorial, I showed how you can add or remove record from MySQL database table with AngularJS and PHP.
I use a single PHP file to handle all $http request for this define multiple if statement which executes according to request type.

If you found this tutorial helpful then don't forget to share.

The autocomplete search box suggest data options to the user based on its input.
From there, it can select the option.
In the demonstration, I am creating a search box and display suggestion list whenever the user input value in the search box. If a user clicks outside the suggestion list then hide it.
Set a selected value to search box when an option gets selected.
I am using PHP with AngularJS to fetch records.

1. Table structure

I am using users table in the tutorial example.

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.

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

Add ng-click='containerClicked()' on <div class='container'> to detect outside click of a search box and suggestion list <ul><li>.
Create a textbox element and for display, suggestion created the <ul> <li>.

• On textbox defined ng-keyup='fetchUsers()‘ and ng-model directive.Also, add ng-click='searchboxClicked($event)'.
• Show list of suggestion result on <li> using ng-repeat directive. Define ng-click directive which calls setValue($index,$event).

Completed Code

<body ng-app='myapp'>

  <div class='container' ng-controller="fetchCtrl" ng-click='containerClicked();' >
 
    <input type='text' 
          ng-keyup='fetchUsers()' 
          ng-click='searchboxClicked($event);' 
          ng-model='searchText' 
          placeholder='Enter text'><br>
    <ul id='searchResult' >
      <li ng-click='setValue($index,$event)' 
          ng-repeat="result in searchResult" >
          {{ result.name }}
      </li>
    </ul>
 
  </div>
 
</body>

4. CSS

.container{
  width: 100%; 
  height: 300px;
}

#searchResult{
  list-style: none;
  padding: 0px;
  width: 250px;
  position: absolute;
  margin: 0;
}

#searchResult li{
  background: lavender;
  padding: 4px;
  margin-bottom: 1px;
}

#searchResult li:nth-child(even){
  background: cadetblue;
  color: white;
}

#searchResult li:hover{
  cursor: pointer;
}

input[type=text]{
  padding: 5px;
  width: 250px;
  letter-spacing: 1px;
}

5. PHP

Create a new getData.php file.
Search value on users table according to POST value and limit query result to 5. Initialize the array with the users name and return a JSON value.
Completed Code

<?php

include 'config.php';

$data = json_decode(file_get_contents("php://input"));

$search = $data->searchText;

// Fetch 5 records
$sel = mysqli_query($con,"select * from users where name like '%".$search."%' limit 5");
$data = array();

while ($row = mysqli_fetch_array($sel)) {
  $data[] = array("name"=>$row['name']);
}

echo json_encode($data);

6. Script

Defined 4 methods in the controller –

• fetchUsers() – This function is been called when keydown event trigger on the input box.

• setValue() – This function is called when an option gets selected from suggestion list. Initialize $scope.searchText with $scope.searchResult[index].name (selected value) and empty $scope.searchResult. Execute $event.stopPropagation() to stop parent click from trigger.
• searchboxClicked() – Call on search box click. From here, execute $event.stopPropagation() to stop parent click from trigger.
• containerClicked() – Call on <div class='container'> click. Empty the $scope.searchResult.

Completed Code

var fetch = angular.module('myapp', []);

fetch.controller('fetchCtrl', ['$scope', '$http', function ($scope, $http) {
 
   // Fetch data
   $scope.fetchUsers = function(){
                
      var searchText_len = $scope.searchText.trim().length;

      // Check search text length
      if(searchText_len > 0){
         $http({
           method: 'post',
           url: 'getData.php',
           data: {searchText:$scope.searchText}
         }).then(function successCallback(response) {
           $scope.searchResult = response.data;
         });
      }else{
         $scope.searchResult = {};
      }
                
   }

   // Set value to search box
   $scope.setValue = function(index,$event){
      $scope.searchText = $scope.searchResult[index].name;
      $scope.searchResult = {};
      $event.stopPropagation();
   }

   $scope.searchboxClicked = function($event){
      $event.stopPropagation();
   }

   $scope.containerClicked = function(){
      $scope.searchResult = {};
   }
 
}]);

7. Conclusion

The autocomplete search allows the user to search on the list and select a value from the suggestions.
Define ng-click on the parent to detect outside of suggestion list and search box. Stop the parent click from trigger using .stopPropagation() when clicked on the suggestion list or search box.
You can check my earlier tutorial to learn the implementation of this functionality with jQuery.

It is always a better idea to check the entered username exists or not if you are allowing the users to choose username while registration and wants it to be unique.
With this, the user will instantly know that their chosen username is available or not

1. Table structure

I am using users table in the tutorial example.

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

2. Configuration

Create a new config.php file.

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

Creating a basic registration form layout that has three input elements and a button.

• Defined ng-keyup directive on username input field that calls checkUsername().
• Add a <div > following with username input field to show the available status and to adjust classes on <div> define ng-class directive.

Completed Code

<body ng-app='myapp'>

 <div class="container" ng-controller='signup'>

  <div id="div_reg">
   <h1>Registration</h1>

   <div>
    <input type="text" class="textbox" id="txt_name" ng-model="name" placeholder="Name"/>
   </div>

   <!-- Username -->
   <div>
    <input type="text" class="textbox" id="txt_uname" ng-model="username" ng-keyup='checkUsername()' placeholder="Username" />
    <div ng-class="addClass(unamestatus)" >{{ unamestatus }}</div>
   </div>

   <div>
    <input type="password" class="textbox" id="txt_pwd" ng-model="password" placeholder="Password"/>
   </div>

   <div>
    <input type="submit" value="Submit" name="but_submit" id="but_submit" />
   </div>

  </div>

 </div>

</body>

4. CSS

/* Container */
.container{
 margin: 0 auto;
 width: 70%;
}

/* Registration */
#div_reg{
 border: 1px solid gray;
 border-radius: 3px;
 width: 470px;
 height: 370px;
 box-shadow: 0px 2px 2px 0px gray;
 margin: 0 auto;
}

#div_reg h1{
 margin-top:0px;
 font-weight: normal;
 padding:10px;
 background-color:cornflowerblue;
 color:white;
 font-family:sans-serif;
}

#div_reg div{
 clear: both;
 margin-top: 10px;
 padding: 5px;
}

#div_reg .textbox{
 width: 96%;
 padding: 7px;
}

#div_reg input[type=submit]{
 padding: 7px;
 width: 100px;
 background-color: lightseagreen;
 border: 0px;
 color: white;
}

/* Response */
.response{
 padding: 6px;
 display: block;
}
.hide{
 display: none;
}
.exists{
 color: green;
}

.not-exists{
 color: red;
}

 

5. PHP

Create new uname_check.php.
Here, check the POST username value in users table and return a response.
Completed Code

<?php

include 'config.php';

$data = json_decode(file_get_contents("php://input"));

// Username
$uname = $data->username;
 
$resText = '';

if($uname != ''){
 // Check username
 $sel = mysqli_query($con,"select count(*) as allcount from users where username = '".$uname."' ");
 $row = mysqli_fetch_array($sel);

 $resText = "Available";
 if($row['allcount'] > 0){
  $resText = 'Not available';
 }
}

echo $resText;

6. Script

Defined two methods –

• checkUsername – Send $http request to check the username exists or not and initialize $scope.unamestatus with response.data.
• addClass – Return class names according  to unamestatus value.

Completed Code

var fetch = angular.module('myapp', []);

fetch.controller('signup', ['$scope', '$http', function ($scope, $http) {
 
 // Check username 
 $scope.checkUsername = function(){
 
  $http({
   method: 'post',
   url: 'uname_check.php',
   data: {username:$scope.username}
  }).then(function successCallback(response) {
   $scope.unamestatus = response.data;
  });
 }

 // Set class
 $scope.addClass = function(unamestatus){
  if(unamestatus == 'Available'){
   return 'response exists';
  }else if(unamestatus == 'Not available'){
   return 'response not-exists';
  }else{
   return 'hide';
  }
 }

}]);

7. Conclusion

Using the above script you can alert the user whether the username exists or not.
Always make sure to recheck the value before saving in your Database table.

If you found this tutorial helpful then don't forget to share

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.

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.

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

• Alphabetical search

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

 

Remember me option allow the user to automatically get logged in to the website without entering its username and password again.
To do this I am using $_COOKIE which store value on the client side for detecting the user. Next time when the user come it will automatically redirect to the homepage.
Encrypt the value before storing it in $_COOKIE and decrypt it while access. It will automatically destroy after 30 days.

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,
  `password` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

<?php
session_start();
$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

Creating a login form which has two input elements for entering username and password, a submit button and a checkbox for enabling Remember me.
Completed Code

<div class="container">
 <form method="post" action="">
  <div id="div_login">
  <h1>Login</h1>
  <div>
  <input type="text" class="textbox" name="txt_uname" placeholder="Username" />
  </div>
  <div>
  <input type="password" class="textbox" name="txt_pwd" placeholder="Password"/>
  </div>
  <div>
  <input type="checkbox" name="rememberme" value="1" />&nbsp;Remember Me
  </div>
  <div>
  <input type="submit" value="Submit" name="but_submit" />
  </div>
  </div>
 </form>
</div>

4. PHP

Initialize $_SESSION and $_COOKIE
Initializing $_SESSION['userid'] when the entered username and password matched in MySQL database table and initialize $_COOKIE['rememberme'] when Remember me checkbox is being checked.

Encrypt $_COOKIE
Before assigning a value to $_COOKIE I encrypt it for this I created encryptCookie() function and set its expire time to 30 days.

Check values
Check $_SESSION and $_COOKIE variable when the user next time come to the login page.
First, check $_SESSION variable is set or not if set then redirect the user to home.php.
If $_SESSION is not set then check $_COOKIE variable.
For decrypt the encrypted value I created decryptCookie() function and check the return value in MySQL database table. If value found then redirect to home.php.

Completed Code

include "config.php";

// Check if $_SESSION or $_COOKIE already set
if( isset($_SESSION['userid']) ){
 header('Location: home.php');
 exit;
}else if( isset($_COOKIE['rememberme'] )){
 
 // Decrypt cookie variable value
 $userid = decryptCookie($_COOKIE['rememberme']);
 
 $sql_query = "select count(*) as cntUser,id from users where id='".$userid."'";
 $result = mysqli_query($con,$sql_query);
 $row = mysqli_fetch_array($result);

 $count = $row['cntUser'];

 if( $count > 0 ){
  $_SESSION['userid'] = $userid; 
  header('Location: home.php');
  exit;
 }
}

// Encrypt cookie
function encryptCookie( $value ) {
 $key = 'youkey';
 $newvalue = base64_encode( mcrypt_encrypt( MCRYPT_RIJNDAEL_256, md5( $key ), $value, MCRYPT_MODE_CBC, md5( md5( $key ) ) ) );
 return( $newvalue );
}

// Decrypt cookie
function decryptCookie( $value ) {
 $key = 'youkey';
 $newvalue = rtrim( mcrypt_decrypt( MCRYPT_RIJNDAEL_256, md5( $key ), base64_decode( $value ), MCRYPT_MODE_CBC, md5( md5( $key ) ) ), "\0");
 return( $newvalue );
}

// On submit
if(isset($_POST['but_submit'])){

 $uname = mysqli_real_escape_string($con,$_POST['txt_uname']);
 $password = mysqli_real_escape_string($con,$_POST['txt_pwd']);
 
 if ($uname != "" && $password != ""){

  $sql_query = "select count(*) as cntUser,id from users where username='".$uname."' and password='".$password."'";
  $result = mysqli_query($con,$sql_query);
  $row = mysqli_fetch_array($result);

  $count = $row['cntUser'];

  if($count > 0){
   $userid = $row['id'];
   if( isset($_POST['rememberme']) ){

    // Set cookie variables
    $days = 30;
    $value = encryptCookie($userid);
    setcookie ("rememberme",$value,time()+ ($days * 24 * 60 * 60 * 1000));
   }
 
   $_SESSION['userid'] = $userid; 
   header('Location: home.php');
   exit;
  }else{
   echo "Invalid username and password";
  }

 }

}

Homepage

Within the homepage, I created a logout button. Using it to destroy the $_SESSION and $_COOKIE variable when it gets button gets clicked.
Completed Code

<?php
 include "config.php";

// Check user login or not
 if(!isset($_SESSION['userid'])){
 header('Location: index.php');
}

// logout
if(isset($_POST['but_logout'])){
 session_destroy();

 // Remove cookie variables
 $days = 30;
 setcookie ("rememberme","", time() - ($days * 24 * 60 * 60 * 1000));

 header('Location: index.php');
}
?>
<h1>Homepage</h1>
<form method='post' action="">
 <input type="submit" value="Logout" name="but_logout">
</form>

5. Conclusion

For security purpose, I encoded the userid before storing it in a $_COOKIE and you can replace the key with your key which should be long enough so anyone couldn’t guess.

You can save your uploading images in the database table for later use e.g. display user profile or product image, create the image gallery, etc.
There are two ways of doing this –

• Save the path or name of an image
• Encode image into a base64 format

In this tutorial, I show you both of the methods for storing and retrieving an image from the database table.

1. Table structure

In the example, I am using images table for storing data.

• name – This field is used to store the image file name.
• image – This field is used to store the image base64 generated value.

CREATE TABLE `images` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `image` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file.

<?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. Save path or name

You can either save the full path or name of an image in your MySQL database table. Retrieve the image name or path from the MySQL database and use to make an image source.
Here, I am storing the file name in the MySQL database.
Completed Code

<?php
include("config.php");

if(isset($_POST['but_upload'])){
 
  $name = $_FILES['file']['name'];
  $target_dir = "upload/";
  $target_file = $target_dir . basename($_FILES["file"]["name"]);

  // Select file type
  $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));

  // Valid file extensions
  $extensions_arr = array("jpg","jpeg","png","gif");

  // Check extension
  if( in_array($imageFileType,$extensions_arr) ){
 
     // Insert record
     $query = "insert into images(name) values('".$name."')";
     mysqli_query($con,$query);
  
     // Upload file
     move_uploaded_file($_FILES['file']['tmp_name'],$target_dir.$name);

  }
 
}
?>

<form method="post" action="" enctype='multipart/form-data'>
  <input type='file' name='file' />
  <input type='submit' value='Save name' name='but_upload'>
</form>

Retrieve

Select the name or path of the image which you have stored in the database table and use it in the image source.
Example

<?php

$sql = "select name from images where id=1";
$result = mysqli_query($con,$sql);
$row = mysqli_fetch_array($result);

$image = $row['name'];
$image_src = "upload/".$image;

?>
<img src='<?php echo $image_src;  ?>' 

4. base64_encode()

You can store the full image in the Database table by converting it into the base64 format. You don’t need to store image reference in Database table e.g. name, path and not require to store the image on your server.
In PHP base64_encode() method is been used for base64 conversion. Before storing it in the database I append data:image/'.$imageFileType.';base64, text with base64 value.
Now when need to display the image just fetch the value and use it as an image source.

Note – In the example, I upload the image to directory. You can remove the upload code if you only want the image will accessible through base64 stored values in the database.

Completed Code

<?php
include("config.php");

if(isset($_POST['but_upload'])){
 
  $name = $_FILES['file']['name'];
  $target_dir = "upload/";
  $target_file = $target_dir . basename($_FILES["file"]["name"]);

  // Select file type
  $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));

  // Valid file extensions
  $extensions_arr = array("jpg","jpeg","png","gif");

  // Check extension
  if( in_array($imageFileType,$extensions_arr) ){
 
    // Convert to base64 
    $image_base64 = base64_encode(file_get_contents($_FILES['file']['tmp_name']) );
    $image = 'data:image/'.$imageFileType.';base64,'.$image_base64;
    // Insert record
    $query = "insert into images(image) values('".$image."')";
    mysqli_query($con,$query);
  
    // Upload file
    move_uploaded_file($_FILES['file']['tmp_name'],$target_dir.$name);
  }
 
}
?>

<form method="post" action="" enctype='multipart/form-data'>
  <input type='file' name='file' />
  <input type='submit' value='Save name' name='but_upload'>
</form>

Retrieve

Select the stored base64 value and using it in the image source.
Example

<?php

 $sql = "select image from images where id=1";
 $result = mysqli_query($con,$sql);
 $row = mysqli_fetch_array($result);

 $image_src2 = $row['image'];
 
?>
<img src='<?php echo $image_src; ?>' >

5. Conclusion

In my opinion, instead of storing an image in MySQL database in the base64 format, it’s better to store in the server and save the reference in the database table to keep track of the location.
It is fast and consumes less space in the database table compare to base64.
If you found this tutorial helpful then don't forget to share.

With only using AngularJS it is not possible to get data from MySQL database because it only handles Client-side requests.
You have to use any Server side language at the backend which handles the request and returns the response.
In AngularJS $http service is use to send AJAX request.

1. Table structure

I am using users table in the tutorial example.

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

2. Configuration

Create a new config.php file.

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

You can either download and include angular.min.js in the page or use CDN.

<script src='https://ajax.googleapis.com/ajax/libs/angularjs/1.6.1/angular.min.js'></script>

Define <body ng-app='myapp'> and <div ng-controller='userCtrl'> on the page.
For displaying data create a <table> layout and specify ng-repeat directive on a row. Which loop through all available data in users Array.
Completed Code

 <!doctype html>
<html>
 <head>
  <title>How to get data from MySQL with AngularJS - PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.1/angular.min.js"></script>
 
 </head>
 <body ng-app='myapp'>

  <div ng-controller="userCtrl">
 
    <table >
 
      <tr>
        <th>First name</th>
        <th>Last name</th>
        <th>Username</th>
      </tr>
  
      <!-- Display records -->
      <tr ng-repeat="user in users">
        <td>{{user.fname}}</td>
        <td>{{user.lname}}</td>
        <td>{{user.username}}</td>
      </tr>
 
    </table>
  </div>
 
 </body>
</html>

4. PHP

Create a getData.php file.
From here return JSON Array which has user details (fname, lname, and username).
Completed Code

include 'config.php';

$sel = mysqli_query($con,"select * from users");
$data = array();

while ($row = mysqli_fetch_array($sel)) {
 $data[] = array("fname"=>$row['fname'],"lname"=>$row['lname'],"username"=>$row['username']);
}
echo json_encode($data);

5. Script

Create new module and setup AJAX request in controller ( userCtrl ).

• Path – Passing file name getData.php in url and set method to get.
• Success – AJAX successfully callback handle by then where store reponse.data to $scope.users.

At HTML end display data using ng-repeat directive.
Completed Code

<script>
var fetch = angular.module('myapp', []);

fetch.controller('userCtrl', ['$scope', '$http', function ($scope, $http) {
 $http({
  method: 'get',
  url: 'getData.php'
 }).then(function successCallback(response) {
  // Store response data
  $scope.users = response.data;
 });
}]);

</script>

6. Conclusion

Angular provide $http service which helps to make an AJAX call to the server.
Similar to jQuery AJAX request you can either send GET or POST type request. Within tutorial example, I send GET request from $http to fetch data from MySQL database in JSON format.

If you found this tutorial helpful then don't forget to share.

You don’t need to require any other extra plugin for working with Zip files.
PHP has ZipArchive class that allows us to create a zip file or extract the existing file.
ZipArchive class extractTo() method is used to extract the zip file that takes destination absolute path as argument.

1. On the same directory

I am assuming the Zip file is stored in the project root directory.
Create an object of the ZipArchive class and open given zip file using $zip->open() method.
If it returns TRUE then extract the file to the specified path ($path) with extractTo() method by passing path value as an argument in it.

Here, I am extracting the file in project files directory.

Completed Code

// Get Project path
define('_PATH', dirname(__FILE__));

// Zip file name
$filename = 'zipfile.zip';
$zip = new ZipArchive;
$res = $zip->open($filename);
if ($res === TRUE) {

 // Unzip path
 $path = _PATH."/files/";

 // Extract file
 $zip->extractTo($path);
 $zip->close();

 echo 'Unzip!';
} else {
 echo 'failed!';
}

2. Specific file

With file element, you can select the zip file that you want to extract.
If a selected file is valid then pass $_FILES['file']['tmp_name'] to open() method and extract it to specified path using extractTo() method.
Completed Code

<?php 
// Get Project path
define('_PATH', dirname(__FILE__));

// Unzip selected zip file
if(isset($_POST['unzip'])){
 $filename = $_FILES['file']['name'];

 // Get file extension
 $ext = pathinfo($filename, PATHINFO_EXTENSION);

 $valid_ext = array('zip');

 // Check extension
 if(in_array(strtolower($ext),$valid_ext)){
  $tmp_name = $_FILES['file']['tmp_name'];

  $zip = new ZipArchive;
  $res = $zip->open($tmp_name);
  if ($res === TRUE) {

   // Unzip path
   $path = _PATH."/files/";

   // Extract file
   $zip->extractTo($path);
   $zip->close();

   echo 'Unzip!';
  } else {
   echo 'failed!';
  }
 }else{
  echo 'Invalid file';
 }
 
}
?>
<form method='post' action='' enctype='multipart/form-data'>
 
 <!-- Unzip selected zip file -->
 <input type='file' name='file'><br/>
 <input type='submit' name='unzip' value='Unzip' />
</form>

3. Conclusion

Using above PHP script you can extract the existing zip files at the specified location. For this, you need to pass the absolute path in extractTo() method of ZipArchive Class.
If you found this tutorial helpful then don't forget to share.

For upload file with AngularJS need to send the file by $http service and with the use of PHP store the requested file to the server and return a response.
In the demonstration, I am creating two examples –

• In the first, using JavaScript to select a file,
• And in the second, using a custom directive

Then pass it as data in $http service for upload.

1. Without Custom directive

HTML

Create a file element, a button for upload, and <p> for display response result.
With button added ng-click directive which calls upload() function.
Completed Code

<body ng-app='myapp'>

 <div ng-controller="userCtrl">
  
  <input type='file' name='file' id='file'><br/>
  <input type='button' value='Upload' id='upload' ng-click='upload()' >
  
  <p>{{ response.name }}</p>
 </div>

</body>

AngularJS

Declare a module and define $scope.upload in the controller which triggers the button click.
Within the function get the selected file from the file element and append it to FormData object variable fd.
Sending POST request using $http service where pass FormData object as data and set Content-Type: undefined using headers property.
Completed Code

 var upload= angular.module('myapp', []);

upload.controller('userCtrl', ['$scope', '$http', function ($scope, $http) {
 $scope.upload = function(){
 
  var fd = new FormData();
  var files = document.getElementById('file').files[0];
  fd.append('file',files);

  // AJAX request
  $http({
   method: 'post',
   url: 'upload.php',
   data: fd,
   headers: {'Content-Type': undefined},
  }).then(function successCallback(response) { 
    // Store response data
    $scope.response = response.data;
  });
 }
 
}]);

2. With Custom directive

HTML

Only added a custom model ng-file='uploadfiles' on the input element and other codes will be the same as the above example.
Completed Code

 <body ng-app='myapp'>

 <div ng-controller="userCtrl">
  
  <input type='file' name='file' id='file' ng-file='uploadfiles'><br/>
  <input type='button' value='Upload' id='upload' ng-click='upload()' >
  
  <p>{{ response.name }}</p>
 </div>

</body>

AngularJS

Declaring custom directive using .directive where initializing the scope when the change event trigger and use it to define FormData Object variable fd.
Completed Code

var upload = angular.module('myapp', []);
upload.directive('ngFile', ['$parse', function ($parse) {
 return {
  restrict: 'A',
  link: function(scope, element, attrs) {
   element.bind('change', function(){

    $parse(attrs.ngFile).assign(scope,element[0].files)
    scope.$apply();
   });
  }
 };
}]);

upload.controller('userCtrl', ['$scope', '$http', function ($scope, $http) {
 
 $scope.upload = function(value){
  var fd=new FormData();
  angular.forEach($scope.uploadfiles,function(file){
  fd.append('file',file);
 });

 $http({
  method: 'post',
  url: 'upload.php',
  data: fd,
  headers: {'Content-Type': undefined},
 }).then(function successCallback(response) { 
  // Store response data
  $scope.response = response.data;
 });
}
 
}]);

3. PHP

Create a new upload.php file and an upload directory.
Upload file to upload directory and return an Array in JSON format which has a file name.
Completed Code

<?php

/* Getting file name */
$filename = $_FILES['file']['name'];

/* Location */
$location = 'upload/';

/* Upload file */
move_uploaded_file($_FILES['file']['tmp_name'],$location.$filename);

$arr = array("name"=>$filename);
echo json_encode($arr);

4. Conclusion

Both examples are same only FormData initializing step is different. You can use any of them to upload file to the server with Angular.
If you found this tutorial helpful then don't forget to share.

There is the various approach of selecting the last insert id from MySQL table.

• Select a single row from the table in descending order and store the id.
• Select Maximum value.
• The following query gives you next AUTO_INCREMENT value from the selected table which you can use to get the last id.

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user'

If your currently AUTO_INCREMENT column last value is 8 and when you execute the above query on the Table this will return 9.
In PHP there is an inbuilt method which simply returns the last insert id according to the previous insert query.

1. mysqli_insert_id()

It returns last AUTO_INCREMENT column value of the previous successfully executed insert query.
Syntax –

mysqli_insert_id($connection-variable)

It returns 0 when the table doesn’t have any AUTO_INCREMENT column.

2. Example

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

// Insert query 
$query = "insert into users(username,fname,lname) values('sonarika','Sonarika','Bhadoria')"; 

mysqli_query($con,$query); 

// Get last insert id 
$lastid = mysqli_insert_id($con); 

echo "last id : ".$lastid; 
?>

Output
last id : 4

3. Conclusion

Now you know how to get last insert id value using an inbuilt method. But you need to call it immediately after the insert query because it works according to the last query.
If you are maintaining the id column manually and not using AUTO_INCREMENT in MySQL table then it is not a good choice you can go with other options.
You can learn more about mysqli_insert_id() method from here.

 

If the form is already submitted and the user tries to refresh the webpage then the browser will attempt to resubmit the form.
If you are allowing the user to fill and submit the form for insert data. In this case, if the user refreshes the page then the data is reinserted on the MySQL database table if some action does not perform for preventing it.
In this tutorial, I show you some ways by using which you can avoid the page resubmit.

1. HTML Form

In the demonstration, I am using the following <form> which have 4 input fields and a button.

<form method='post' action=''>
  <input type='text' placeholder='Enter username' name='txt_uname' id='txt_uname' ><br/>
  <input type='text' placeholder='Enter First name' name='txt_fname' id='txt_fname' ><br/>
  <input type='text' placeholder='Enter Last name' name='txt_lname' id='txt_lname' ><br/>
  <input type='text' placeholder='Enter email' name='txt_email' id='txt_email' ><br/>
  <input type='submit' value='Submit' id='submit' name='submit'>
</form>

2. Redirect the user to another page

Simply redirect the user to another page after the form successfully submitted.
Example

<?php 
include 'config.php'; // Database connection

if(isset($_POST['submit'])){
  $uname = $_POST['txt_uname'];
  $fname = $_POST['txt_fname'];
  $lname = $_POST['txt_lname'];
  $email = $_POST['txt_email'];

  // Insert record
  $insert_query = "INSERT INTO 
                 users(username,fname,lname,email) 
                 VALUES('".$uname."','".$fname."','".$lname."','".$email."')";
  mysqli_query($con,$insert_query);

  // Redirect to another page
  header('location: home.php');
}
?>

3. Check entry in MySQL Database Table

Here, you can do two things –

• Check the unique value in a field
• Check all submit values in MySQL

Check the unique value in a field

Create a field in your table which only stores unique value e.g. username, email, etc on each page submit. Check the submit value in this field if it’s not found then insert the record.
Example

<?php 
include 'config.php'; // Database connection

if(isset($_POST['submit'])){
  $uname = $_POST['txt_uname'];
  $fname = $_POST['txt_fname'];
  $lname = $_POST['txt_lname'];
  $email = $_POST['txt_email'];

  // Check username is exists or not
  $query = "SELECT count(*) as allcount FROM users WHERE username='".$uname."'";
  $result = mysqli_query($con,$query);
  $row = mysqli_fetch_array($result);
  $allcount = $row['allcount'];

  // insert new record
  if($allcount == 0){
    $insert_query = "INSERT INTO 
       users(username,fname,lname,email) 
       VALUES('".$uname."','".$fname."','".$lname."','".$email."')";
    mysqli_query($con,$insert_query);
  }
 
}
?>

Check all submit values in MySQL

You can either check all submitted values exists in the MySQL database table if not then insert a new record.
Example

<?php 
include 'config.php'; // Database connection

if(isset($_POST['submit'])){
  $uname = $_POST['txt_uname'];
  $fname = $_POST['txt_fname'];
  $lname = $_POST['txt_lname'];
  $email = $_POST['txt_email'];

  // Check username is exists or not
  $query = "SELECT count(*) as allcount FROM users 
          WHERE username='".$uname."' && fname='".$fname."' && 
          lname='".$lname."' && email='".$email."'";
  $result = mysqli_query($con,$query);
  $row = mysqli_fetch_array($result);
  $allcount = $row['allcount'];

  // insert new record
  if($allcount == 0){
     $insert_query = "INSERT INTO 
        users(username,fname,lname,email) 
        VALUES('".$uname."','".$fname."','".$lname."','".$email."')";
     mysqli_query($con,$insert_query);
  }
 
}
?>

4. With AJAX

You can use AJAX to save data and you can either reload the page or not on successful callback.
PHP (saveData.php)

<?php 
include 'config.php'; // Database connection

$uname = $_POST['username'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$email = $_POST['email'];

$insert_query = "INSERT INTO 
                 users(username,fname,lname,email) 
                 VALUES('".$uname."','".$fname."','".$lname."','".$email."')";
mysqli_query($con,$insert_query);
echo 1;
?>

jQuery

$(document).ready(function(){

  $("#submit").click(function(){
    var username = $("#txt_uname").val();
    var fname = $("#txt_fname").val();
    var lname = $("#txt_lname").val();
    var email = $("#txt_email").val();
 
    $.ajax({
       url:'saveData.php',
       type:'post',
       data:{username:username,fname:fname,lname:lname,email:email},
       success:function(response){
          location.reload(); // reloading page
       }
    });
 
  });
});

5. Conclusion

I showed you some of the ways by using it you can prevent the page from resubmitting. You can use any of the methods on your webpage.
If you found this tutorial helpful then don't forget to share.

The Pdf file creation in PHP mainly requires when need to generate the file on the basis of the available data otherwise, simply create it manually with the external applications.
For example – generating the report, the user certificate, etc.
For generating pdf file I am using Dompdf library which generates the downloadable pdf file from HTML.

1. Basic example

Download the library from here.
Store HTML string in $html and assign file name in $filename.
Include autoload.inc.php.
Instantiating Dompdf Class object and pass $html in loadHtml() method. The render() method renders the HTML to pdf.
Completed Code

<?php
$html = "<table border='1' width='100%' style='border-collapse: collapse;'>
        <tr>
            <th>Username</th><th>Email</th>
        </tr>
        <tr>
            <td>yssyogesh</td>
            <td>yogesh@makitweb.com</td>
        </tr>
        <tr>
            <td>sonarika</td>
            <td>sonarika@gmail.com</td>
        </tr>
        <tr>
            <td>vishal</td>
            <td>vishal@gmail.com</td>
        </tr>
        </table>";
$filename = "newpdffile";

// include autoloader
require_once 'dompdf/autoload.inc.php';

// reference the Dompdf namespace
use Dompdf\Dompdf;

// instantiate and use the dompdf class
$dompdf = new Dompdf();

$dompdf->loadHtml($html);

// (Optional) Setup the paper size and orientation
$dompdf->setPaper('A4', 'landscape');

// Render the HTML as PDF
$dompdf->render();

// Output the generated PDF to Browser
$dompdf->stream($filename);

2. Preview of the file

For displaying the preview of the file before it is being downloaded. You need to pass the second parameter in the stream() method which contains Attachment option name in the Array.
By default, Attachment option value is 1 which force the browser to open download dialog. So, instead of 1 pass 0.
Completed Code

<?php

$html = "<table border='1' width='100%' style='border-collapse: collapse;'>
        <tr>
            <th>Username</th><th>Email</th>
        </tr>
        <tr>
            <td>yssyogesh</td>
            <td>yogesh@makitweb.com</td>
        </tr>
        <tr>
            <td>sonarika</td>
            <td>sonarika@gmail.com</td>
        </tr>
        <tr>
            <td>vishal</td>
            <td>vishal@gmail.com</td>
        </tr>
        </table>";
$filename = "newpdffile";

// include autoloader
require_once 'dompdf/autoload.inc.php';

// reference the Dompdf namespace
use Dompdf\Dompdf;

// instantiate and use the dompdf class
$dompdf = new Dompdf();

$dompdf->loadHtml($html);

// (Optional) Setup the paper size and orientation
$dompdf->setPaper('A4', 'landscape');

// Render the HTML as PDF
$dompdf->render();

// Output the generated PDF to Browser
$dompdf->stream($filename,array("Attachment"=>0));

3. Save to server

Saving the generated file on the server for this executing output() method after the render() and store return value in a variable($output) and writing it in a file using file_put_contents() method.
Completed Code

 <?php 

$html = "<table border='1' width='100%' style='border-collapse: collapse;'>
        <tr>
            <th>Username</th><th>Email</th>
        </tr>
        <tr>
            <td>yssyogesh</td>
            <td>yogesh@makitweb.com</td>
        </tr>
        <tr>
            <td>sonarika</td>
            <td>sonarika@gmail.com</td>
        </tr>
        <tr>
            <td>vishal</td>
            <td>vishal@gmail.com</td>
        </tr>
        </table>";

// include autoloader
require_once 'dompdf/autoload.inc.php';

// reference the Dompdf namespace
use Dompdf\Dompdf;

// instantiate and use the dompdf class
$dompdf = new Dompdf();

$dompdf->loadHtml($html);

// (Optional) Setup the paper size and orientation
$dompdf->setPaper('A4', 'landscape');

// Render the HTML as PDF
$dompdf->render();

$output = $dompdf->output();
file_put_contents("file.pdf", $output); 

4. Conclusion

You can easily use this library in your project when you have to generate the pdf file. You need to create your HTML layout and store in a variable or you can directly pass it in loadHtml().
It has some limitations that you need to know –

• not particularly tolerant to poorly-formed HTML input (using Tidy first may help).
• Large files or large tables can take a while to render.
• CSS float is not supported (but is in the works).

You can learn more about this from their documentation with the examples.
If you found this tutorial helpful then don't forget to share.

Live editable table is good for update content quickly while viewing it.
This can be used to update full records details or some of them and you have doesn’t need to send the user to edit form page.
It is user-friendly and timesaving.
To implementing this I am using jQuery AJAX.
In this tutorial, I show you two ways to make your table columns editable –

1. Using contentEditable attribute
2. Showing the input element when the user clicks on the cell.

3. 1. Table structure

   Create users table.
   

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

   2. Configuration

   Create a config.php for database connection.
   
   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. With ContentEditable

   The contenteditable attribute enables the container to take input values.
   

   HTML

   Displaying user list on the <table>. Creating a <div contenteditable='true'> inside cell which stores the values.
   Completed Code
   

   <div class='container'>
    
    <table width='100%' border='0'>
     <tr>
      <th width='10%'>S.no</th>
      <th width='40%'>Username</th>
      <th width='40%'>Name</th>
     </tr>
     <?php 
      $query = "select * from users order by name";
      $result = mysqli_query($con,$query);
      $count = 1;
      while($row = mysqli_fetch_array($result) ){
       $id = $row['id'];
       $username = $row['username'];
       $name = $row['name'];
     ?>
     <tr>
      <td><?php echo $count; ?></td>
      <td> 
        <div contentEditable='true' class='edit' id='username_<?php echo $id; ?>'> 
          <?php echo $username; ?>
        </div> 
      </td>
      <td> 
        <div contentEditable='true' class='edit' id='name_<?php echo $id; ?>'>
          <?php echo $name; ?> 
        </div> 
      </td>
     </tr>
     <?php
      $count ++;
     }
     ?> 
    </table>
   </div> 

   CSS

    .edit{
    width: 100%;
    height: 25px;
   }
   .editMode{
    border: 1px solid black;
   }
   
   /* Table Layout */
   table {
    border:3px solid lavender;
    border-radius:3px;
   }
   
   table tr:nth-child(1){
    background-color:dodgerblue;
   }
   table tr:nth-child(1) th{
    color:white;
    padding:10px 0px;
    letter-spacing: 1px;
   }
   
   /* Table rows and columns */
   table td{
    padding:10px;
   }
   table tr:nth-child(even){
    background-color:lavender;
    color:black;
   }

   PHP

   Create a new update.php file for saving the modified content.
   Completed Code
   

   <?php
   
   include "config.php";
   
   $field = $_POST['field'];
   $value = $_POST['value'];
   $editid = $_POST['id'];
   
   $query = "UPDATE users SET ".$field."='".$value."' WHERE id=".$editid;
   mysqli_query($con,$query);
   
   echo 1;

   jQuery

   Saving content when the focus out from <div class='edit'>. In AJAX request passing field name, value, and id.
   Completed Code
   

   $(document).ready(function(){
    
    // Add Class
    $('.edit').click(function(){
     $(this).addClass('editMode');
    });
   
    // Save data
    $(".edit").focusout(function(){
     $(this).removeClass("editMode");
     var id = this.id;
     var split_id = id.split("_");
     var field_name = split_id[0];
     var edit_id = split_id[1];
     var value = $(this).text();
   
     $.ajax({
      url: 'update.php',
      type: 'post',
      data: { field:field_name, value:value, id:edit_id },
      success:function(response){
       console.log('Save successfully'); 
      }
     });
    
    });
   
   });

   5. Show Input Element

   In the second method, instead of making container editable using input elements which show when the user clicks on the cell.
   

   HTML

   In the table cell with <div> container also creating input element which is shown only when the user clicks on the <div> container. This does with jQuery.
   Both of them contains the same values.
   Completed Code
   

    <table width='100%' border='0'>
    <tr>
     <th width='10%'>S.no</th>
     <th width='40%'>Username</th>
     <th width='40%'>Name</th>
    </tr>
    <?php 
    $query = "select * from users order by name";
    $result = mysqli_query($con,$query);
    $count = 1;
    while($row = mysqli_fetch_array($result) ){
     $id = $row['id'];
     $username = $row['username'];
     $name = $row['name'];
    ?>
    <tr>
     <td><?php echo $count; ?></td>
     <td> 
       <div class='edit' > <?php echo $username; ?></div> 
       <input type='text' class='txtedit' value='<?php echo $username; ?>' id='username_<?php echo $id; ?>' >
     </td>
     <td> 
      <div class='edit' ><?php echo $name; ?> </div> 
      <input type='text' class='txtedit' value='<?php echo $name; ?>' id='name_<?php echo $id; ?>' >
     </td>
    </tr>
    <?php
    $count ++;
    }
    ?> 
   </table>

   
   

   CSS

   CSS is similar as in the above example but just added one new class.
   Completed Code
   

    .txtedit{
    display: none;
    width: 99%;
    height: 30px;
   }

   
   

   PHP

   The PHP code is similar to the first example there is no change in the code.
   

   jQuery

   Displaying Textbox when the click event triggers on <div class='edit'> and hiding the container.
   When the focus out of the Textbox then send AJAX request to save content and hide the Textbox and update the container text before showing.
   Completed Code
   

    $(document).ready(function(){
    
    // Show Input element
    $('.edit').click(function(){
     $('.txtedit').hide();
     $(this).next('.txtedit').show().focus();
     $(this).hide();
    });
   
    // Save data
    $(".txtedit").focusout(function(){
     
     // Get edit id, field name and value
     var id = this.id;
     var split_id = id.split("_");
     var field_name = split_id[0];
     var edit_id = split_id[1];
     var value = $(this).val();
     
     // Hide Input element
     $(this).hide();
   
     // Hide and Change Text of the container with input elmeent
     $(this).prev('.edit').show();
     $(this).prev('.edit').text(value);
   
     // Sending AJAX request
     $.ajax({
      url: 'update.php',
      type: 'post',
      data: { field:field_name, value:value, id:edit_id },
      success:function(response){
       console.log('Save successfully'); 
      }
     });
    
    });
   
   });

   
   

   7. Conclusion

   Live data editing is a quick and easy way to allow users to modify the contents.
   I showed you two ways for implementing this you can use any one of them in your project.
   Within the demonstration, I used TextBox element in the second example you also use any other elements like – radio button, checkboxes, etc. according to your requirement.
   

   Note – If you have long list of information and you are showing some of them on your table layout, in this case, its better to take the user to edit page to update some other information.

   If you found this tutorial helpful then don't forget to share
   
   

There are many things you can do with AJAX to improve the user experience, for example, Add, edit or delete the record and update layout without reloading the page.
With only PHP you can easily delete records but require to submit the page or send value by URL and according to it delete a record.
This works and removes the record but it reloads the page every time.
You can do this with jQuery AJAX where you need to pass the record id from AJAX which needs to delete.
In the demonstration, I am creating the HTML table which shows the list of records with a delete button. When the button gets clicked then remove the record and also remove HTML table row with fadeOut() effect.

1. Table structure

I am using posts Table.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `link` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a config.php file for database connection.
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

List records from posts table in the <table >.
Here, I add the <span class='delete'> for deleting the record and use the record id to define value of id attribute.
Completed Code

 <div class='container'>
 <table border='1' >
  <tr style='background: whitesmoke;'>
   <th>S.no</th>
   <th>Title</th>
   <th>Operation</th>
  </tr>

  <?php 
  $query = "SELECT * FROM posts";
  $result = mysqli_query($con,$query);

  $count = 1;
  while($row = mysqli_fetch_array($result) ){
    $id = $row['id'];
    $title = $row['title'];
    $link = $row['link'];

  ?>
    <tr>
     <td align='center'><?php echo $count; ?></td>
     <td><a href='<?php echo $link; ?>'><?php echo $title; ?></a></td>
     <td align='center'>
       <span class='delete' id='del_<?php echo $id; ?>'>Delete</span>
     </td>
    </tr>
  <?php
   $count++;
  }
 ?>
 </table>
</div>

4. PHP

Create a remove.php file.
Read POST id.
Check if a record exists or not in the posts table. If exists then execute Delete query on the id.
echo 1 for return.
If not deleted then echo 0 for return.
Completed Code

<?php 
include "config.php";

$id = $_POST['id'];

if($id > 0){

  // Check record exists
  $checkRecord = mysqli_query($con,"SELECT * FROM posts WHERE id=".$id);
  $totalrows = mysqli_num_rows($checkRecord);

  if($totalrows > 0){
    // Delete record
    $query = "DELETE FROM posts WHERE id=".$id;
    mysqli_query($con,$query);
    echo 1;
    exit;
  }
}

echo 0;
exit;

5. jQuery

Define click event on delete class.
Get delete id by splitting the element id and sending this in AJAX request. On AJAX successfully callback check response.
If response == 1 then remove a row using remove() and use fadeOut() effect to add some delay.
If response != 1 then alert('Invalid ID.');.
Completed Code

$(document).ready(function(){

 // Delete 
 $('.delete').click(function(){
   var el = this;
   var id = this.id;
   var splitid = id.split("_");

   // Delete id
   var deleteid = splitid[1];
 
   // AJAX Request
   $.ajax({
     url: 'remove.php',
     type: 'POST',
     data: { id:deleteid },
     success: function(response){

       if(response == 1){
  // Remove row from HTML Table
  $(el).closest('tr').css('background','tomato');
  $(el).closest('tr').fadeOut(800,function(){
     $(this).remove();
  });
      }else{
  alert('Invalid ID.');
      }

    }
   });

 });

});

6. Conclusion

In this post, I explained how you can use AJAX to delete existing records from MySQL Table. I used it with HTML table in the demonstration you can also use it with <div>, <ul>, etc. elements.

Pagination is a technique to break and display the large list of content in the smaller parts. This reduces your page load time and it is user-friendly.
There are multiple types of pagination available some are –

• Previous and Next
• Load More
• Numeric

In this tutorial, I show How you can add numeric pagination to your web page.

1. Table structure

I am using employee table

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(80) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(70) 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 = mysql_connect($host, $user, $password) or die("Unable to connect");

// selecting database
$db = mysql_select_db($dbname, $con) or die("Database not found");

3. HTML and PHP

Now displaying content from the MySQL table and add pagination.

• I set the maximum number of rows to display at a time to 5 which you change by editing –

 $rowperpage = 5; // Total rows display

• If $_GET['page'] is set then update the $row value which we use in SELECT query LIMIT.
• Counting the total number of rows and fetch data from MySQL table.
• Now creating a unordered list for numeric pagination which displays page number where I set the maximum page numbers to display at a time too 5.

             // Total number list show
                    $numpages = 5;

 Create <li> which contains page numbers by looping and break the loop when remainder equals to 0.

if($i%($numpages+1) == 0){
 break;
}

   Completed Code 

<div id="content">
 <?php
 include("config.php");

 $rowperpage = 5; // Total rows display
 
 $row = 0;
 if(isset($_GET['page'])){
  $row = $_GET['page']-1;
  if($row < 0){
   $row = 0;
  }
 }

 // count total number of rows
 $sql = "SELECT COUNT(*) AS cntrows FROM employee";
 $result = mysql_query($sql);
 $fetchresult = mysql_fetch_array($result);
 $allcount = $fetchresult['cntrows'];
 ?>

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

  // selecting rows
  $limitrow = $row*$rowperpage;
  $sql = "SELECT * FROM employee ORDER BY ID ASC limit $limitrow,".$rowperpage;
  $result = mysql_query($sql);

  /////////////
  $sno = $row + 1;
  if(isset($_GET['page'])){
   $sno = (($_GET['page']*$rowperpage)+1) - $rowperpage;
   if($sno <=0) $sno = 1;
  }
  while($fetch = mysql_fetch_array($result)){
   $name = $fetch['emp_name'];
   $salary = $fetch['salary'];
  ?>
   <tr>
    <td align='center'><?php echo $sno; ?></td>
    <td align='center'><?php echo $name; ?></td>
    <td align='center'><?php echo $salary; ?></td>
   </tr>
  <?php
   $sno ++;
  }
  ?>
 </table>


 <!-- Number list (start) -->
 <ul class="pagination">
 
 <?php
 // calculate total pages
 $total_pages = ceil($allcount / $rowperpage);

 $i = 1;$prev = 0;

 // Total number list show
 $numpages = 5;

 // Set previous page number and start page 
 if(isset($_GET['next'])){
  $i = $_GET['next']+1;
  $prev = $_GET['next'] - ($numpages);
 }

 if($prev <= 0) $prev = 1;
 if($i == 0) $i=1;

 // Previous button next page number
 
 $prevnext = 0;
 if(isset($_GET['next'])){
  $prevnext = ($_GET['next'])-($numpages+1);
  if($prevnext < 0){
   $prevnext = 0;
  }
 }
 
 // Previous Button
 echo '<li ><a href="?page='.$prev.'&next='.$prevnext.'">Previous</a></li>';
 
 if($i != 1){
  echo '<li ><a href="?page='.($i-1).'&next='.$_GET['next'].'" '; 
  if( ($i-1) == $_GET['page'] ){
   echo ' class="active" ';
  }
  echo ' >'.($i-1).'</a></li>';
 }
 
 // Number List
 for ($shownum = 0; $i<=$total_pages; $i++,$shownum++) {
  if($i%($numpages+1) == 0){
   break;
  }
 
  if(isset($_GET['next'])){ 
   echo "<li><a href='?page=".$i."&next=".$_GET['next']."'";
  }else{
   echo "<li><a href='?page=".$i."'";
  }
 
  // Active
  if(isset($_GET['page'])){
   if ($i==$_GET['page']) 
    echo " class='active'";
   }
   echo ">".$i."</a></li> ";
  }

  // Set next button
  $next = $i+$rowperpage;
  if(($next*$rowperpage) > $allcount){
   $next = ($next-$rowperpage)*$rowperpage;
  }

  // Next Button
  if( ($next-$rowperpage) < $allcount ){ 
   if($shownum == ($numpages)){
    echo '<li ><a href="?page='.$i.'&next='.$i.'">Next</a></li>';
   }
  }
 
  ?>
 </ul>
 <!-- Numbered List (end) -->
</div>

4. CSS

Adding some CSS to the unordered list.
 Completed Code:-

/* Numeric */
.pagination{
 list-style: none;
}
.pagination li{
 float: left;
 border: 1px solid #000;
 padding: 5px 7px;
 margin-right: 10px;
 border-radius: 3px;
}
.pagination li a{
 text-decoration: none;
 color:black;
}

.active{
 color: red !important;
}

5. Conclusion

I hope this tutorial, helps and explains you the addition of numeric pagination to the web page.