[solved]WHERE clause in pagination

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

[solved]WHERE clause in pagination

Post by zoe »

Hi
I'm building a search facility and am trying to paginate the results. Everything's rosey so long as my query is a simple SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

<?php

$village = ($_GET['village']);
$businesstype = ($_GET['businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = "SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}else{
$query_count = "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET['id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = ("SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}else{
$query = ("SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
if ($id>=$limit){
	$prev=$id-$limit;
	echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$prev.'">prev</a>';
}else{
	echo '<span style="color:#999;">prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
	$next=$id+$limit;
		echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'">next</a>';
}else{
	echo '<span style="color:#999;">next</span>';
}
?>
I've tried ouputing the querys and all's well there on the first page. On the 'next' page, the querys contain no values for the variables.
Does this make any sense? Would be eternally grateful for any suggestions.
Sorry - this should probably have been posted in 'databases'to paginate the results. Everything's rosey so long as my query is a simple SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

<?php

$village = ($_GET['village']);
$businesstype = ($_GET['businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = "SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}else{
$query_count = "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET['id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = ("SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}else{
$query = ("SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
if ($id>=$limit){
	$prev=$id-$limit;
	echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$prev.'">prev</a>';
}else{
	echo '<span style="color:#999;">prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
	$next=$id+$limit;
		echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'">next</a>';
}else{
	echo '<span style="color:#999;">next</span>';
}
?>
I've tried ouputing the querys and all's well there on the first page. On the 'nsey so long as my query is a simple SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

&lt;?php

$village = ($_GET&#1111;'village']);
$businesstype = ($_GET&#1111;'businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = &quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}else{
$query_count = &quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET&#1111;'id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = (&quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}else{
$query = (&quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print &quote;&lt;p&gt;&lt;b&gt;$row&#1111;1]&lt;/b&gt;&lt;br/&gt;\n
		   		  $row&#1111;8]&lt;br/&gt;\n
				  $row&#1111;2], $row&#1111;3]&lt;br/&gt;\n
				  $row&#1111;4]&lt;br/&gt;\n 
				  $row&#1111;5]&lt;br/&gt;\n
				  phone: $row&#1111;6]&lt;br/&gt;\n
				  fax: $row&#1111;7]&lt;br/&gt;\n
				  email: &lt;a href=\&quote;mailto:$row&#1111;9]\&quote;&gt;$row&#1111;9]&lt;/a&gt;&lt;br/&gt;\n
				  web: &lt;a href=\&quote;http://$row&#1111;10]\&quote; target=\&quote;_blank\&quote;&gt;$row&#1111;10]&lt;/a&gt;&lt;br/&gt;\n
				  &amp;nbsp;&lt;/p&gt;&quote;;
}
if ($id&gt;=$limit){
	$prev=$id-$limit;
	echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;prev&lt;/span&gt;';
}
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;';
if ($id&lt;($totalrows - $limit)){
	$next=$id+$limit;
		echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
 as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

&lt;?php

$village = ($_GET&#1111;'village']);
$businesstype = ($_GET&#1111;'businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = &quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}else{
$query_count = &quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET&#1111;'id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = (&quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}else{
$query = (&quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print &quote;&lt;p&gt;&lt;b&gt;$row&#1111;1]&lt;/b&gt;&lt;br/&gt;\n
		   		  $row&#1111;8]&lt;br/&gt;\n
				  $row&#1111;2], $row&#1111;3]&lt;br/&gt;\n
				  $row&#1111;4]&lt;br/&gt;\n 
				  $row&#1111;5]&lt;br/&gt;\n
				  phone: $row&#1111;6]&lt;br/&gt;\n
				  fax: $row&#1111;7]&lt;br/&gt;\n
				  email: &lt;a href=\&quote;mailto:$row&#1111;9]\&quote;&gt;$row&#1111;9]&lt;/a&gt;&lt;br/&gt;\n
				  web: &lt;a href=\&quote;http://$row&#1111;10]\&quote; target=\&quote;_blank\&quote;&gt;$row&#1111;10]&lt;/a&gt;&lt;br/&gt;\n
				  &amp;nbsp;&lt;/p&gt;&quote;;
}
if ($id&gt;=$limit){
	$prev=$id-$limit;
	echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;prev&lt;/span&gt;';
}
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;';
if ($id&lt;($totalrows - $limit)){
	$next=$id+$limit;
		echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
I've tried ouputing tle SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

<?php

$village = ($_GET['village']);
$businesstype = ($_GET&#1111;'businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = &quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}else{
$query_count = &quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET&#1111;'id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = (&quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}else{
$query = (&quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print &quote;&lt;p&gt;&lt;b&gt;$row&#1111;1]&lt;/b&gt;&lt;br/&gt;\n
		   		  $row&#1111;8]&lt;br/&gt;\n
				  $row&#1111;2], $row&#1111;3]&lt;br/&gt;\n
				  $row&#1111;4]&lt;br/&gt;\n 
				  $row&#1111;5]&lt;br/&gt;\n
				  phone: $row&#1111;6]&lt;br/&gt;\n
				  fax: $row&#1111;7]&lt;br/&gt;\n
				  email: &lt;a href=\&quote;mailto:$row&#1111;9]\&quote;&gt;$row&#1111;9]&lt;/a&gt;&lt;br/&gt;\n
				  web: &lt;a href=\&quote;http://$row&#1111;10]\&quote; target=\&quote;_blank\&quote;&gt;$row&#1111;10]&lt;/a&gt;&lt;br/&gt;\n
				  &amp;nbsp;&lt;/p&gt;&quote;;
}
if ($id&gt;=$limit){
	$prev=$id-$limit;
	echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;prev&lt;/span&gt;';
}
echo '&amp;nbsp;& search facility and am trying to paginate the results. Everything's rosey so long as my query is a simple SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

&lt;?php

$village = ($_GET&#1111;'village']);
$businesstype = ($_GET&#1111;'businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = &quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}else{
$query_count = &quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')&quote;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET&#1111;'id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = (&quote;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}else{
$query = (&quote;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit&quote;);
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print &quote;&lt;p&gt;&lt;b&gt;$row&#1111;1]&lt;/b&gt;&lt;br/&gt;\n
		   		  $row&#1111;8]&lt;br/&gt;\n
				  $row&#1111;2], $row&#1111;3]&lt;br/&gt;\n
				  $row&#1111;4]&lt;br/&gt;\n 
				  $row&#1111;5]&lt;br/&gt;\n
				  phone: $row&#1111;6]&lt;br/&gt;\n
				  fax: $row&#1111;7]&lt;br/&gt;\n
				  email: &lt;a href=\&quote;mailto:$row&#1111;9]\&quote;&gt;$row&#1111;9]&lt;/a&gt;&lt;br/&gt;\n
				  web: &lt;a href=\&quote;http://$row&#1111;10]\&quote; target=\&quote;_blank\&quote;&gt;$row&#1111;10]&lt;/a&gt;&lt;br/&gt;\n
				  &amp;nbsp;&lt;/p&gt;&quote;;
}
if ($id&gt;=$limit){
	$prev=$id-$limit;
	echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$prev.'&quote;&gt;prev&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;prev&lt;/span&gt;';
}
echo '&amp;nbsp;&amp;nbsp;&amp;nbsp;';
if ($id&lt;($totalrows - $limit)){
	$next=$id+$limit;
		echo '&lt;a href=&quote;'.$_SERVER&#1111;'PHP_SELF'].'?id='.$next.'&quote;&gt;next&lt;/a&gt;';
}else{
	echo '&lt;span style=&quote;color:#999;&quote;&gt;next&lt;/span&gt;';
}
?&gt;
I've tried ouputing the querys and all's well there on the first page. On the 'next' page, the querys contain no values for the variables.
Does this make any sense? Would be eternally grateful for any suggestions.
search facility and am trying to paginate the results. Everything's rosey so long as my query is a simple SELECT *. As soon as I complicate the query with a WHERE clause, the first page of results is fine, then the query doesn't seem to be passed on to the next page, which then displays the unfiltered contents of the table and offers no further pages. I'm afraid I have posted a similar question before, but I've a slightly better understanding of my own question now!
From my code, can anyone see why the $village and $businesstype variables aren't getting passed to the next page?

Code: Select all

<?php

$village = ($_GET['village']);
$businesstype = ($_GET['businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = "SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}else{
$query_count = "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET['id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = ("SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}else{
$query = ("SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
if ($id>=$limit){
	$prev=$id-$limit;
	echo '<a href="'.$_SERVER&#1
<?php

$village = ($_GET['village']);
$businesstype = ($_GET['businesstype']);
$limit = 3;

if ($village == 'G63'){
$query_count = "SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}else{
$query_count = "SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

$id = $_GET['id'];
if (!isset($id)){
	$id=0;
}
if ($village == 'G63'){
$query = ("SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}else{
$query = ("SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
if ($id>=$limit){
	$prev=$id-$limit;
	echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$prev.'">prev</a>';
}else{
	echo '<span style="color:#999;">prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
	$next=$id+$limit;
		echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'">next</a>';
}else{
	echo '<span style="color:#999;">next</span>';
}
?>
I've tried ouputing the querys and all's well there on the first page. On the 'next' page, the querys contain no values for the variables.
Does this make any sense? Would be eternally grateful for any suggestions.
Sorry - this should probably have been posted in 'databases'
Last edited by zoe on Mon Jul 18, 2005 1:33 am, edited 1 time in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

You should post the variables using $_GET.
For example in your next link, the URL should look like $_SERVER['PHP_SELF']."?id=$next&var1=value&var2=value.

This will pass your variables quite nicely.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

Forgive my newbiness. I've been thinking along those lines, but don't know the syntax.
I tried a couple of variations on:

Code: Select all

echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&businesstype=($_GET['businesstype'])&village=($_GET['village'])">prev</a>';
This gives a parse error, but is it remotely on the right track?ose lines, but don't know the syntax.
I tried a couple of variations on:

Code: Select all

echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&businesstype=($_GET['businesstype'])&a tried a couple of variations on:

Code: Select all

echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&amp;businesstype=($_GET&#1111;'businesstype'])&amp;village=($_GET&#1111;'village'])&quote;&gt;prev&lt;/a&gt;';
This gives a parse error, but is it remote
echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&businesstype=($_GET['businesstype'])&village=($_GET['village'])">prev</a>';

This gives a parse error, but is it remotely on the right track?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Here's the parse error fix:

Code: Select all

echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&businesstype='.$_GET['businesstype'].'&village='.$_GET['village'].'">prev</a>';
and yes, that's the way to concatenate your query string for passing variables via GET ;)echo '<a href="'.$_SERVER['PHP_SELF'].'?id=$prev&businesstype='.$_GET['businesstype'].'&village='.$_GET['village'].'">prev</a>';


and yes, that's the way to concatenate your query string for passing variables via GET ;)
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

thank you sooo much. That's certainly fixed the parse error. Still not getting the results, but I must be close. I can smell it! Will dig around in my code some more....

Have dug a little.The only problem now is that $next and $prev are not being processed, so to speak; The url when i click the 'next' link is: http://www.g63directory.com/Results1.ph ... ge=Balfron. Why might this be?
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

[solved] WHERE clause in pagination

Post by zoe »

sob sob... i can't believe it finally works! I've been struggling with this one for 3 days. thanks so much to everyone who helped. i've learnt so much about this.

The fully functional echo statement is:

Code: Select all

echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'&var1='.$_GET['var1'].'&var2='.$_GET['var2'].'">next</a>';
For anyone else out there losing sleep over this, I know there's a lot of code out there that claims to do the job but here's what works for me. Please excuse any newbie messiness :oops:

Code: Select all

<?php

mysql_connect("host", "username", "password")//connect to database
or die("Failure to connect to database");
mysql_select_db(database);

$village = ($_GET['village']);//retrieve variables from form (user input)
$businesstype = ($_GET['businesstype']);

$limit = 3;//set number of records per page

$id = $_GET['id'];//number of first record on current page(0 if first page, otherwise retrieved from previous page)
if (!isset($id)){
	$id=0;
	}else{
	$id=$id;
}

//retrieve number of records returned by query
if ($village == 'G63'){//my site offers the option to search any local village or alternatively, the entire postal area.
$query_count = "SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}else{
$query_count = "SELECT * FROM table WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);



echo "<h1>Your search results for <strong>\"$businesstype\"</strong> in <strong>$village</strong>:</h1><br/>";

//perform query
if ($village == 'G63'){
$query = ("SELECT * FROM table WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}else{
$query = ("SELECT * FROM table WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit");
}
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {//display results
		   print "<p><b>$row[1]</b><br/>\n
		   		  $row[8]<br/>\n
				  $row[2], $row[3]<br/>\n
				  $row[4]<br/>\n 
				  $row[5]<br/>\n
				  phone: $row[6]<br/>\n
				  fax: $row[7]<br/>\n
				  email: <a href=\"mailto:$row[9]\">$row[9]</a><br/>\n
				  web: <a href=\"http://$row[10]\" target=\"_blank\">$row[10]</a><br/>\n
				  &nbsp;</p>";
}
if ($id>=$limit){//create previous and next links to navigate through results
	$prev=$id-$limit;
	echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$prev.'&businesstype='.$_GET['businesstype'].'&village='.$_GET['village'].'">prev</a>';
}else{
	echo "<span style=\"color:#999;\">prev</span>";
}
echo "&nbsp;&nbsp;&nbsp;";
if ($id<($totalrows - $limit)){
	$next=$id+$limit;
		echo '<a href="'.$_SERVER['PHP_SELF'].'?id='.$next.'&businesstype='.$_GET['businesstype'].'&village='.$_GET['village'].'">next</a>';
}else{
	echo "<span style=\"color:#999;\">next</span>";
}
?>
Post Reply