PHP - Pagination Of Results From Php/mysql: What Is The Best Method?

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.
Code: [Select]
         <?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: &quot;".$fnamesearch." ".$lnamesearch."&quot; 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: [code] . . . [/code] tags added.

HI,

Please forgive my inexperience I am new to php and any help is most apreciated.

I have a database called liveproj and a table named olde_history.

Within the table, Project history is stored. Each project has a unique number ie 10383 etc.
I am trying to display the results for an individual project once the project has been selected.

The structure of the table is as such:

$sql = 'CREATE TABLE IF NOT EXISTS `liveproj`.`olde_history` (
  `History_Id` int(6) NOT NULL AUTO_INCREMENT,
  `History_Project_Id` varchar(10) NOT NULL,
  `History_Department_Id` varchar(25) NOT NULL,
  `History_By` varchar(25) DEFAULT NULL,
  `History_Contact` varchar(25) DEFAULT NULL,
  `History_Date` varchar(10) NOT NULL,
  `History_Description` varchar(500) DEFAULT NULL,
  `History_Action_Owner` varchar(25) DEFAULT NULL,
  `History_Status_Id` varchar(10) DEFAULT NULL,
  `History_Action_Date` varchar(10) DEFAULT NULL,
  `History_Action_Description` varchar(500) DEFAULT NULL,
  `History_Date_Resolved` varchar(10) DEFAULT NULL,
  `History_Email` varchar(150) DEFAULT NULL,
  `History_Minutes` varchar(150) DEFAULT NULL,
  `History_Visit` varchar(150) DEFAULT NULL,
   PRIMARY KEY (`History_Id`)
)';

A snippet of the table structure
I can View all information based on all projects and this works fine althoughh I only wish to display the relevent info for a selected project.

The error code I get is as follows:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10,10' at line 1

My code is:


Code: [Select]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Live Project List</title>
<link href="../css/Style.css" rel="stylesheet" type="text/css" />
<style type="text/css"></style>
</head>
<body id="body">

<div align="center" id="Section">Project History List
  <div id="apDiv1">
    <form id="form1" name="form1" method="post" action="">
      <input type="text" name="Search" id="Search" size="20" />
      <img src="../Images/search.png" width="24" height="24" align="middle" />
    </form>
  </div>
</div>
<table class="hovertable">
  <tr>
    <th width="10%">Date:</th>
    <th width="10%">By:</th>
    <th width="30%">History Description:</th>
    <th width="5%">Action Status:</th>
    <th width="5%">Action Owner:</th>
    <th width="10%">Action Rqd Date</th>
    <th width="10%">Action Description:</th>
    <th width="10%">Date Resolved:</th>
    <th width="5%">View:</th>
    <th width="5%">Edit:</th>
  </tr>

<?php
###############################################################################
# Start of pagination script
###############################################################################

IF (ISSET($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} ELSE {
   $pageno = 1;
} // if

###############################################################################
# 2. Identify how many database rows are available
############################################################################### 

# This code will count how many rows will satisfy the current query.

session_start();
include "../scripts/connect.php";
include "../scripts/Inc/db_lp.php";
//$disc = $_SESSION['Disc'];
$quote='"';
$projno2 = $_SESSION['Project'];
$projno = "$quote$projno2$quote";

$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` = '$projno'";
//$query = "SELECT count(*) FROM `liveproj` . `olde_history` WHERE `History_Id` <> ''"; //WORKING LINE

$result = mysql_query($query) or die(mysql_error());
$query_data = MYSQL_FETCH_ROW($result);
$numrows = $query_data[0];

PRINT "NUMROWS: $numrows<br>";

###############################################################################
# 3. Calculate number of $lastpage
############################################################################### 
 
# This code uses the values in $rows_per_page and $numrows in order to identify the number of the last page.
 
$rows_per_page = 10; ######## Set the ammount of rows you wish to display per page #########
$lastpage = CEIL($numrows/$rows_per_page);

###############################################################################
# 4. Ensure that $pageno is within range
###############################################################################  

# This code checks that the value of $pageno is an integer between 1 and $lastpage.
 
$pageno = (int)$pageno;
IF ($pageno < 1) {
   $pageno = 1;
} ELSEIF ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
 
###############################################################################
# 5. Construct LIMIT clause
###############################################################################   

# This code will construct the LIMIT clause for the sql SELECT statement.
 
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
 
###############################################################################
# 6. Issue the database query
###############################################################################  

# Now we can issue the database query and process the result.

$query = "SELECT * FROM `liveproj` . `olde_history` $limit";

$result = mysql_query($query) or die(mysql_error()); 

#... process contents of $result ...
 
     WHILE ($row = MYSQL_FETCH_ARRAY($result)) {
 
echo '<tr onmouseover="this.style.backgroundColor=\'#CCCCCC\';" onmouseout="this.style.backgroundColor=\'#ffffff\';"><td width="10%">';
echo $row["History_Date"]; 
echo "</td>";
echo '<td width="10%" >';
echo $row["History_By"];
echo "</td>";
echo '<td width="30%">';
echo "" . substr($row['History_Description'],0,20) . ".....[Select View To Read More]" ; 
//echo $row["History_Description"];
echo "</td>";
echo '<td width="5%">'; 
echo $row["History_Status_Id"];
echo "</td>";
echo '<td width="5%">'; 
echo $row["History_Action_Owner"];
echo "</td>";
echo '<td width="10%">'; 
echo $row["History_Action_Date"];
echo "</td>";
echo '<td width="10%">'; 
//echo $row["History_Action_Description"]; 
echo "" . substr($row['History_Action_Description'],0,20) . ".....[Select View To Read More]" ; 
echo "</td>";
echo '<td width="10%">';
echo $row["History_Date_Resolved"]; 
echo "</td>";
echo '<td width="5%">'; 
echo "<div class=form>"; 
echo '<form id="form" name="form" method="post" action="Doc_History_Desc.php" onSubmit="return confirm(';
echo '">';
echo '<input type="hidden" name="histid" value="';
echo $row["History_Id"];
echo '"><input type="image"SRC="../Images/Icons/List-View.png" Height="22" Width="22" value="View" alt="submit" class="hidebutton" name="B1">';
//echo '"><input type="submit" value="View" name="B1">';
echo "</form>";
echo "</td>";
echo '<td width="5%">'; 
echo "<div class=form>"; 
echo '<form id="form" name="form" method="post" action=".php" onSubmit="return confirm(';
echo '">';
echo '<input type="hidden" name="histid" value="';
echo $row["History_Id"];
echo '"><input type="image"SRC="../Images/Icons/List-Edit.png" Height="22" Width="22" value="View" alt="submit" class="hidebutton" name="B1">';
//echo '"><input type="submit" value="View" name="B1">';
echo "</form>";
echo "</td>";
echo "</tr>";

} 

?>
 
</table>
<p><br />
  <br />
</p>

<?php
###############################################################################
# 7. Construct pagination hyperlinks
###############################################################################  

# Finally we must construct the hyperlinks which will allow the user to select 
# other pages. We will start with the links for any previous pages.

echo '<div align="center" id="SectionEnd">'; 
IF ($pageno == 1) {
   echo "<font size='1' color='#FFFFFF'>FIRST | PREV</font>";

} ELSE {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a>";

   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a>";
} // if
 
 
# Next we inform the user of his current position in the sequence of available pages.
 
echo " <font size='1' color='#FFFFFF'>( Page $pageno of $lastpage )</font> ";
 
# This code will provide the links for any following pages.
 
IF ($pageno == $lastpage) {
   echo "<font size='1' color='#FFFFFF'>NEXT | LAST</font>";
} ELSE {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a>";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a>";
} // if
echo'<br/>'; 
echo'<br/>'; 
echo'</div>'; 

###############################################################################
# End of pagination script
###############################################################################
?>

</body>
</html>




Thankyou in advance.

Good Evening,
I've been in a trial and error state for the past week trying to figure out how I can put on the pre-made pagination scripts I found on the web, but of no luck. I have just started programming like two weeks ago, and is still trying to understand logics and stuffs so I can get my desired output. So far, everything is doing well except for one - I am really having a hard time to incorporate pagination scripts with my code. To rank myself as a php programmer, I can say I am lower than one, and seemingly, what I want to achieve is an advanced code. Can anyone help me on what pagination code I should try to play with and incorporate with my code?

Here's my code so far:

<?PHP
include("dbconnection.php"); //Include database connection to file

$query = "SELECT * FROM records";

if(isset($_POST["btnSearch"]))

{
$query .= " WHERE last_name LIKE '%".$_POST["search"]."%' OR first_name LIKE '%".$_POST["search"]."%'OR territory LIKE '%".$_POST["search"]."%'OR job_title LIKE '%".$_POST["search"]."%'OR title LIKE '%".$_POST["search"]."%'OR employer LIKE '%".$_POST["search"]."%' ORDER BY territory ASC LIMIT 0,15"  ;

$result = mysql_query($query, $connection) or die(mysql_error());
} 

?>

<!-- Start of table -->
<table width="760" border="0" align="center" cellpadding="0" cellspacing="0">
<td>
      <table width="760" border="0" cellpadding="0" cellspacing="0">
        
<!-- Table data containing the Asia Logo -->
            <td width="199" align="center" valign="top">
                <a href="login.html">
                 <img src="asia.gif" alt="" width="152" height="58" border="0" />
                </a>
            </td>
            <!-- Table data containing Home button -->
            <td width="176" align="right" valign="bottom">
                <a href="main.php">
                 <img src="Home.jpg" width="104" height="20" border="0"/>
                </a>
            </td>
            <!-- Table data containing View Client button -->
            <td width="130" align="right" valign="bottom">
                <img src="View.jpg" width="104" height="20" border="0"/>
            </td>
            <!-- Table data containing the Add Client button -->
            <td width="146" align="right" valign="bottom">
                <a href="add_client.php">
                 <img src="Add.jpg" width="104" height="20" border="0"/>
                </a>
            </td>
            <!-- Blank table data -->
            <td width="109" align="right" valign="bottom">&nbsp;
            
            </td>
        </table>

        <!-- Table design division and body-->
        <table width="760" border="0" cellpadding="0" cellspacing="0">
     <td width="200" height="3"  bgcolor="#1B1C78">
     <img src="images/topspacerblue.gif" alt="" width="1" height="3" /></td>
    <td width="560"  bgcolor="#0076CC">
     <img src="images/topspacerlblue.gif" alt="" width="1" height="3" /></td>
    <tr>
    <td height="500" colspan="2" align="center" valign="top"  bgcolor="#F3FAFE">
            <!-- Page contents -->
            <!-- Search Query -->
            <br>
            <form name="form" action="view_client.php" method="post">
            <table width="351" border="0">
<tr>
                    <td width="137" align="left" valign="middle">
                        SEARCH RECORD:
                    </td>
                    <td width="144" align="center" valign="middle">
                        <input type="text" name="search" />
                    </td>
                    <td width="56" align="left" valign="middle">
                        <input type="submit" name="btnSearch" value="Search" />
                    </td>
</tr>
</table>
            <br>
            <!-- End of search query-->
            <!-- Start of Search Results-->
            <table border="0" cellpadding="3" cellspacing="1" bordercolor="38619E" >
                <tr>
                    <th width="80" align="center" bgcolor="#E0E8F3">Territory</th>
                    <th width="330" align="center" bgcolor="#E0E8F3">Employer</th>
                    <th width="160" align="center" bgcolor="#E0E8F3">Name</th>
                    <th width="80" align="center" valign="middle" bgcolor="#E0E8F3">&nbsp;</th>
                </tr>
<?php
if($result)
{
for($i=0; $i<mysql_num_rows($result); $i++)
   {
$id = trim(mysql_result($result, $i, "id"));
$territory = trim(mysql_result($result, $i, "territory"));
$employer = trim(mysql_result($result, $i, "employer"));
$first_name = trim(mysql_result($result, $i, "first_name"));
$last_name = trim(mysql_result($result, $i, "last_name"));
  
echo "<tr>";
   echo "<td>".$territory."</td>";
   echo "<td>".$employer."</td>";
   echo "<td>".$last_name.", ".$first_name."</td>";
   echo "<td><a href='edit_client.php?id=".$id."'>edit</a> | <a href='delete_client.php?id=".$id."'>delete</a></td>";
   echo "</tr>";
}   
}  
?>

</table>
            </form>
            <!-- End of page -->
            </td>
          </tr>
        </table>
</td>
<tr>
     <td height="38">
         <table width="760" border="0" cellpadding="0" cellspacing="0">      
         <td width="200" height="35" align="center"  bgcolor="#1B1C78" class=white>
         <a href="disclaimer.html">
             <font color="#FFFFFF">Legal Disclaimer</font>
             </a>
         </td>
         <td width="560" align="center"  bgcolor="#0076CC">
         Copyright &copy; 2006 - 2010 Asia. All rights reserved.
         </td>
</table></td>
</tr>
</table>

Thank you very much, and I am looking forward for your responses. Thanks!

Hi All,

I have a pagination script that works correctly when a user clicks on a category heading, however when I am using it to try an display search results it does not take into account the set of records to display per page, it just shows all results.


<?php


echo '<h3>Search Results</h3><br />';



$query = "SELECT count(*) from merchants WHERE name LIKE '%$_GET[q]%' OR short like '%$_GET[q]%' OR full like '%$_GET[q]%' OR keywords like '%$_GET[q]%'"; 
$row=mysql_fetch_assoc(mysql_query($query));
$total_records = $row['count(*)'];
$records_per_page = 5;
$total_pages = ceil($total_records / $records_per_page);
$page = intval($p);
if ($page < 1 || $page > $total_pages) $page = 1;
$offset = ($page - 1) * $records_per_page; 
 
$limit = " LIMIT $offset, $records_per_page";



$query = mysql_query("SELECT * FROM merchants WHERE name LIKE '%$_GET[q]%' OR short like '%$_GET[q]%' OR full like '%$_GET[q]%' OR keywords like '%$_GET[q]%' ORDER BY m_id  DESC") ; 
echo mysql_error() ;



while($row = mysql_fetch_assoc($query)){

$name = $row['name'];
$short = $row['short'];
$per = $row['percent'];
$m_id = $row['m_id'];

?>
    
    <div id="cbbox">
    <a href="viewm.php?m_id=<?php echo $m_id;?>">
     <div class="cbname">
         <?php echo $name;?> &nbsp; - Up to <?php echo $per ;?>% Cashback Available
        </div>
        <div class="cbimage">
         <img src="pimage/<?php echo $name;?>.gif" height="50px" width="100px" />
        </div>
      <div class="cbshort">
         <?php echo $short;?>
        </div>
        </a>
        <div class="cbtweet">
        <a href="http://twitter.com/home/?status=Get up to <?php echo $per;?> percent Cashback on purchase from <?php echo $name;?> http://goo.gl/PLkp"><img src="images/cbtweet.png" width="90" height="50" alt="Tweet This" /></a>
 
        </div></div>
<br /><br />
<?php
}
if($total_records > 5)
{
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a title='page $i p=$i'>Page $i -  </a>";
}
}

?>
</div>


if I echo out $total_records it displays the correct amount (7 in the case I am looking at)

Any ideas?