Load more data using jQuery Ajax and PHP from database

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestPrint this pageEmail this to someone

Youtube, Twitter and Facebook are using Load More Data technique. They provide users to dynamically load the data on a button click instead of displaying the pagination links. Show more technique is very interactive because the data is loaded without refreshing the page. In this article, we will build a similar technique for load more data on click from the database using jQuery Ajax and PHP. This step by step tutorial will make the whole process very easy and understandable for you.

Load more data using jQuery Ajax and PHP from database

Let start the tutorial. Assume that, we have a 

1
tutorials

 table at the database. Now we will display all the tutorials list and except the pagination links we will use Show More technique.

At first create a 

1
tutorials

 table at the database.

CREATE TABLE `tutorials` (  
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
    `created` datetime NOT NULL,  
    `modified` datetime NOT NULL,  
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',  
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

db_config.php File

This file is used for connect the database and select table.

//db details
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'test';

//connect and select db
$con = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

index.php File

JavaScript Code:
Before begging the JavaScript code we need to include the jQuery library. 

1
$(document).on('click','.show_more',function(){})

 is used alternatively of 

1
.live()

 function, because jQuery 

1
.live()

 function is deprecated. 

1
$(this).attr('id')

 is used for get the last tutorial ID.

$(document).ready(function(){
    $(document).on('click','.show_more',function(){
        var ID = $(this).attr('id');
        $('.show_more').hide();
        $('.loding').show();
        $.ajax({
            type:'POST',
            url:'ajax_more.php',
            data:'id='+ID,
            success:function(html){
                $('#show_more_main'+ID).remove();
                $('.tutorial_list').append(html);
            }
        }); 
    });
});

PHP Code:
At first we have included the database configuration file (

1
db_config.php

). We will get the results from 

1
tutorials

table in descending order and display the tutorial list.

<div class="tutorial_list">
    <?php
    //include database configuration file
    include('db_config.php');
    
    //get rows query
    $query = mysqli_query($con, "SELECT * FROM tutorials ORDER BY id DESC LIMIT 2");
    
    //number of rows
    $rowCount = mysqli_num_rows($query);
    
    if($rowCount > 0){ 
        while($row = mysqli_fetch_assoc($query)){ 
            $tutorial_id = $row['id'];
    ?>
        <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row["title"]; ?></h2></a></div>
    <?php } ?>
    <div class="show_more_main" id="show_more_main<?php echo $tutorial_id; ?>">
        <span id="<?php echo $tutorial_id; ?>" class="show_more" title="Load more posts">Show more</span>
        <span class="loding" style="display: none;"><span class="loding_txt">Loading....</span></span>
    </div>
    <?php } ?>
</div>

ajax_more.php File

This file contains some PHP code. We will get the last displayed result ID and display results from tutorials table where ID < last displayed data ID.

<?php
if(isset($_POST["id"]) && !empty($_POST["id"])){

//include database configuration file
include('db_config.php');

//count all rows except already displayed
$queryAll = mysqli_query($con,"SELECT COUNT(*) as num_rows FROM tutorials WHERE id < ".$_POST['id']." ORDER BY id DESC");
$row = mysqli_fetch_assoc($queryAll);
$allRows = $row['num_rows'];

$showLimit = 2;

//get rows query
$query = mysqli_query($con, "SELECT * FROM tutorials WHERE id < ".$_POST['id']." ORDER BY id DESC LIMIT ".$showLimit);

//number of rows
$rowCount = mysqli_num_rows($query);

if($rowCount > 0){ 
    while($row = mysqli_fetch_assoc($query)){ 
        $tutorial_id = $row["id"]; ?>
        <div class="list_item"><a href="javascript:void(0);"><h2><?php echo $row[“title”]; ?></h2></a></div>
<?php } ?>
<?php if($allRows > $showLimit){ ?>
    <div class="show_more_main" id="show_more_main<?php echo $tutorial_id; ?>">
        <span id="<?php echo $tutorial_id; ?>" class=“show_more” title="Load more posts">Show more</span>
        <span class="loding" style="display: none;"><span class="loding_txt">Loading….</span></span>
    </div>
<?php } ?>  
<?php 
    } 
}
?>

CSS code

The following CSS code is used in our script, to design the tutorials list and show more link.

.tutorial_list{ 
margin-bottom:20px;
}
div.list_item {
border-left: 4px solid #7ad03a;
padding: 1px 12px;
background-color:#F1F1F1;
-webkit-box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
}
div.list_item {
margin: 5px 15px 2px;
}
div.list_item p {
margin: .5em 0;
padding: 2px;
font-size: 13px;
line-height: 1.5;
}
.list_item a {
text-decoration: none;
padding-bottom: 2px;
color: #0074a2;
-webkit-transition-property: border,background,color;
transition-property: border,background,color;-webkit-transition-duration: .05s;
transition-duration: .05s;
-webkit-transition-timing-function: ease-in-out;
transition-timing-function: ease-in-out;
}
.list_item a:hover{ 
text-decoration:underline;
}
.show_more_main {
margin: 15px 25px;
}
.show_more {
background-color: #f8f8f8;
background-image: -webkit-linear-gradient(top,#fcfcfc 0,#f8f8f8 100%);
background-image: linear-gradient(top,#fcfcfc 0,#f8f8f8 100%);
border: 1px solid;
border-color: #d3d3d3;
color: #333;
font-size: 12px;
outline: 0;
}
.show_more {
cursor: pointer;
display: block;
padding: 10px 0;
text-align: center;
font-weight:bold;
}
.loding {
background-color: #e9e9e9;
border: 1px solid;
border-color: #c6c6c6;
color: #333;
font-size: 12px;
display: block;
text-align: center;
padding: 10px 0;
outline: 0;
font-weight:bold;
}
.loding_txt {
background-image: url(loading_16.gif);
background-position: left;
background-repeat: no-repeat;
border: 0;
display: inline-block;
height: 16px;
padding-left: 20px;
}

 

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestPrint this pageEmail this to someone

Leave a Reply

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