Category: MySQL

So you have a Mysql table you want to pull data from, but you don’t want to flood the page with everything in the table right! So you need some pagination to seperate all the content in the table into easy to open pages.

So let’s say this is your mysql query:

[code lang=”php”]
$sql = mysql_query(“SELECT * FROM table1”) or die(mysql_error());
[/code]

So this will grab everything from table1.

If we have a lot of rows in this table this is going to return all of them, so we need to make a pagination for all the rows returned. This requires modifying the query and adding some variables. I like to have the PER_PAGE,OFFSET, and PAGE_NUM variables defined so you can use them globally in every class and function, however you may not want the same values throughout so declare them as you wish.

Here is the header PHP for definitions:

[code lang=”php”]
// === START Pagination definitions === //
$pgNperPage=15;
$pgNpageNum=1;
if(isset($_GET[“p”])){
$pgNpageNum=(int)$_GET[“p”];
}
$pgNoffset = ($pgNpageNum – 1) * $pgNperPage;
// definitions
define(“SITE_URL”,”http://mysite.com/”,true);
define(“PER_PAGE”,$pgNperPage,true);
define(“OFFSET”,$pgNoffset,true);
define(“PAGE_NUM”,$pgNpageNum,true);
// === END Pagination definitions === //
[/code]

So we have to modify the query we use in $sql to include a limit with the offset and per page values used. I have made a pagination function which will do everything for me. We’ll pass the query along with some parameters through this function to output the new query and the pagination HTML.

Here is the function syntax:

[code lang=”php”]
pagination($query,$pageNum,$perpage,$sortable,$cat=””,$sort=””,$headers=””,$pageL=””);
[/code]

Function Paremeters:

• $query – The original query to use for the Mysql. i.e. “SELECT * FROM table1”. (No ORDER BY or LIMIT).
• $pageNum – Current page number.
• $perpage – Per page integer value.
• $sortable – Array of sortable fields in mysql table to order the results by.
• $cat – What is being paginated. i.e. (Users, videos, comments, etc.) [Optional]
• $sort – Field in Mysql table to sort it by. Default in the function definition is “timestamp” – usually used for date. [Optional]
• $headers – Additional URL headers besides the sort and page number. i.e. “&g=bf-2142” (Start with & not ?) [Optional]
• $pageL – Pagination letter. Useful if you have multiple paginations on one page. i.e. “cp”. (Default is p.) [Optional]

Here is the pagination function definition:

[code lang=”php”]
function pagination($query,$pageNum,$perpage,$sortable,$cat=””,$sort=””,$headers=””,$pageL=””){
$pagination = array();
$paginationDetails = “”;
$adjacents = 3;
if(!$perpage){
$perpage = 15; // default
}
if($cat){
$cat = ” “.$cat.””;
}
$pageNum = (int)$pageNum;
if(!$pageNum){
$pageNum=1;
}
if(!$pageL){
$pageL=”p”;
}
$pgNoffset = ($pageNum – 1) * $perpage;

if(isset($_GET[‘dsc’]) && $_GET[‘dsc’] != “”){
$dsc = “DESC”;
$urldsc = “&dsc=1”;
} else {
$dsc = “”;
$urldsc = “”;
}
if(is_array($sortable)){
if(in_array($sort,$sortable)){
$sort = trim($sort);
} else {
$sort = “”;
}
}
if($sort){
$order_by = “ORDER BY “.$sort.” “.$dsc.””;
} else {
$order_by = “”;
}
$limit = “LIMIT “.$pgNoffset.”,”.$perpage.””;
$queryNew = $query.’ ‘.$order_by.’ ‘.$limit;
$pagination[] = $queryNew;
if(strpos($_SERVER[“REQUEST_URI”],”?”)){
$pos = strpos($_SERVER[“REQUEST_URI”],”?”);
} else {
$pos = strlen($_SERVER[“REQUEST_URI”]);
}
$pageURL = substr($_SERVER[“REQUEST_URI”],0,$pos);

$pageURL .= “?sort=”.$sort.””;
$pageURL .= “&dsc=”.$dsc.””;
$pageURL .= $headers;

$sql = mysql_query($queryNew) or die(mysql_error());
$count = mysql_num_rows($sql);
if($count > 0){
$totalQuery = mysql_query($query) or die(mysql_error());
$totalCount = mysql_num_rows($totalQuery);
$total = ceil($totalCount / $perpage);
$pm1 = $total – 1;
$paginationDetails .= ‘

‘;
$pagination[] = $paginationDetails;
}
return $pagination;
}
[/code]

Now we just need to include the HTML and CSS classes for the pagination so viewers can navigate through the pages returned from this query.

Pagination CSS:

[code lang=”css”]
.pagination,.pagination div {
background-color: #eee;
font: 11px tahoma;
border: 1px solid #ccc;
text-align: left;
}
.a_td {
color: #343434;
background-color: #fff;
font-size: 11px;
font-family: tahoma;
}
.a_page {
color: #343434;
background-color: #fff;
font-size: 11px;
font-family: tahoma;
padding:0px;
}
.a_page:hover {
background-color: #eee;
}
.a_page a {
display:block;
padding:2px;
color: #333;
text-decoration: none;
}
.page_selected {
background-color: #333;
color: #fff;
font-weight: bold;
font-size: 11px;
font-family: tahoma;
}
.page_selected a {
color: #000;
}
[/code]

Now we have the function included on our page along with the necessary CSS styles. Now we can call the function and echo the contents of the returned array. The function pagination returns an array like so: pagination = [newMysqlQuery, paginationHTML].

So let’s say this was our function for downloads:

[code lang=”php”]
function downloads($g){
$g=mysql_real_escape_string($g); // Game
$validSorts = array(“title”,”catType”,”dl_count”,”timestamp”); // Sorts
if(isset($_GET[‘sort’]) && in_array($_GET[‘sort’],$validSorts)){
$sort = mysql_real_escape_string($_GET[‘sort’]);
} else {
$sort = ‘timestamp’; // default sort used.
}
if(isset($_GET[‘dsc’])){
$desc = “DESC”;
} else {
$desc = “”;
}
if(isset($_GET[‘p’])){
$page = (int)$_GET[‘p’];
} else {
$page = 1;
}
$query = “SELECT * FROM downloads WHERE gameCat = ‘$g'”;
$pagination = pagination($query,$page,15,$validSorts,”downloads”,$sort,”&g=”.$g.””); // Call the function.
$sql = mysql_query($pagination[0]) or die(mysql_error()); // $pagination[0] = new query.
$count = mysql_num_rows($sql);
}
[/code]

Now we have called the pagination function with some variables for displaying our downloads. Along with printing the pagination HTML you could display the rows with the new Mysql query that the function returns.

See how to display Mysql Rows here!

For this example we will simply echo the pagination HTML:

[code lang=”php”]
echo $pagination[1];
[/code]

Check this function out LIVE here:

http://bfgamerz.com/members.php