Load More style Pagination in PHP/MYSQLi

Author: CodexWorld
Author URL: http://www.codexworld.com/
Author Email: contact@codexworld.com
Tutorial Link: http://www.codexworld.com/load-more-data-using-jquery-ajax-php-from-database/

============ Instruction ============
Create a database (test) at phpMyAdmin => Import the “tutorials.sql” file into this database => Move “load_more_data_jquery_ajax/” directory at the localhost => Open the “index.php” file at the browser => Test functionality.

==========================================================================

Step: 1 index.php

<!DOCTYPE html>
<html lang='en-US' xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Load more data using jQuery, Ajax and PHP</title>
<style type="text/css">

.tutorial_list{ 
margin-bottom:20px;
}
div.list_item {
	border-bottom:dotted 1px #CCC;
	padding-top:10px;
	padding-bottom:10px;
}
div.list_item {
margin: 5px 0px 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: #000;
-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;
font-weight:bold;
}
.list_item a:hover{ 
text-decoration:underline;
}
.show_more_main {
margin: 15px 0px;
}
.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;
}

</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<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.php',
			data:'id='+ID,
			success:function(html){
				$('#show_more_main'+ID).remove();
				$('.tutorial_list').append(html);
			}
		});
		
	});
});
</script>
</head>

<body>

<?php
//include database configuration file
include('conn.php');
?>

        	
            <div class="tutorial_list">
			<?php
			//get rows query
			$query = mysqli_query($db, "SELECT * FROM yout_db_table ORDER BY id DESC LIMIT 6");
			
			//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">
                <div><a href="<?php echo $row['link']; ?>" target="_blank"><?php echo $row['title']; ?></a></div>
                <div><?php echo $row['sub_title']; ?></div>
                </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>


</body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

<!DOCTYPE html>

<head>

<title>Load more data using jQuery, Ajax and PHP</title>

.tutorial_list{

margin-bottom:20px;

}

div.list_item {

border-bottom:dotted 1px #CCC;

padding-top:10px;

padding-bottom:10px;

}

div.list_item {

margin: 5px 0px 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: #000;

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

font-weight:bold;

}

.list_item a:hover{

text-decoration:underline;

}

.show_more_main {

margin: 15px 0px;

}

.show_more {

background-color: #f8f8f8;

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-position: left;

background-repeat: no-repeat;

border: 0;

display: inline-block;

height: 16px;

padding-left: 20px;

}

</style>

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

}

});

</script>

</head>

<body>

<?php

//include database configuration file

include('conn.php');

<?php

//get rows query

$query = mysqli_query($db, "SELECT * FROM yout_db_table ORDER BY id DESC LIMIT 6");

//number of rows

$rowCount = mysqli_num_rows($query);

if($rowCount > 0){

while($row = mysqli_fetch_assoc($query)){

$tutorial_id = $row['id'];

<div><a href="<?php echo $row['link']; ?>" target="_blank"><?php echo $row['title']; ?></a></div>

</div>

<?php } ?>

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

</body>

</html>

Step 2: conn.php (DB connection)

connection for server:

<?php
$db = mysqli_connect("localhost","root","pass","db_name");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

<?php

$db = mysqli_connect("localhost","root","pass","db_name");

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

connection for localhost:

<?php

$db = mysqli_connect("localhost","root","pass","db_name");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
?>

<?php

$db = mysqli_connect("localhost","root","pass","db_name");

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

Step 3: ajax_more

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

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

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

$showLimit = 4;

//get rows query
$query = mysqli_query($db, "SELECT * FROM newsletters 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">
        <div><a href="<?php echo $row['link']; ?>" target="_blank"><?php echo $row["title"]; ?></a></div>
         <div><?php echo $row['sub_title']; ?></div>
        </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 
    } 
}
?>

<?php

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

//include database configuration file

include('conn.php');

//count all rows except already displayed

$queryAll = mysqli_query($db,"SELECT COUNT(*) as num_rows FROM your_db_name WHERE id < ".$_POST['id']." ORDER BY id DESC");

$row = mysqli_fetch_assoc($queryAll);

$allRows = $row['num_rows'];

$showLimit = 4;

//get rows query

$query = mysqli_query($db, "SELECT * FROM newsletters 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><a href="<?php echo $row['link']; ?>" target="_blank"><?php echo $row["title"]; ?></a></div>

</div>

<?php } ?>

<?php if($allRows > $showLimit){ ?>

<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

}

Leave a Reply Cancel reply