Page 1 of 1

pagination, again

Posted: Mon Feb 21, 2005 8:27 am
by mohson
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.

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&#1111;'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&#1111;$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&#1111;firstname]%'";


$result = mysql_query($query);

// end config---------------------------------

$opt_cnt = count ($option);

$go = $_GET&#1111;'go'];
// paranoid
if ($go == "") &#123;
$go = $default;
&#125;
elseif (!in_array ($go, $option)) &#123;
$go = $default;
&#125;
elseif (!is_numeric ($go)) &#123;
$go = $default;
&#125;
$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 ++) &#123;
if ($option&#1111;$i] == $go) &#123;
echo "<option value="".$option&#1111;$i]."" selected="selected">".$option&#1111;$i]."</option>\r\n";
&#125; else &#123;
echo "<option value="".$option&#1111;$i]."">".$option&#1111;$i]."</option>\r\n";
&#125;
&#125;

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&#1111;'offset'];
//paranoid
if (get_magic_quotes_gpc() == 0) &#123;
$off = addslashes ($off);
&#125;
if (!is_numeric ($off)) &#123;
$off = 1;
&#125;
// this checks if user is trying to put something stupid in query string
if ($off > $off_pag) &#123;
$off = 1;
&#125;

if ($off == "1") &#123;
$limit = "0, $nol";
&#125;
elseif ($off <> "") &#123;
for ($i = 0; $i <= ($off - 1) * $nol; $i ++) &#123;
$limit = "$i, $nol";
$count = $i + 1;
&#125;
&#125; 




// 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)) 






&#123;($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;
&#125;

echo "</table>"; 

echo "<br /><br />\r\n";
if ($off <> 1) &#123;
$prev = $off - 1;
echo "&#1111; < <a href="$filename?offset=$prev&go=$go">prev</a> ] \r\n";
&#125;
for ($i = 1; $i <= $off_pag; $i ++) &#123;
if ($i == $off) &#123;
echo "&#1111;<b> $i </b>] \r\n";
&#125; else &#123;
echo "&#1111; <a href="$filename?offset=$i&go=$go">$i</a> ] \r\n";
&#125;
&#125;
if ($off < $off_pag) &#123;
$next = $off + 1;
echo "&#1111; <a href="$filename?offset=$next&go=$go">next</a> > ] \r\n";
&#125;

echo "<br /><br />\r\n";
echo "Page $off of $off_pag<br />\r\n";






?>

<a href="queries.html">Search Again</a>

Posted: Mon Feb 21, 2005 8:54 am
by feyd
your paging links do not pass any information of what the query was, nor are you using sessions to pass the information.

Posted: Mon Feb 21, 2005 9:39 am
by mohson
Thanks feyd,

Any advice on how I would do this??

Posted: Mon Feb 21, 2005 9:47 am
by feyd
first off, you need to pass the information of what they searched for, in some fashion...

on startup you need to check the post variables and this information transfer..