Ajax Scroll Paging Using jQuery, PHP and MySQL

Ajax Scroll Paging Using jQuery, PHP and MySQL

Hi guys, I have received many request for creating a new tutorial about Ajax Scroll Paging Using jQuery, PHP and MySQL. Nowadays a lot of popular websites use it, like Facebook and Twitter. So in this tutorial, I’ll show you how to do that. It’s very easy, just follow the tutorial step by step.

Download source code

1. Demonstration

2. Structure

/css : contain the CSS files
/js : contain the JavaScript files
/images : contain all images

3. Database

Create a new database named “scroll_paging”, and import the SQL script in main folder, or copy/paste the script bellow:

--
-- Database: `scroll_paging`
--

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `photo` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `photo`, `title`, `description`) VALUES
(1, 'images/demo_1.jpg', 'Sport cars', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(2, 'images/demo_2.jpg', 'Lamborghini', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(3, 'images/demo_3.jpg', 'Ferrari', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(4, 'images/demo_4.jpg', 'BMW', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(5, 'images/demo_5.jpg', 'Mercedes benz', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(6, 'images/demo_6.jpg', 'Bugatti', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(7, 'images/demo_7.jpg', 'Porsche', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(8, 'images/demo_8.jpg', 'Toyota', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(9, 'images/demo_9.jpg', 'Audi', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.'),
(10, 'images/demo_10.jpg', 'Honda', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.');

4. Files

index.php

This is the main file, it will be displayed on the first view:

<?php
// including the config file
include('config.php');
$pdo = connect();
// select 4 items ordered by id
$sql = 'SELECT * FROM items ORDER BY id ASC LIMIT 0, 4';
$query = $pdo->prepare($sql);
$query->execute();
$list = $query->fetchAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Ajax Scroll Paging Using jQuery, PHP and MySQL</title>
<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>

<body>
    <div class="container">
        <div class="header">
            <img src="images/BeWebDeveloper.png" alt="BeWebDeveloper" />
        </div><!-- header -->
        <h1 class="main_title">Ajax Scroll Paging Using jQuery, PHP and MySQL</h1>
        <div class="content">
            <ul id="items">
                <?php
                $last_id = 0;
                foreach ($list as $rs) {
                    $last_id = $rs['id']; // keep the last id for the paging
                    ?>
                    <li>
                        <h2><?php echo $rs['title']; ?></h2>
                        <img src="<?php echo $rs['photo']; ?>">
                        <p><?php echo $rs['description']; ?></p>
                    </li>
                    <?php
                }
                ?>
                <script type="text/javascript">var last_id = <?php echo $last_id; ?>;</script>
            </ul>
            <!-- this is the paging loader, now is hidden, it wiil be shown when we scroll to bottom --> 
            <p id="loader"><img src="images/ajax-loader.gif"></p>
        </div><!-- content -->    
        <div class="footer">
            Powered by <a href="http://www.bewebdeveloper.com/">bewebdeveloper.com</a>
        </div><!-- footer -->
    </div><!-- container -->
</body>
</html>

config.php

This is the config file. It contains a single function for connection to the database using PDO.

<?php
// PDO connect *********
function connect() {
	$host = 'localhost';
	$db_name = 'scroll_paging';
	$db_user = 'root';
	$db_password = '';
    return new PDO('mysql:host='.$host.';dbname='.$db_name, $db_user, $db_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}
?>

load_more.php

This file will be executed using ajax with jQuery everytime we scroll to bottom, it connect to the database and select the needed statements.

<?php
// including the config file
include('config.php');
$pdo = connect();

// get request params
$last_id = $_POST['last_id'];
$limit = 5; // default value
if (isset($_POST['limit'])) {
	$limit = intval($_POST['limit']);
}

// select items for page params
try {
	$sql = 'SELECT * FROM items WHERE id > :last_id ORDER BY id ASC LIMIT 0, :limit';
	$query = $pdo->prepare($sql);
	$query->bindParam(':last_id', $last_id, PDO::PARAM_INT);
	$query->bindParam(':limit', $limit, PDO::PARAM_INT);
	$query->execute();
	$list = $query->fetchAll();
} catch (PDOException $e) {
	echo 'PDOException : '.  $e->getMessage();
}

$last_id = 0;
foreach ($list as $rs) {
    $last_id = $rs['id'];
    echo '<li>';
    echo '<h2>'.$rs['title'].'</h2>';
    echo '<img src="'.$rs['photo'].'">';
    echo '<p>'.$rs['description'].'</p>';
    echo '</li>';
}

if ($last_id != 0) {
	echo '<script type="text/javascript">var last_id = '.$last_id.';</script>';
}

// sleep for 1 second to see loader, it must be deleted in prodection
sleep(1);
?>

script.js

This is the JavaScript file

var is_loading = false; // initialize is_loading by false to accept new loading
var limit = 4; // limit items per page
$(function() {
    $(window).scroll(function() {
        if($(window).scrollTop() + $(window).height() == $(document).height()) {
            if (is_loading == false) { // stop loading many times for the same page
                // set is_loading to true to refuse new loading
                is_loading = true;
                // display the waiting loader
                $('#loader').show();
                // execute an ajax query to load more statments
                $.ajax({
                    url: 'load_more.php',
                    type: 'POST',
                    data: {last_id:last_id, limit:limit},
                    success:function(data){
                        // now we have the response, so hide the loader
                        $('#loader').hide();
                        // append: add the new statments to the existing data
                        $('#items').append(data);
                        // set is_loading to false to accept new loading
                        is_loading = false;
                    }
                });
            }
       }
    });
});

style.css

This is the CSS file, it contain the design styles

* {
	margin: 0;
	padding: 0;
}
body {
	padding: 10px;
	background: #eaeaea;
	text-align: center;
	font-family: arial;
	font-size: 12px;
	color: #333333;
}
.container {
	width: 800px;
	height: auto;
	background: #ffffff;
	border: 1px solid #cccccc;
	border-radius: 10px;
	margin: auto;
	text-align: left;
}
.header {
	padding: 10px;
}
.main_title {
	background: #cccccc;
	color: #ffffff;
	padding: 10px;
	font-size: 20px;
	line-height: 20px;
}
.content {
	padding: 10px;
	min-height: 100px;
	text-align: center;
}
.footer {
	padding: 10px;
	text-align: right;
}
.footer a {
	color: #999999;
	text-decoration: none;
}
.footer a:hover {
	text-decoration: underline;
}
/* items ******************/
#loader {
	text-align: center;
	display: none;
}
#items { 
	list-style: none; 
	text-align: left; 
}
#items li { 
	margin: 0 0 10px 0;
	background: #f1f0f0;
	border: 1px solid #999999;
	border-radius: 5px;
	color: #333333;
}
#items li h2 {    
	font-size: 18px;
	padding: 5px;
}
#items li img {
	width: 100%;
}
#items li p {
	padding: 5px;
}
Ajax, JavaScript, PHP, Scroll Paging
comments powered by Disqus

Social Profiles

bewebdeveloper on facebook bewebdeveloper on twitter bewebdeveloper on google plus bewebdeveloper on vk bewebdeveloper on youtube bewebdeveloper on tumblr bewebdeveloper rss

Subscribe to our Newsletter

Facebook

Twitter

Google+

HTML Tutorials
CSS Tutorials

Advertising