All posts tagged mysql

From many days i am thinking to make a pagination script using php and jquery. Today i am making a pagination script for you. How to implement Pagination with jQuery, PHP and MySQL. It is a simple tutorial. It’s looks big but very simple script. Take a look at this screen shot.

The tutorial contains three PHP files and two js files and one css file includes jQuery plugin.

-config.php (Database Configuration)
-pagination.php
-pagination_data.php
-jquery.js
-jquery_pagination.js
–style.css

We also include the database script in the download zip file with data so that you can run this code and check.

[smProductImageAdd src="http://learneveryday.net/codecanyon/adverticement/add_codecnayon_smart-social-share-asp.net.png" alt= "Smart Social Share" href="http://codecanyon.net/item/php-smart-social-share/202558" title="Smart Social Share (Php Plugin)" description="Smart Social share is a php plugin . Which helps you to give user to share your content with social book mark site. There are 4 plugin in this package." ref="marifdu"]

Database Table

CREATE TABLE IF NOT EXISTS `northwind_products` (
  `ProductID` int(1) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(256) NOT NULL,
  `UnitPrice` decimal(4,2) NOT NULL,
  `UnitsInStock` int(10) NOT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=78 ;

jquery_pagination.js
Contains javascript this script works like a data controller.

$(document).ready(function()
{
//Data Loading Image
function Data_Load()
{
$("#loading").fadeIn(900,0);
$("#loading").html('<img src="bigLoader.gif" />');
}
//Hide Loading Image
function Hide_Load()
{
$("#loading").fadeOut('slow');
};

//Default Starting Page Results
$("#pagination li:first")
.css({'color' : '#FF0084'}).css({'border' : 'none'});
Data_Load();
$("#content").load("pagination_data.php?page=1", Hide_Load());

//Pagination Click
$("#pagination li").click(function(){
Data_Load();
//CSS Styles
$("#pagination li")
.css({'border' : 'solid #dddddd 1px'})
.css({'color' : '#0063DC'});

$(this)
.css({'color' : '#FF0084'})
.css({'border' : 'none'});

//Loading Data
var pageNum = this.id;
$("#content").load("pagination_data.php?page=" + pageNum, Hide_Load());
});

});

config.php
You have to change hostname, username, password and databasename.

<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "learneveryday_tutorial";
$db = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die('Error connection to database server: ' . mysql_error());
mysql_select_db($mysql_database, $db)
or die('Error connection to database server: ' . mysql_error());
?>

pagination.php
User interface page.

<?php
include('config.php');
$per_page = 10;

//Calculating no of pages
$sql = "select count(*) from northwind_products";
$result = mysql_query($sql);
$count = mysql_fetch_row($result);
$pages = ceil($count[0]/$per_page);

?>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.0/jquery.min.js"></script>
<script type="text/javascript" src="jquery_pagination.js"></script>
<link rel="stylesheet" href="style.css" type="text/css" />

<div id="loading" ></div>
<div id="content" ></div>
<ul id="pagination">
<?php
//Pagination Numbers
for($i=1; $i<=$pages; $i++)
{
echo '<li id="'.$i.'">'.$i.'</li>';
}
?>
</ul>

pagination_data.php
Simple php script display data from the messages table.

<?php
include('config.php');
$per_page = 10;
if($_GET)
{
$page=$_GET['page'];
}

$start = ($page-1)*$per_page;
$sql = "select * from northwind_products order by ProductID limit $start,$per_page";
$result = mysql_query($sql);
?>
<table width="800px">
<?php
while($row = mysql_fetch_array($result))
{
$ProductID=$row['ProductID'];
$ProductName=$row['ProductName'];
?>
<tr>
<td><?php echo $ProductID; ?></td>
<td><?php echo $ProductName; ?></td>
</tr>
<?php
}
?>
</table>

CSS Code
CSS code for page numbers.

#loading
{
width: 100%;
position: absolute;
}
li
{
list-style: none;
float: left;
margin-right: 16px;
padding:5px;
border:solid 1px #dddddd;
color:#0063DC;
}
li:hover
{
color:#FF0084;
cursor: pointer;
}

You can download the full code from bellow link.