Pagination

[neha_jaltare]

Hello,

I am facing one problem of pagination.

Actually I did pagination successfully,but problem is getting by where clause

I used SELECT query with WHERE clause.Using input field.

Now the problem is that "while choosing the page of that pagination I have to again choose that input fields which used in the where clause.

Here is my piece of code :

Collapse | Copy Code

<?php
   $i = 0;

                if(!empty($_POST['select2']))
                {
        foreach ($_POST['select2'] as $selectedOption)
            {
                $options[$i++] = $selectedOption;
            }
                }

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
}
      $sql ="select DISTINCT date,mobno,city,state,type,telecaller,time FROM import";
    $query  = mysql_query($sql);
      $query_data = mysql_num_rows($query);

$numrows = $query_data;
$rows_per_page = 10;
$lastpage      = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
}
if ($pageno < 1) {
   $pageno = 1;
}
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
            $sql = "select date,mobno,city,state,type,telecaller FROM import WHERE time IN(";
        $num = count($options);
        for ($i=0; $i<$num-1; $i++)
        {
        $sql .= "'".$options[$i]."', ";
        }
        $sql .= "'".$options[$i]."')";
        $sql .= "GROUP BY mobno,telecaller ORDER BY date DESC $limit";
       // OR date1='$_POST[date]'
        //echo $sql . "<br>";
          $query  = mysql_query($sql);
            echo"<div id='pagination'>";
if ($pageno == 1) {
   echo " FIRST    PREV &nbsp &nbsp &nbsp ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> &nbsp &nbsp &nbsp ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> &nbsp &nbsp &nbsp ";
}
echo"</div>";
echo"<div id='pagination1'>";
echo " ( Page <b>$pageno</b> of $lastpage ) &nbsp &nbsp &nbsp";
echo"</div>";
echo"<div id='pagination2'>";
if ($pageno == $lastpage) {
   echo " NEXT   LAST &nbsp &nbsp &nbsp";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> &nbsp &nbsp &nbsp";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}
echo"</div>";

[memfiss]

ur input is a filter ? U can use jquery+ajax pagination , then u will save all filters or just save it into session

[spiderwell]

you can aslo adapt your script to add the extra parameters to the pagination links, and have it check for them as part of the GET array and re apply them that way

[PFMaBiSmAd]

Pagination involves 5 main parts -

 

1) A main query with a WHERE clause that matches the record set you are interested in or no WHERE clause if you are interested in all your records (a LIMIT clause is appended to this main query later to retrieve the specific records for any logical page.) Your main query would also have any JOINS, GROUP BY, ORDER BY terms, ... it needs to retrieve the data you want in the order that you want it.

 

2) A secondary query, with the same WHERE clause (or lack of) as the main query that gets a count of the total number of matching records you are interested in (used to calculate and limit the maximum logical page number.)

 

3) Get the requested logical page number (if any, if not default to page 1) and calculate the starting row number for the requested logical page.

 

4) Query for, retrieve, and display the matching records for the requested logical page.

 

5) Produce pagination links that allow any of the logical pages to be requested.

 

In order to propagate any search term/filters,... you should pass them as GET parameters in the URL so that someone can bookmark a page and be able to return to that same page later. I recommend using http_build_query when building pagination links so that the code only modifies the part of the URL query string for pagination and leaves any other GET parameters as is.

 

Sample code that does the above -

 

<?php
// settings used by this code -
$rows_per_page = 20; // how many rows to display per logical page
$pagination_name = 'pageno'; // the $_GET[xxxxx] index name to use for pagination
$pagination_range = 3; //  maximum number of pagination links to show either side of the currently selected page

// connect to your database server and select your database here...

// assuming this is being used for a search script, output a simple search form and produce a $where_clause to match the rows you are interested in
$search_form = "<form method='get' action=''>Search: <input type='text' name='search'><input type='submit'></form>";
echo $search_form;

// get and condition any search term
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$where_clause = '';
if($search != ''){
// form a simple LIKE '%search term%' comparison
$where_clause = sprintf("WHERE your_column LIKE '%%%s%%'",mysql_real_escape_string($search));
}

// define the main and count queries
$main_query = "SELECT * FROM your_table $where_clause";
$count_query = "SELECT COUNT(*) FROM your_table $where_clause";

// find the total number of matching rows
$result = mysql_query($count_query) or die("Query failed: $count_query<br />Error: " . mysql_error());
list($total_rows) = mysql_fetch_row($result);

// calculate the total number of logical pages
$total_pages = ceil($total_rows/$rows_per_page);

// get and condition or set a default for the requested page
$requested_page = isset($_GET[$pagination_name]) ? intval($_GET[$pagination_name]) : 1;

// set max/min limits for the requested page. max first, then min so if the total is zero (no matching data), the requested page is 1
if($requested_page > $total_pages){
   $requested_page = $total_pages;
}
if($requested_page < 1){
   $requested_page = 1;
}

// calculate the starting row number for the requested logical page
$offset = ($requested_page - 1) * $rows_per_page;

// form the actual query to retrieve the matching data for the requested logical page
$query = "$main_query LIMIT $offset, $rows_per_page";

// query for the actual data
$result = mysql_query($query) or die("Query failed: $query<br />Error: " . mysql_error());

// get number of rows returned by the query for the logical page
$num_rows = mysql_num_rows($result);
if($num_rows == 0){
// query matched no rows
echo "There are no matching records to display on this page.";
} else {
echo "Your query matched $total_rows record" .($total_rows > 1 ? 's' : '').". ";
echo "Displaying records: ".($offset+1)." - " . ($offset+$num_rows) . ".<br />";
// loop over the matching rows and output the data the way you want on your page
while($row = mysql_fetch_assoc($result)){
	echo $row['your_column'] . '<br />';
}
}

// build pagination navigation links (if there's more than one page)
// this code uses http_build_query to build the query string on the end of the URL so that any existing get parameters, such as a search term, are not modified. This code only modifies the pagination get parameter and leaves all other get parameters as is.
$pagination_links = ''; // build pagination links in a string (output it later in your actual content on the page)
if($total_pages > 1){
// produce 'first' and 'prev' links
if($requested_page > 1){
	// 'first' page link
	$_GET[$pagination_name] = 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= "<a href='?" . http_build_query($_GET, '', '&') . "'><<</a> ";
	// 'prev' page link
	$_GET[$pagination_name] = $requested_page - 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'><</a> ";
} else {
	// text only place holders
	$pagination_links .= " << < ";
}

// loop to produce links for a range of pages around the currently selected page
for($x = $requested_page - $pagination_range; $x < $requested_page + $pagination_range + 1; $x++){
	// if between min and max page number
	if($x > 0 && $x <= $total_pages){
		// if currently requested page, output text only place holder
		if($x == $requested_page){
			$pagination_links .= " [<b>$x</b>] ";
		} else {
			// output page link
			$_GET[$pagination_name] = $x; // set/replace the pagination GET parameter (all other GET parameters unchanged)
			$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>$x</a> ";
		}
	}
}
                 
// produce 'next' and 'last' links
if($requested_page != $total_pages){
	// 'next' page link
	$_GET[$pagination_name] = $requested_page + 1; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>></a> ";
	// 'last' page link
	$_GET[$pagination_name] = $total_pages; // set/replace the pagination GET parameter (all other GET parameters unchanged)
	$pagination_links .= " <a href='?" . http_build_query($_GET, '', '&') . "'>>></a>";
} else {
	// text only place holders
	$pagination_links .= " > >>";
}
}

// output the pagination navigation links
echo $pagination_links; // echo the links wherever you want in the content on your page

[neha_jaltare]

PFMaBiSmAd,

Can you please explain me little bit of above coding.I don't want to use here copy and paste method.

Thanks for the reply to my question..

 

[Christian F.]

The code is really well explained by the comments already. I suggest by starting with them, and trying out the code locally. Pick at it line by line, and see if you can't decode what they do.

Then, if you still have problems, come back with a specific line/question and we'll be able to help.

[neha_jaltare]

// get and condition any search term
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$where_clause = '';
if($search != ''){
// form a simple LIKE '%search term%' comparison
$where_clause = sprintf("WHERE your_column LIKE '%%%s%%'",mysql_real_escape_string($search));
}

I don't understand these line.Why do you use it. Is it necessary?

[Christian F.]

That's what's adding the WHERE clause to the query, in case you want to limit the rows displayed. So, it will be necessary unless you only want to show all of the rows in the pagination.

 

If it's the ternary operator that's confusing you, it's basically an IF ? THEN : ELSE which returns the values of the "THEN" or "ELSE" block, depending upon the truthfulness of the IF-statement. More information about it can be found in the PHP manual.

Same goes for sprintf ().

[neha_jaltare]

But why we use sprintf function here?

 

Sorry sir , Actually I  am beginner that's why I am asking many quetions to you.

[Christian F.]

If you read the PHP manual page for it, you'll see what sprintf () does. Add that to what mysql_real_escape_string () does, and the reason should be quite obvious.

Should also be quite easy to see what it does if you print out the result of that line, and from there figuring out why shouldn't take much. I can give you a hint and say that it's for security and readability.