[solved]WHERE clause in pagination
Posted: Sun Jul 17, 2005 10:17 am
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?
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?
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?
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?
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?
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'
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
</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 ' ';
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>';
}
?>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
</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 ' ';
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>';
}
?>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 = "e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}else{
$query_count = "e;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
$id = $_GETї'id'];
if (!isset($id)){
$id=0;
}
if ($village == 'G63'){
$query = ("e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit"e;);
}else{
$query = ("e;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"e;);
}
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
print "e;<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=\"e;mailto:$rowї9]\"e;>$rowї9]</a><br/>\n
web: <a href=\"e;http://$rowї10]\"e; target=\"e;_blank\"e;>$rowї10]</a><br/>\n
&nbsp;</p>"e;;
}
if ($id>=$limit){
$prev=$id-$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$prev.'"e;>prev</a>';
}else{
echo '<span style="e;color:#999;"e;>prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
$next=$id+$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$next.'"e;>next</a>';
}else{
echo '<span style="e;color:#999;"e;>next</span>';
}
?>
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 = "e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}else{
$query_count = "e;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
$id = $_GETї'id'];
if (!isset($id)){
$id=0;
}
if ($village == 'G63'){
$query = ("e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit"e;);
}else{
$query = ("e;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"e;);
}
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
print "e;<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=\"e;mailto:$rowї9]\"e;>$rowї9]</a><br/>\n
web: <a href=\"e;http://$rowї10]\"e; target=\"e;_blank\"e;>$rowї10]</a><br/>\n
&nbsp;</p>"e;;
}
if ($id>=$limit){
$prev=$id-$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$prev.'"e;>prev</a>';
}else{
echo '<span style="e;color:#999;"e;>prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
$next=$id+$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$next.'"e;>next</a>';
}else{
echo '<span style="e;color:#999;"e;>next</span>';
}
?>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 = "e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}else{
$query_count = "e;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
$id = $_GETї'id'];
if (!isset($id)){
$id=0;
}
if ($village == 'G63'){
$query = ("e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit"e;);
}else{
$query = ("e;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"e;);
}
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
print "e;<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=\"e;mailto:$rowї9]\"e;>$rowї9]</a><br/>\n
web: <a href=\"e;http://$rowї10]\"e; target=\"e;_blank\"e;>$rowї10]</a><br/>\n
&nbsp;</p>"e;;
}
if ($id>=$limit){
$prev=$id-$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$prev.'"e;>prev</a>';
}else{
echo '<span style="e;color:#999;"e;>prev</span>';
}
echo '&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
<?php
$village = ($_GETї'village']);
$businesstype = ($_GETї'businesstype']);
$limit = 3;
if ($village == 'G63'){
$query_count = "e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}else{
$query_count = "e;SELECT * FROM business_directory WHERE Village = '$village' AND (Name LIKE '%$businesstype%' OR Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')"e;;
}
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
$id = $_GETї'id'];
if (!isset($id)){
$id=0;
}
if ($village == 'G63'){
$query = ("e;SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%') ORDER by 'Name' ASC LIMIT $id, $limit"e;);
}else{
$query = ("e;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"e;);
}
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
print "e;<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=\"e;mailto:$rowї9]\"e;>$rowї9]</a><br/>\n
web: <a href=\"e;http://$rowї10]\"e; target=\"e;_blank\"e;>$rowї10]</a><br/>\n
&nbsp;</p>"e;;
}
if ($id>=$limit){
$prev=$id-$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$prev.'"e;>prev</a>';
}else{
echo '<span style="e;color:#999;"e;>prev</span>';
}
echo '&nbsp;&nbsp;&nbsp;';
if ($id<($totalrows - $limit)){
$next=$id+$limit;
echo '<a href="e;'.$_SERVERї'PHP_SELF'].'?id='.$next.'"e;>next</a>';
}else{
echo '<span style="e;color:#999;"e;>next</span>';
}
?>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
</p>";
}
if ($id>=$limit){
$prev=$id-$limit;
echo '<a href="'.$_SERVER
<?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
</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 ' ';
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>';
}
?>Does this make any sense? Would be eternally grateful for any suggestions.
Sorry - this should probably have been posted in 'databases'