pagination, again
Posted: Mon Feb 21, 2005 8:27 am
I am experiencing a rather confusing problem.
My pagination code allows me to search through my records with previous next buttons and page number links and it works brilliantly....UNTILL I started doing queries and displaying search results. this is what happens.
If I enter 'D' in my search text box it will find me all the people with 'D's as the first letter in the name and present the first say 20 records but if there are more than 20 people with 'D' as the first letter then it will display the first 20 and present a next link to indicate more results... THAT IS PERFECT...
The problem is that when I click the next link it doesnt go to the remaining results but instead it displays a random set of results.
Can anyone see why aside from the first page when I click next my other results are not being displayed?
ps. this only occurs with search queries - the < > links work fine when I display all results.
My pagination code allows me to search through my records with previous next buttons and page number links and it works brilliantly....UNTILL I started doing queries and displaying search results. this is what happens.
If I enter 'D' in my search text box it will find me all the people with 'D's as the first letter in the name and present the first say 20 records but if there are more than 20 people with 'D' as the first letter then it will display the first 20 and present a next link to indicate more results... THAT IS PERFECT...
The problem is that when I click the next link it doesnt go to the remaining results but instead it displays a random set of results.
Can anyone see why aside from the first page when I click next my other results are not being displayed?
ps. this only occurs with search queries - the < > links work fine when I display all results.
Code: Select all
<?php
// config-------------------------------------
$host = "****"; //your database host
$user = "****"; // your database user name
$pass = "****"; // your database password
$db = "contact_management_system"; // your database name
$filename = "searchfirstname1.html"; // name of this file
$option = array (5, 10, 20, 50, 100, 200);
$default = 20; // default number of records per page
$action = $_SERVERї'PHP_SELF']; // if this doesn't work, enter the filename
// the query used to search the DB
foreach($HTTP_POST_VARS as $varname => $value)
$formVarsї$varname]=$value;
$query = "SELECT
o.org_id,o.web_url,
p.person_id,p.org_id,p.salutation,p.firstname,p.surname,
p.organisation,p.role,p.address1,p.address2,p.city,
p.postcode,p.telephone,p.mobile,p.fax,p.dateoflastcontact,
p.datecontactagain,p.notes,p.email,
DATE_FORMAT(dateoflastcontact, '%d/%m/%y')
AS dateoflastcontact, DATE_FORMAT(datecontactagain, '%d/%m/%y')
AS datecontactagain
FROM people p LEFT JOIN organisations o
ON o.org_ID = p.org_id
WHERE firstname LIKE '$formVarsїfirstname]%'";
$result = mysql_query($query);
// end config---------------------------------
$opt_cnt = count ($option);
$go = $_GETї'go'];
// paranoid
if ($go == "") {
$go = $default;
}
elseif (!in_array ($go, $option)) {
$go = $default;
}
elseif (!is_numeric ($go)) {
$go = $default;
}
$nol = $go;
$limit = "0, $nol";
$count = 1;
echo "<form name="form1" id="form1" method="get" action="$action">\r\n";
echo "<select name="go" id="go">\r\n";
for ($i = 0; $i <= $opt_cnt; $i ++) {
if ($optionї$i] == $go) {
echo "<option value="".$optionї$i]."" selected="selected">".$optionї$i]."</option>\r\n";
} else {
echo "<option value="".$optionї$i]."">".$optionї$i]."</option>\r\n";
}
}
echo "</select>\r\n";
echo "<input type="submit" name="Submit" id="Submit" value="Go" />\r\n";
echo "</form>\r\n";
$connection = mysql_connect ($host, $user, $pass) or die ("Unable to connect");
mysql_select_db ($db) or die ("Unable to select database $db");
// control query------------------------------
/* this query checks how many records you have in your table.
I created this query so we could be able to check if user is
trying to append number larger than the number of records
to the query string.*/
$off_sql = mysql_query ("$query") or die ("Error in query: $off_sql".mysql_error());
$off_pag = ceil (mysql_num_rows($off_sql) / $nol);
//--------------------------------------------
$off = $_GETї'offset'];
//paranoid
if (get_magic_quotes_gpc() == 0) {
$off = addslashes ($off);
}
if (!is_numeric ($off)) {
$off = 1;
}
// this checks if user is trying to put something stupid in query string
if ($off > $off_pag) {
$off = 1;
}
if ($off == "1") {
$limit = "0, $nol";
}
elseif ($off <> "") {
for ($i = 0; $i <= ($off - 1) * $nol; $i ++) {
$limit = "$i, $nol";
$count = $i + 1;
}
}
// Query to extract records from database.
$sql = mysql_query ("$query LIMIT $limit") or die ("Error in query: $sql".mysql_error());
// Define your colors for the alternating rows
$color1 = "#ADD8E6";$color2 = "#E0FFFF";
$color = $color2;echo
"<table width="50%" border="0" cellpadding="2" cellspacing="2">
<tr>
<td><b><small>RecNo</small></b></td>
<td><b><small>PID</small></b></td>
<td><b><small>OID</small></b></td>
<td><b><small>Title</small></b></td>
<td><b><small>First Name</small></b></td>
<td><b><small>Surname</small></b></td>
<td><b><small>Organisation</small></b></td>
<td><b><center><small>Role</small></center></b></td>
<td><b><small>Address(1)</small></b></td>
<td><b><small>Address(2)</small></b></td>
<td><b><small>City</small></b></td>
<td><b><small>Post Code</small></b></td>
<td><b><small>Telephone</small></b></td>
<td><b><small>Mobile</small></b></td>
<td><b><small>Fax</small></b></td>
<td><b><small>Last Contact</small></b></td>
<td><b><small>Contact Again</small></b></td>
<td><b><small>Notes</small></b></td>";
while ($row = mysql_fetch_object($sql))
{($color==$color2)? $color = $color1 : $color = $color2;
echo "<tr bgcolor="$color"><td>".$count . '</td><td> ' . $row->person_id .'</td><td>'.
$row->org_id .'</td><td>'.
$row->salutation .'</td><td>'.
'<a href=mailto:'.$row->email.'>'.$row->firstname .'</a></td><td>'.
'<a href=mailto:'.$row->email.'>'.$row->surname .'</a></td><td>'.
'<a href=http://'.$row->web_url.'>'.$row->organisation . '</a></td><td>'.
$row->role.'</td><td>'.
$row->address1 .'</td><td>'.
$row->address2 .'</td><td>'.
$row->city .'</td><td>'.
$row->postcode .'</td><td>'.
$row->telephone .'</td><td>'.
$row->mobile .'</td><td>'.
$row->fax .'</td><td>'.
$row->dateoflastcontact.'</td><td>'.
$row->datecontactagain .'</td><td>'.
$row->notes .'</td></tr>';
$count += 1;
}
echo "</table>";
echo "<br /><br />\r\n";
if ($off <> 1) {
$prev = $off - 1;
echo "ї < <a href="$filename?offset=$prev&go=$go">prev</a> ] \r\n";
}
for ($i = 1; $i <= $off_pag; $i ++) {
if ($i == $off) {
echo "ї<b> $i </b>] \r\n";
} else {
echo "ї <a href="$filename?offset=$i&go=$go">$i</a> ] \r\n";
}
}
if ($off < $off_pag) {
$next = $off + 1;
echo "ї <a href="$filename?offset=$next&go=$go">next</a> > ] \r\n";
}
echo "<br /><br />\r\n";
echo "Page $off of $off_pag<br />\r\n";
?>
<a href="queries.html">Search Again</a>