gistfile1.php

<?php

// This is the database connection configuration
$db_config = array(
	'host'		=> 'localhost', // Your server
	'database'	=> 'demo', // Database name
	'username'	=> 'root', // Username
	'password'	=> '', // Password
	'charset'	=> 'utf8',
	'collation'	=> 'utf8_unicode_ci',
	'prefix'	=> '',
	'fetch'		=> PDO::FETCH_ASSOC,
);

// $db will hold the connection to the database based on the configuration above
// More about pdo here http://php.net/manual/en/book.pdo.php
$db = new PDO('mysql:host='.$db_config['host'].';charset='.$db_config['charset'].';dbname='.$db_config['database'], $db_config['username'], $db_config['password'], array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES "'.$db_config['charset'].'"', PDO::ATTR_DEFAULT_FETCH_MODE => $db_config['fetch']));

$query_total = $db->query('SELECT COUNT(*) as total FROM demo');
$query_total_result = $query_total->fetch();
$total_count = (int) $query_total_result['total']; // We may fetch 'total' because we said that the value of COUNT(*) would be shown as 'total' in the select

// We set the current page as 1 by default
$current_page = 1;
// But if the url contains ?page=X the value X will be used
if ( !empty($_GET['page']) )
	$current_page = (int) $_GET['page'];

// We set the limit as 10 by default
$limit = 10;
// But if the url contains ?per_page=X the value X will be used
if ( !empty($_GET['per_page']) )
	$limit = (int) $_GET['per_page'];


// The offset is how many results we want to ignore
// if you have pagination and you're in page 2, you dont want the results from the first page,
// so if you have 10 items per page, you define an offset of 10, and a limit of 10, that will return the results from page 2
$offset = 0;
// At first the offset is 0, we want the first results, but if we are on another page, we need to calculate the offset
if ($current_page > 0)
	$offset = ( $limit * $current_page ) - $limit;

// Preform the query with the designated offset and limit
$query = $db->query(sprintf('SELECT * FROM demo LIMIT %d, %d', $offset, $limit));

// The variable $query now contains your result
// There are two ways to loop through the result

// Using While
if ( $total_count > 0 ) {

	// This means, while the $query->fetch() returns something, keep looping and assign the returned result to the variable $row
	while ( $row = $query->fetch() ) {
		// your code
		// $row['id']
	}

}

// Using Foreach
if ( $total_count > 0 ) {

	$rows = $query->fetchAll(); // $rows will contain the complete result

	foreach ( $rows as $row ) {
		// your code
		// $row['id']
	}

}