MYSQL display with next page and Order by collum

[$username]

MYSQL display with next page and Order by column

 

I have made the code and it works but when I am on page 2 and then select a column it will go back to page 1.

Is there a way I can make it so I can still be in page 2 and also order by column?

 

Here is the code.

 

<?php
// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$Ord = $_GET['Ord'];
if ($Ord == "") 
$Ord = 'ID';
else
$Ord = $_GET['Ord'];
$sql = "SELECT * FROM store ORDER BY `$Ord`"; 
//$query = mysql_query($sql);

$query = " SELECT * FROM store ORDER BY '$Ord'" .
         " LIMIT $offset, $rowsPerPage ";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($row = mysql_fetch_array($result))
{
   //echo $row['ID'] . '<br>';
   echo "<tr>";
echo("<td><a href=\"updateinfo.php?ID=" .  $row["ID"] . "\" title=\"".$row["ID"]."\">" . $row["ID"] .  "</a></td>"); 
//echo "<td>".$row['ID']."</td>";
echo "<td>".$row['FirstName']."</td>";
echo "<td>".$row['LastName']."</td>";
echo "<td>".$row['AddressStreet']."</td>";
echo "<td>".$row['AddressCity']."</td>";
echo "<td>".$row['AddressState']."</td>";
echo "<td>".$row['AddressZip']."</td>";
echo "<td>".$row['Paid']."</td>";
echo "<td>".$row['Amount']."</td>";
echo "<td>".$row['CaseNotes1']."</td>";
echo "<td>".$row['CaseNotes2']."</td>";
echo "<td>".$row['CaseNotes3']."</td>";
echo "<td>".$row['CaseNotes4']."</td>";
echo "<td>".$row['OtherInfo']."</td>";
echo "<td>".$row['ServBy']."</td>";
echo "<td>".$row['ClientName']."</td>";
echo "<td>".$row['County']."</td>";
echo "<td>".$row['ServDate']."</td>";
echo "</tr>";
}


// ... more code here
// ... the previous code

// how many rows we have in database
$query   = "SELECT COUNT(ID) AS numrows FROM store";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

// ... still more code coming
// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;
?>

 

Thanks,

Brett

[teng84]

where is this thing set $rowsPerPage??

[$username]

Here is the part here I am using the html to pass a var in the url

 

<table border="2" width="1800" rules="all" cellpadding="2" cellspacing="0" bordercolor="#000000" bgcolor="silver">
  <tr>
    <td><a href="./show.php?Ord=ID"><b><u>Case ID</u></b></td>
    <td><a href="./show.php?Ord=FirstName"><b><u>First Name</u></b></td>
    <td><a href="./show.php?Ord=LastName"><b><u>Last Name</u></b></td>
<td><a href="./show.php?Ord=AddressStreet"><b><u>Street</u></b></td>
<td><a href="./show.php?Ord=AddressCity"><b><u>City</u></b></td>
<td><a href="./show.php?Ord=AddressState"><b><u>State</u></b></td>
<td><a href="./show.php?Ord=AddressZip"><b><u>ZipCode</u></b></td>
<td><a href="./show.php?Ord=Paid"><b><u>Paid</u></b></td>
<td><b>Amount</b></td>
<td width="200"><b>Attempt 1</b></td>
<td><b>Attempt 2</b></td>
<td><b>Attempt 3</b></td>
<td><b>Attempt 4</b></td>
<td><b><a href="./show.php?Ord=OtherInfo"><u>Other Info</u></b></td>
<td><b><a href="./show.php?Ord=ServBy"><u>Served By</u></b></td>
<td><a href="./show.php?Ord=ClientName"><b><u>Client Name</u></b></td>
<td><a href="./show.php?Ord=County"><b><u>County Served</u></b></td>
<td><b>Serv Date</b></td>
  </tr>

 

Ok here is the part where I am trying to pull the order from a var

 

<?php
$Ord = $_GET['Ord'];
if ($Ord == "") 
$Ord = 'ID';
else
$Ord = $_GET['Ord'];
$sql = "SELECT * FROM store ORDER BY `$Ord`"; 
//$query = mysql_query($sql);

$query = " SELECT * FROM store ORDER BY '$Ord'" .
         " LIMIT $offset, $rowsPerPage ";
$result = mysql_query($query) or die('Error, query failed');
?>

 

Here is the complete code

 

 

<?php
include 'dbopen.php';
include 'dbconnect.php';
$rowsPerPage = 100;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$Ord = $_GET['Ord'];
if ($Ord == "") 
$Ord = 'ID';
else
$Ord = $_GET['Ord'];
$sql = "SELECT * FROM store ORDER BY `$Ord`"; 
//$query = mysql_query($sql);

$query = " SELECT * FROM store ORDER BY '$Ord'" .
         " LIMIT $offset, $rowsPerPage ";
$result = mysql_query($query) or die('Error, query failed');

// print the random numbers
while($row = mysql_fetch_array($result))
{
   //echo $row['ID'] . '<br>';
   echo "<tr>";
echo("<td><a href=\"updateinfo.php?ID=" .  $row["ID"] . "\" title=\"".$row["ID"]."\">" . $row["ID"] .  "</a></td>"); 
//echo "<td>".$row['ID']."</td>";
echo "<td>".$row['FirstName']."</td>";
echo "<td>".$row['LastName']."</td>";
echo "<td>".$row['AddressStreet']."</td>";
echo "<td>".$row['AddressCity']."</td>";
echo "<td>".$row['AddressState']."</td>";
echo "<td>".$row['AddressZip']."</td>";
echo "<td>".$row['Paid']."</td>";
echo "<td>".$row['Amount']."</td>";
echo "<td>".$row['CaseNotes1']."</td>";
echo "<td>".$row['CaseNotes2']."</td>";
echo "<td>".$row['CaseNotes3']."</td>";
echo "<td>".$row['CaseNotes4']."</td>";
echo "<td>".$row['OtherInfo']."</td>";
echo "<td>".$row['ServBy']."</td>";
echo "<td>".$row['ClientName']."</td>";
echo "<td>".$row['County']."</td>";
echo "<td>".$row['ServDate']."</td>";
echo "</tr>";
}


// ... more code here
// ... the previous code

// how many rows we have in database
$query   = "SELECT COUNT(ID) AS numrows FROM store";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}

// ... still more code coming
// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";

   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";

   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

// and close the database connection


// ... and we're done!

?>