Page 1 of 3

Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 3:26 am
by simonmlewis
Dear all
I have a strange issue here. The search form passes the data to this page. For now I am using only the $searchadv and $category variables.

If I use only the $searchadv then the pages work, with results on each page.
If I bring $category into play, page one works and it shows there are other pages, but when you click on them you see the
<div class='sectionhead'>Sorry, there are no results for $search.</div>
Can anyone please help.

Code: Select all

<?php 
$searchadv = $_POST['searchadv'];
$category = $_POST['category'];
$pricemin = $_POST['pricemin'];
$pricemax = $_POST['pricemax'];
include "dbconn.php";
echo "<div class='head'>Advanced Searching</div>";
 
$rowsPerPage = 5;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE title LIKE '%$searchadv%' AND category = '$category' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
if (mysql_num_rows($result)==0) { echo "<div class='sectionhead'>Sorry, there are no results for $search.</div>"; }
else {
 
while ($row = mysql_fetch_object($result))
      while ($row = mysql_fetch_object($result)) {
    echo "
<div class='cat_prodlistbox'>
<div class='cat_producttitle'>";
 
                    $position=43; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->title,0,$position);
echo "$postcontent ...</div>
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a></div
            </div>
                    
            ";
            }}          
    mysql_free_result($result);
    echo "</table>";
            
    echo "<div style='clear:both' /><hr noshade size='1' color='#cccccc' />";
    $query   = "SELECT COUNT(id) AS numrows FROM products WHERE title LIKE '%$searchadv%' AND category = '$category'";
    
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
    mysql_close($sqlconn);
?>

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 4:23 am
by matthijs
not related to your original question, but for the security of the app, you should escape your queries (mysql_real_escape_string() ) when querying the db, and escape when you echo out variables to HTML using htmlentities()

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 4:31 am
by simonmlewis
Hey thanks. I'll do that. Not sure what you mean at htmlentites though, but I don't know about the other bit.

The problem is now more peculiar, but may help identify the answer to more knowledgable coders. When you run this script searching on $searchadv only, it should come up with a load of responses (pages). It does. Says page 1, 2.
But when I click on page 2 (or 'next'), it then shows there are 4 pages, and if I click on page 1, there are still 4 pages (yet the 4th is empty).

Does this help anyone to fix my headache???

Code: Select all

<?php 
$searchadv = $_POST['searchadv'];
$category = $_POST['category'];
$pricemin = $_POST['pricemin'];
$pricemax = $_POST['pricemax'];
include "dbconn.php";
echo "<div class='head'>Advanced Searching</div>";
 
$rowsPerPage = 5;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$result = mysql_query ("SELECT * FROM products WHERE title LIKE '%$searchadv' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
if (mysql_num_rows($result)==0) { echo "<div class='sectionhead'>Sorry, there are no results.</div>"; }
else {
 
while ($row = mysql_fetch_object($result))
      while ($row = mysql_fetch_object($result)) {
    echo "
<div class='cat_prodlistbox'>
<div class='cat_producttitle'>";
 
                    $position=43; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->title,0,$position);
echo "$postcontent ...<br/>£$row->price</div>
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a></div
            </div>
                    
            ";
            }}          
    mysql_free_result($result);
    echo "</table>";
            
    echo "<div style='clear:both' /><hr noshade size='1' color='#cccccc' />";
    $query   = "SELECT COUNT(id) AS numrows FROM products WHERE title LIKE '%$searchadv%'";
    
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=searchadvresults&menu=home&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
        mysql_free_result($result);
    mysql_close($sqlconn);
?>

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:01 am
by susrisha

Code: Select all

 
$searchadv = $_POST['searchadv'];
$category = $_POST['category'];
$pricemin = $_POST['pricemin'];
$pricemax = $_POST['pricemax'];
 
Your problem lies in the above lines. The first page will show the results because all the post variables are set and got from the initial form. However these are not transferred to the next pages. Try printing the mysql query you are executing on each page and you will get to know of the problem.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:13 am
by simonmlewis
Sorry I don't understand.
The form is on a previous page.
I have to put them in the results page to store the variables.

What other way is there??

The problem I am having is not that the pages aren't working entirely, it's that I get pages 1,2.... but when I click 'next', I get 3 and 4 as well.

But it only works if I use just ONE criteria in the search. ie. $searchadv.
If I put:

Code: Select all

... AND category = '$category'...
into both MySQL queries, page one will work, but page two won't work at all and it says there are no results.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:15 am
by Eran
What other way is there??
Use GET instead of POST - pass the parameters in the URL. Continue to pass those parameters from page to page

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:31 am
by simonmlewis
Firstly, that means totally re-writing this.
Secondly, there's no need as this method works in my general search page which has just one search POST variable.

It's when I have more than one, that it fails.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:38 am
by jackpf
I think what pytrin means is that you need to use GET variables...unless you want to use javascript to create a form and populate the inputs when the user goes to the next page...

It'd be a lot easier to pass the variables in the url.

Currently, you're not passing them at all.
Firstly, that means totally re-writing this.
Then rewrite it you must! ;)

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:40 am
by simonmlewis
But please - if it works in another search page, why would it not work on this page when I am just adding one more "AND" in the MySQL query?

Fair enough what you are saying - but this does work...just need to let it work with a second critera in that query.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:41 am
by susrisha
Let me put it this way..
Try to print out your mysql query statement before executing a search and try to access all the pages [1,2,3..] to see whats the actual query you are trying to execute. That might show you whats wrong with post parameters and why use get parameters.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:54 am
by Eran
Firstly, that means totally re-writing this.
not at all. it means making a couple of relatively minor changes - change the form method to GET, and continue to pass those parameters in the page URLs you generate as a query string.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 5:58 am
by simonmlewis

Code: Select all

$var=mysql_query($result);
      echo $var;
??

Sorry, I never need to do this so never remember how to.

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 6:02 am
by simonmlewis
Thanks pytrin.
But I don't think you see my point here: this work correctly when passing one variable to this results page.

Page after page .... correct.

If I pass two or more variables, it fails. Forget about the URL GET for a moment.... why would two variables cause this to fail? And why when passing ONE variable would the first page say there are TWO pages, but when you click 'next', you are told there are actually FOUR pages?

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 7:58 am
by susrisha

Code: Select all

 
echo "<table width='100%' cellpadding='0' cellspacing='0' class='table'>";
$query="SELECT * FROM products WHERE title LIKE '%$searchadv%' AND category = '$category' LIMIT $offset, $rowsPerPage";
$result = mysql_query ("SELECT * FROM products WHERE title LIKE '%$searchadv%' AND category = '$category' LIMIT $offset, $rowsPerPage") or die (mysql_error()); // please print this query and let us know the output.
echo $query; //can you please echo the query thats present here...
 

Re: Page Numbering Query - page one ok, page 2,3..not ok.

Posted: Mon Aug 24, 2009 8:08 am
by simonmlewis
SELECT * FROM products WHERE title LIKE '%boat%' AND category = 'RC Boats' LIMIT 0, 6