Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Code for you + Question

Status
Not open for further replies.

Sleidia

Technical User
May 4, 2001
1,284
FR
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 :

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");    
    
?>
 
basic rule is never ask a database server to return more information than you need.

in the first instance all you need is the total rows. so your queries should be (in specie):
Code:
$where = "";//where part of the sql
$count_query ="Select count(*) as cnt where $where;
$data_query ="Select * from $table where $where offset $offset limit $limit order by $order ASC";

apologies if i have missed the point - i did not read your code in full.
 

Ok but this gets very tricky when you want to do a SELECT on several tables if you use COUNT() within the function :(

In its former state, I did use COUNT() because I always used this function with one single table.
But I had to modify it so that I could query multiple tables.

Thanks for your post anyway.
 
when you say you are querying multiple tables i assume you mean with a single query (i.e. join or union etc). if so then the same rules apply.

if not- then i don't see how a page slider mechanism works with non-contiguous data.
 
I have not so much a technical piece of advice as a piece of practical advice:

If your code is worth saving, post it as a FAQ. If you post it in a thread, within a week, it will never be seen again.

Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top