Jump to content

Pagination


neha_jaltare

Recommended Posts

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>";

Link to comment
Share on other sites

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

Link to comment
Share on other sites

// 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?

Link to comment
Share on other sites

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 ().

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.