pagination for PHP - MySQL - next pages dont show results

PCurry · February 11, 2011

Hi,

my page is currently up at brewhas.org/transactions.php. When I retrieve a set of results > the defined # per page (such as entering 'WI' for last name), I see the first page correctly, and see the links to next pages, but when I go to the next page there are no results displayed. Same for p.3, etc.

So I've looked around enough to know that I need to pass a variable to the subsequent pages, either via a session variable or the URL. I have 2 fields input on the form, as well as a limit and offset. My questions are 1) do I need to pass all 4 and if so then should I use a session variable so the URL does not get too long, and 2) can anyone provide any help with setting session variables and including them to be passed?

I'm a newbie and have grabbed some code where I can but this is where i'm stuck. thanks in advance.

         <?php
               include 'config.php';
               include 'opendb.php';
               $rows_per_page = 20;

                  //Get the values from the text boxes         
                  $fnamesearch = $_POST["fnamesearch"];
                  $lnamesearch = $_POST["lnamesearch"];

                  //These trim and convert the name fields to upper case.
                  $fnamesearch=strtoupper($fnamesearch);
                  $lnamesearch=strtoupper($lnamesearch);
                  $fnamesearch=trim($fnamesearch);
                  $lnamesearch=trim($lnamesearch);
            
                  // Count how many rows are coming back for the query
                  //This checks to see if there are at least 2 chars in the last name.
                  if (strlen($lnamesearch)<2)
                     {
                        echo "<p class='style7'>Please enter at least 2 letters of the last name.</p>";
                        exit;
                     }
                  else  //sets the query to get the number of rows
                     {
                        $query = "SELECT COUNT(*) FROM BKL_TRANSACTIONS WHERE((PLAYER_LNAME LIKE '$lnamesearch%')& (PLAYER_FNAME LIKE '$fnamesearch%'))";
                     }

                  $result = mysql_query($query) or die ("Could not execute the query");   //executes the get count query
                  $query_data = mysql_fetch_row($result);
                  $numrows = $query_data[0];
                  echo "count: ".$numrows;   
                  $lastpage = ceil($numrows/$rows_per_page);      //determines how many pages based on rows divided by rows per page
                  echo "pages: ".$lastpage;

                  // Get required page number.  If not present, default to 1.
                  if (isset($_GET['pageno']) && is_numeric($_GET['pageno'])) 
                     {$pageno = $_GET['pageno'];
                     } 
                  else 
                     {$pageno = 1;
                     } 
                  
                  //Checks that the value of $pageno is an integer between 1 and $lastpage.
                  $pageno = (int)$pageno;
                  if ($pageno > $lastpage) 
                     {$pageno = $lastpage;
                     } 
                  if ($pageno < 1) 
                     {$pageno = 1;
                     }                  
               
                  //constructs OFFSET for the sql SELECT statement
                  $offset = ($pageno - 1) * $rows_per_page;
                  
                  //This checks to see if there are at least 2 chars in the last name.
                  if (strlen($lnamesearch)<2)
                     {
                        echo "<p class='style7'>Please enter at least 2 letters of the last name.</p>";
                        exit;
                     }
                  else  //sets the query to get the number of rows
                     {
                        $query = "SELECT * FROM BKL_TRANSACTIONS WHERE((PLAYER_LNAME LIKE '$lnamesearch%')& (PLAYER_FNAME LIKE '$fnamesearch%')) LIMIT $offset, $rows_per_page";
                     }
      
                  $result = mysql_query($query) or die ("Could not execute the query");   //executes the query
                  //Count the number of results 
                  $numrows=mysql_num_rows($result);
                  
                  echo "<p class='style7'>Your search: "".$fnamesearch." ".$lnamesearch."" returned <b>".$numrows."</b> results.</p>";
                  
                  if ($numrows == 0)  //if no matches, don't display the table 
                        exit;
                  else            //build the table and insert rows 
                     {
                              echo "<table border=1 cellspacing=0 cellpadding=1 width='77%' align=left bordercolor=#666666>";
                              echo "<tr bgcolor=#cccc99 class='style5'><th width='10%' class='style5'>Date</th>";
                              echo "<th width='10%' class='style5'>Action</th>";
                              echo "<th width='12%' class='style5'>From</th>";
                              echo "<th width='12%' class='style5'>To</th>";
                              echo "<th width='5%' class='style5'>Pos</th>";
                              echo "<th width='18%' class='style5'>Name</th>";      
                              echo "<th width='5%' class='style5'>Round</th></tr>";                     
      
                           while ($row = mysql_fetch_array($result))
                           {
                              echo "<tr><td width='10%' class='style6'>".$row['TRANS_DT']."</td>";
                              echo "<td width='10%' class='style6'>".$row['TRANS_ACTION']."</td>";
                              echo "<td width='12%' class='style6'>".$row['FROM_OWNER']."</td>";
                              echo "<td  width='12%' class='style6'>".$row['TO_OWNER']."</td>";
                              echo "<td  width='5%' class='style6'>".$row['POSITION']."</td>";
                              echo "<td  width='18%' class='style6'>".$row['PLAYER_FNAME']." ".$row['PLAYER_LNAME']."</td>";
                              echo "<td  width='5%' class='style6'>".$row['DRAFT_RND']."</td></tr>";
                           }
               
                           /******  build the pagination links ******/   
                           // if not on page 1, don't show back links
                           if ($pageno > 1) {
                              // show << link to go back to page 1
                              echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'><<</a> ";
                              // get previous page num
                              $prevpage = $pageno - 1;
                              // show < link to go back to 1 page
                              echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'><</a> ";
                           }
                           // range of num links to show
                           $range = 3;

                           // loop to show links to range of pages around current page
                           for ($x = ($pageno - $range); $x < (($pageno + $range)  + 1); $x++) {
                              // if it's a valid page number...
                              if (($x > 0) && ($x <= $lastpage)) {
                                // if we're on current page...
                                if ($x == $pageno) {
                                  // 'highlight' it but don't make a link
                                  echo " [<b>$x</b>] ";
                                // if not current page...
                                } else {
                                  // make it a link
                                  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$x'>$x</a> ";
                                } // end else
                              } // end if 
                           }
                           // if not on last page, show forward and last page links        
                        if ($pageno != $lastpage) {
                           // get next page
                           $nextpage = $pageno + 1;
                           // echo forward link for next page 
                           echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>></a> ";
                           // echo forward link for lastpage
                           echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>>></a> ";
                        } // end if
                        /******  end build pagination links ******/               
                        echo "</table>";
                     }      
                  mysql_free_result($result);    //release the result set from the table
                  mysql_close($conn);          //close the connection to the db
               ?>

MOD EDIT:

 . . .

tags added.

Pikachu2000 · February 11, 2011

When posting code, please enclose it within the forum's

 . . .

BBCode tags.

PCurry · February 11, 2011

When posting code, please enclose it within the forum's
 . . . 
BBCode tags.

gotcha, thanks for the info.

litebearer · February 11, 2011

Being a firm believer in not reinventing the wheel, I have found this pagination class easy to implement and VERY good --- http://phpsense.com/php/php-pagination-script.html

Sample usage --- http://www.nstoia.com/sat/disp_pag/four.php

PCurry · February 11, 2011

thanks litebearer - that does look like a much cleaner and easy to plug in solution.

so if I define the "pager"

$pager = new PS_Pagination($conn, $sql, 10, 5, "param1=valu1&param2=value2";

then this call to the paginate function

$rs = $pager->paginate();	if(!$rs) die(mysql_error());

replaces the standard

$result = mysql_query($sql) or die ("Could not execute the query");

correct?

PCurry · February 11, 2011

So I think I have implemented this - part of the way (brewhas.org/transactions.php).

I have two text boxes on my initial page to get a fname and lname, and they are part of my WHERE clause. I set those with

	$fnamesearch = $_POST["fnamesearch"];
$lnamesearch = $_POST["lnamesearch"];

(I removed validation to check for 2 chars for now).

and I am passing those as parms in the URL (i.e. http://brewhas.org/transactions.php?page=2&param1=F&param2=ROD)

but on that second page, it doesn't seem to be part of the WHERE clause (results go from 25 to 6440 = all recs in the db).

do I need to do something like if it's p1 get them as above, if subsequent pgs do a get from the URL?

litebearer · February 11, 2011

Here is stripped down version of the example page. It should help you implement that class...

<?php
session_start();

$wurl00 = "../../easy_family/easy_album/";
$db = $wurl00 . "db.php";
$_SESSION['wurl'] = $wurl00;

/* Include the PS_Pagination class DO NOT ALTER */
include('ps_pagination.php');

/* Connect to database - MODIFY TO FIT YOUR NEEDS */
include($db);


/* Create query for data - MODIFY TO FIT YOUR NEEDS */
$sql = 'SELECT * FROM album ORDER BY when2 DESC';

/* Create PS_Pagination object */
$mmc = 4; /* THIS IS THE MAXIMUM NUMBER OF TABLE CELLS TO DISPLAY PER PAGE */
$cpr = 2; /* THIS IS THE CELLS PER ROW */
/* MAKE SURE THE THAT $mmc is EVENLY DIVISABLE BY $cpr */

/* the number 8 in the next line refers to the number of page links to show in the naviagtion */
$pager = new PS_Pagination($conn, $sql, $mmc, 8, "param1=valu1&param2=value2");
$pager->setDebug(true);
$rs = $pager->paginate();
if(!$rs) die(mysql_error());
/* End Setting up pagination */
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<TITLE>Display Data & Paginate</TITLE>
</head>
<body link="#000000" vlink="#000000" alink="#000000">

<!-- MAIN CONTENT DIV -->
<div style="position:absolute; top: 145px; left: 10px; width:900px;">
<center>
	<table>
		<?PHP
		$cell = 1;
		while($row = mysql_fetch_assoc($rs)) {
		  $title = $row['title'];
			if($cell==1) { 	echo "<tr>"; }
			?>
			<td width="120" align="center"  valign="center" height="100" style="background:url('sharedfiles/tile.jpg') no-repeat;">
				<center><a href="now2.php?sid=<?PHP echo $row['id']; ?>" target="_BLANK"><IMG src="<? echo $wurl00 . "thumbs/" . $row['image']; ?>" border="0"></center>
			</td>
			<?PHP
			$dorow = 0;
			if($cell==$cpr) { 	echo "</tr>"; $cell=0; $dorow = 1; }
			$cell ++;
		}
		if($dorow ==0) { echo "</tr>"; }
		?>
	</table>
</center>
<!-- =============================  DISPLAY NAVIGATION  ================================ -->
<center>
<?PHP
//Display the full navigation in one go
echo $pager->renderFullNav();
?>
</center>
<!--  =============================  END OF NAVIGATION ================================ -->
</div>
<!-- END OF CONTENT DIV -->
</body>
</html>

litebearer · February 11, 2011

You might consider putting the values in session variables, then they will 'carry forward' when its paginating

PCurry · February 11, 2011

Thanks - yeah that's what I got out of your prev post, so I've been working through it. But I'm a bit confused as to the timing.

So I've looked around and I see that before the HTML starts I need to start a session, and I can register the two session variables there:

session_start(); 
// store names as session data
session_register('fnamesearch');
session_register('lnamesearch');

Then on my initial pg, I have the two text boxes where I want to use the input to set those two session variables:

$fnamesearch = $_POST["fnamesearch"];
$lnamesearch = $_POST["lnamesearch"];
$_SESSION['fnamesearch'] = $fnamesearch;
$_SESSION['lnamesearch'] = $lnamesearch;

But then I'm a bit confused as to what happens on subsequent pages. When the 2nd page loads, the text boxes will be empty and won't it populate the session variables with blanks, resulting in what's happening now (basically no filter and all rows returned)?

I could add something to say if those session variables are populated then don't get the values from the text box,

if(!isset($_SESSION['lnamesearch'])){
}
else 
	{
	$fnamesearch = $_POST["fnamesearch"];
	$lnamesearch = $_POST["lnamesearch"];
	}

but then I couldn't conduct another search within the same session.

I'm sure I'm missing something, probably very easy.

litebearer · February 11, 2011

VERY close...

Yes check to see if the session variables are set and act accordingly.

IF not set display form

IF set display results AND if you want also display form so they can either use new values.

Might even pre-populate the form fields with the values from the session variables, then you could check at top of page whether session value = post value - if not get new results based upon new values.

Make sense?

PCurry · February 15, 2011

Ok, I've played around with this and stumped myself again.

so I set the session variables and started a session

<?php 
session_start(); 
// define names as session variables
$_SESSION['fnamesearch'];
$_SESSION['lnamesearch'];
?>

and I'm trying to check if the session variables are set. If they are not, I want to grab the input and set the session variables with it. If they are set, then I want to use the session variable rather than grabbing a blank from the form (at least til I hit the point where I want to conduct another search - i'll figure that out later)

	if(!isset($_SESSION['lnamesearch']))	
		{	
			//Get the values from the text boxes			
			$fnamesearch = $_POST["fnamesearch"];
			$lnamesearch = $_POST["lnamesearch"];
			echo "session was not set";
			echo "lnamesearch=".$lnamesearch;
			$_SESSION['fnamesearch'] = $fnamesearch;
			$_SESSION['lnamesearch'] = $lnamesearch;
			echo "session now=".$_SESSION['lnamesearch'];
			}
	else
		{
			$fnamesearch = $_SESSION['fnamesearch'];
			$lnamesearch = $_SESSION['lnamesearch'];
			echo "session was set"."</br>";
			echo "session =".$_SESSION['lnamesearch']."</br>";
			echo "lnamesearch=".$lnamesearch;
		}

but when i come thru this logic, I always get "session was set" but no values in either the session variable or the $lnamesearch variable. I also played around with trying to trim it and checking if it was null, but the same results.

Have I set up the session variables correctly?

PCurry · February 15, 2011

changed the session section back to a register

session_register('fnamesearch');
session_register('lnamesearch');

so it goes thru the part of the code below where the echo says the session has been set, but the session variable still does not have a value at any point (before or after this logic, before or after submitting the form.


	echo "before session var = ".$_SESSION['lnamesearch'];
	$trimmed_session = trim($_SESSION['lnamesearch']);
	echo "trimmed session = ".$trimmed_session;

	if(!isset($_SESSION['lnamesearch']))	
		{	
			//Get the values from the text boxes			
			$fnamesearch = $_POST["fnamesearch"];
			$lnamesearch = $_POST["lnamesearch"];
			echo "session was not set";
			echo "lnamesearch=".$lnamesearch;
			$_SESSION['fnamesearch'] = $fnamesearch;
			$_SESSION['lnamesearch'] = $lnamesearch;
			echo "session now=".$_SESSION['lnamesearch'];
			}
	else
		{
			$fnamesearch = $_SESSION['fnamesearch'];
			$lnamesearch = $_SESSION['lnamesearch'];
			echo "session was set"."</br>";
			echo "session =".$_SESSION['lnamesearch']."</br>";
			echo "lnamesearch=".$lnamesearch;
		}

can anyone tell what is going on?

PCurry · February 15, 2011

Never mind - got it working by getting the parms from the URL and setting the search names to those in that case.

From what I've read the downside is security but it's not like I'm dealing with international trade secrets on my site, so it should work fine.

Thanks Litebearer for pointing me to that script.

Sign In

pagination for PHP - MySQL - next pages dont show results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information