Load More Data from Database using jQuery Ajax PHP MySQL

Youtube, Twitter, and Facebook uses Load More Data technique to list dynamic data in a user-friend way. They allow 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 tutorial, we will show you how to build a similar technique for load more data on click from the database using jQuery, Ajax, and PHP.

The example code builds a simple Load More Data functionality using jQuery and Ajax. For the demonstration purpose, we will retrieve the posts data from the MySQL database and list in the web page. Instead of adding the pagination link to the list, we will integrate Show More technique to load posts data dynamically using jQuery, Ajax, PHP, and MySQL.

Our Ajax based load more results with jQuery tutorial make the whole process simple. Let’s start the load more data from database tutorial.

Create Database Table

To store the post information a table needs to be created in the database. The following SQL creates an posts table with some basic fields in the MySQL database.

CREATE TABLE `posts` (
 `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` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active, 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The dbConfig.php file is used to connect and select the MySQL database. Specify the database hostname ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL credentials.

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

Data List with Show More (index.php)

In this page, the posts data will be retrieved from the MySQL database and listed with the Load More button in the web page.

HTML & PHP Code:
At first include the database configuration file (dbConfig.php). Now, fetch some limited data from the posts table and list them with Show More button.

<div class="postList">
    <?php
    // Include the database configuration file
    include 'dbConfig.php';
    
    // Get records from the database
    $query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT 2");
    
    if($query->num_rows > 0){ 
        while($row = $query->fetch_assoc()){ 
            $postID = $row['id'];
    ?>
    <div class="list_item"><?php echo $row['title']; ?></div>
    <?php } ?>
    <div class="show_more_main" id="show_more_main<?php echo $postID; ?>">
        <span id="<?php echo $postID; ?>" 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>

JavaScript Code:
The jQuery is used to load more data from the database without page refresh, include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

The following jQuery uses Ajax ($.ajax) to get more data from ajax_more.php file and listed them under the postList div.

<script type="text/javascript">
$(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-without-design.php',
            data:'id='+ID,
            success:function(html){
                $('#show_more_main'+ID).remove();
                $('.postList').append(html);
            }
        });
    });
});
</script>

Load More Data (ajax_more.php)

The ajax_more.php file is called by the Ajax request and it handles load more data functionality.

• Include the database configuration file to connect and select the MySQL database.
• Count the total number of records those ID greater than last displayed data ID.
• Fetch records from posts table those ID greater than last displayed post ID.
• List the post data with Show More button.
• Render posts HTML and the view is returned to the success method of Ajax request.

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

    // Include the database configuration file
    include 'dbConfig.php';
    
    // Count all records except already displayed
    $query = $db->query("SELECT COUNT(*) as num_rows FROM posts WHERE id < ".$_POST['id']." ORDER BY id DESC");
    $row = $query->fetch_assoc();
    $totalRowCount = $row['num_rows'];
    
    $showLimit = 2;
    
    // Get records from the database
    $query = $db->query("SELECT * FROM posts WHERE id < ".$_POST['id']." ORDER BY id DESC LIMIT $showLimit");

    if($query->num_rows > 0){ 
        while($row = $query->fetch_assoc()){
            $postID = $row['id'];
    ?>
        <div class="list_item"><?php echo $row['title']; ?></div>
    <?php } ?>
    <?php if($totalRowCount > $showLimit){ ?>
        <div class="show_more_main" id="show_more_main<?php echo $postID; ?>">
            <span id="<?php echo $postID; ?>" 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 to specify the style of the posts list and show more link.

.list_item {
    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);
    margin: 5px 15px 2px;
    padding: 2px;
    font-size: 14px;
    line-height: 1.5;
}
.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.gif);
    background-position: left;
    background-repeat: no-repeat;
    border: 0;
    display: inline-block;
    height: 16px;
    padding-left: 20px;
}