Ok, this post is both a "gift" and a question.
A gift because my code below works perfectly and is very easy to implement for those who want to add a paging navigation bar that helps you to browse you mysql records through several pages.
Now, my question is about optimization of the request that retrieves the total amount of rows :
I think it would be wiser to retrieve the rows number without fetching the data ... which is I'm doing now ... I think.
Anyway, here is the full code for the ones who want to implement it :
A gift because my code below works perfectly and is very easy to implement for those who want to add a paging navigation bar that helps you to browse you mysql records through several pages.
Now, my question is about optimization of the request that retrieves the total amount of rows :
Code:
if($query != "") $rec_tot = mysql_num_rows(mysql_query($query));
I think it would be wiser to retrieve the rows number without fetching the data ... which is I'm doing now ... I think.
Anyway, here is the full code for the ones who want to implement it :
Code:
The function itself :
<?php
function hd_mysql_pager($action, $query, $page_span, $rec_span, $page_vars, $class_on, $class_off) {
$page_num = 1;
if($_GET['page_num']) $page_num = $_GET['page_num'];
// add LIMIT in query
if($action == "LIMIT") {
$limit_string = " LIMIT " . (($page_num * $rec_span) - $rec_span) . "," . $rec_span;
return $limit_string;
// build NAV
} else {
if($query != "") $rec_tot = mysql_num_rows(mysql_query($query));
// display if more than one page
if($rec_tot > $rec_span) {
$page_vars = explode(" . ", $page_vars);
$get_string = "?";
for($i=0; $i < sizeof($page_vars); $i++ ) {
if($_GET[$page_vars[$i]] != "") $get_string .= $page_vars[$i] . "=" . $_GET[$page_vars[$i]] . "&";
}
$url_string = $_SERVER['PHP_SELF'] . $get_string . "page_num=";
$page_tot = ceil($rec_tot / $rec_span);
if($page_num > $page_span) {
$loop_start = (floor((($page_num - 1) * 0.1)) * 10) + 1;
$loop_end = ($loop_start + $page_span) - 1;
$split_prev = $loop_start -1;
$nav_string .= "<a href=\"" . $url_string . "1" . "\" class=\"" . $class_off . "\">[ DEBUT ]</a> ";
$nav_string .= "<a href=\"" . $url_string . $split_prev . "\" class=\"" . $class_off . "\">[ << ]</a> ";
} else {
$loop_start = 1;
$loop_end = $page_span;
}
if($page_num > 1) $nav_string .= "<a href=\"" . $url_string . ($page_num -1) . "\" class=\"" . $class_off . "\">[ < ]</a> ";
for($i = $loop_start; $i <= $loop_end; $i++){
if($i <= $page_tot) {
if(($page_num) == $i){
$nav_string .= "<span class=\"" . $class_on . "\"> [ " . $i . " ] </span> ";
} else {
$nav_string .= "<a href=\"" . $url_string . $i . "\" class=\"" . $class_off . "\">[ $i ]</a> ";
}
}
}
if($page_num < $page_tot) {
$nav_string .= "<a href=\"" . $url_string . ($page_num + 1) . "\" class=\"" . $class_off . "\">[ > ]</a> ";
if($page_tot > $page_span) {
$nav_string .= "<a href=\"" . $url_string . $i . "\" class=\"" . $class_off . "\">[ >> ]</a> ";
$nav_string .= "<a href=\"" . $url_string . $page_tot . "\" class=\"" . $class_off . "\">[ FIN ]</a> ";
}
}
}
return $nav_string;
}
}
?>
How to use it in a page :
<?php
// - ! - show records
$sql_query[1] = "SELECT ";
$sql_query[1] .= "table_1.*, ";
$sql_query[1] .= "table_2.* ";
[...]
$sql_query[1] .= "DESC";
$sql_limit[1] = hd_mysql_pager("LIMIT", NULL, 10, 5, NULL, NULL, NULL);
$sql_result[1] = mysql_query($sql_query[1] . $sql_limit[1],$db_connect);
while ($row = mysql_fetch_array($sql_result[1])) {
[...]
}
// - ! - show paging nav
echo hd_mysql_pager(NULL, $sql_query[1], 10, 5, NULL, "css_class1", "css_class2");
?>