Need php/mysql help

Need help fixing some php code. I have code which paginates output from a mysql database (sort of like a guest book). I am trying to sort data on a particular database column--rather than print all entries-- and paginate output. My code works for the first page of output, but when the page is reloaded to process second page the rest of the database entries are listed. There should be an easy fix, but it hasn't come to me yet. Any thoughts?


Here is the code:

<?php
$msg = "<h2>Select the archive you wish to view.</h2>";
#create archive viewing button
$msg.= "<form action=\"guestbook_view.php\" method=\"post\">";
$msg.="<select name=\"choice\">";
$msg.="<option value=\"All\">All Archives</option>";
$msg.="<option value=\"Male\">Male</option>";
$msg.="<option value=\"Female\">Female</option>";
$msg.="<option value=\"Alabama\">Alabama</option>";
$msg.="<option value=\"Alaska\">Alaska</option>";
$msg.="<option value=\"Arizona\">Arizona</option>";
$msg.="<option value=\"Arkansas\">Arkansas</option>";
$msg.="<option value=\"California\">California</option>";
$msg.="<option value=\"Colorado\">Colorado</option>";
$msg.="<option value=\"Connecticut\">Connecticut</option>";
$msg.="<option value=\"Delaware\">Delaware</option>";
$msg.="<option value=\"Florida\">Florida</option>";
$msg.="<option value=\"Georgia\">Georgia</option>";
$msg.="<option value=\"Hawaii\">Hawaii</option>";
$msg.="<option value=\"Idaho\">Idaho</option>";
$msg.="<option value=\"Illinois\">Illinois</option>";
$msg.="<option value=\"Indiana\">Indiana</option>";
$msg.="<option value=\"Iowa\">Iowa</option>";
$msg.="<option value=\"Kansas\">Kansas</option>";
$msg.="<option value=\"Kentucky\">Kentucky</option>";
$msg.="<option value=\"Louisiana\">Louisiana</option>";
$msg.="<option value=\"Maine\">Maine</option>";
$msg.="<option value=\"Maryland\">Maryland</option>";
$msg.="<option value=\"Massachusetts\">Massachusetts</option>";
$msg.="<option value=\"Michigan\">Michigan</option>";
$msg.="<option value=\"Minnesota\">Minnesota</option>";
$msg.="<option value=\"Mississippi\">Mississippi</option>";
$msg.="<option value=\"Missouri\">Missouri</option>";
$msg.="<option value=\"Montana\">Montana</option>";
$msg.="<option value=\"Nebraska\">Nebraska</option>";
$msg.="<option value=\"Nevada\">Nevada</option>";
$msg.="<option value=\"New Hampshire\">New Hampshire</option>";
$msg.="<option value=\"New Jersey\">New Jersey</option>";
$msg.="<option value=\"New Mexico\">New Mexico</option>";
$msg.="<option value=\"New York\">New York</option>";
$msg.="<option value=\"North Carolina\">North Carolina</option>";
$msg.="<option value=\"North Dakota\">North Dakota</option>";
$msg.="<option value=\"Ohio\">Ohio</option>";
$msg.="<option value=\"Oklahoma\">Oklahoma</option>";
$msg.="<option value=\"Oregon\">Oregon</option>";
$msg.="<option value=\"Pennsylvania\">Pennsylvania</option>";
$msg.="<option value=\"Puerto Rico\">Puerto Rico</option>";
$msg.="<option value=\"Rhode Island\">Rhode Island</option>";
$msg.="<option value=\"South Carolina\">South Carolina</option>";
$msg.="<option value=\"South Dakota\">South Dakota</option>";
$msg.="<option value=\"Tennessee\">Tennessee</option>";
$msg.="<option value=\"Texas\">Texas</option>";
$msg.="<option value=\"Utah\">Utah</option>";
$msg.="<option value=\"Vermont\">Vermont</option>";
$msg.="<option value=\"Virginia\">Virginia</option>";
$msg.="<option value=\"Washington\">Washington</option>";
$msg.="<option value=\"West Virginia\">West Virginia</option>";
$msg.="<option value=\"Wisconsin\">Wisconsin</option>";
$msg.="<option value=\"Wyoming\">Wyoming</option>";
$msg.="</select>";

$msg.="<input type=\"submit\" name=\"submit\" ";
$msg.="value=\"Visit Archive\"> </form>";
echo($msg);?>


<?php

$self = $_SERVER['PHP_SELF'];
$referer = $_SERVER['HTTP_REFERER'];
$choice = $_POST['choice'];

#connect to MySQL
$rs = @mysql_connect( "localhost", "xxxx", "xxxxxx" )
or die( "Could not connect to MySQL" );

#select the database
$rs = @mysql_select_db( "my_database" )
or die( "Could not select database" );



//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
if (empty($choice) or $choice=="All")
{
$data = mysql_query("SELECT * FROM guestbook") or die(mysql_error());
$rows = mysql_num_rows($data);
}
elseif ($choice=="Male")
{
$data = mysql_query("SELECT * FROM guestbook WHERE sex='M' or sex='m'") or die(mysql_error());
$rows = mysql_num_rows($data);
}
elseif ($choice=="Female")
{
$data = mysql_query("SELECT * FROM guestbook WHERE sex='F' or sex='f'") or die(mysql_error());
$rows = mysql_num_rows($data);
}
else
{
$data = mysql_query("SELECT * FROM guestbook WHERE location='$choice'") or die(mysql_error());
$rows = mysql_num_rows($data);
}

//How many rows retrieved
//echo($rows);

//Printing archive search for categories with 0 entries is avoided
if ($rows==0)
echo "<h3>There are no entries in this category</h3>";
else
{ //starts loop for printing entries


//This is the number of results displayed per page
$page_rows = 5;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets range that we will display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

//This is your query again, the same one... the only difference is we add $max into it
if (empty($choice) or $choice=="All")
$data_p = mysql_query("SELECT * FROM guestbook order by time desc $max") or die(mysql_error());
elseif ($choice=="Male")
{
$data_p = mysql_query("SELECT * FROM guestbook WHERE sex='M' or sex='m' order by time desc $max") or die(mysql_error());
$rows = mysql_num_rows($data_p);
}
elseif ($choice=="Female")
{
$data_p = mysql_query("SELECT * FROM guestbook WHERE sex='F' or sex='f' order by time desc $max") or die(mysql_error());
$rows = mysql_num_rows($data_p);
}
else
{
$data_p = mysql_query("SELECT * FROM guestbook WHERE location='$choice' order by time desc $max") or die(mysql_error());
$rows = mysql_num_rows($data_p);
}

//This is where you display your query results
while($row = mysql_fetch_array( $data_p ))
{
?>

<table class="archive" border="1" width="500">
<tr>
<td>you are? <?php echo $row["name"]; ?></td>

<td>slapping whom? <?php echo $row["slappee"]; ?> </td>
<td>when? <?php echo $row["day"]; ?></td>
</tr>
<tr>
<td colspan="3">where? <?php echo $row["location"]; ?></td>
</tr>


<tr>
<td colspan="3">and most importantly, why? :<?php echo $row["comments"]; ?></td>
</tr>
</table>
<br />

<?php } ?>

<?php
echo "<p>";

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}


//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}
} //ends else loop for printing entries
?>

I haven't done an in-depth analysis of your code, but one thing I could suggest is that you put some echo statements in strategic places to see what the code is doing. For example, where you are building your query with the limit, add an

echo $data_p;
into your code. Does your query contain the right limit information? Perhaps your formula for calculating that is incorrect.

Hope this helps.

I received the following PM from the OP concerning this problem:

Thanks for looking at my code. I have in fact used echo statements in an attempt to find my error. Here's what I have found: I have 9 rows in my database. 6 rows have "Colorado" in the location column. I am outputting 5 entries per page. When I sort on "Colorado," 6 rows are found and the first 5 are printed. When I click the "next" anchor, the 6th, 7th, 8th, and 9th entries in my table are printed. By clicking "next" all rows are re-read and everything past the first 5 count is printed. This is my problem--I'm losing the sorted array by clicking "next." Any ideas on these issues?

You're not trying to sort the data after selecting it from the database, are you? That may be the source of your problem. You can have your query do that for you. For example:

SELECT * FROM guestbook ORDER BY state
or whatever the field name is that you want to sort on.

Hope this helps.