Page 1 of 1

Pagination - works without WHERE

Posted: Tue Sep 29, 2009 9:21 pm
by freeworld
Hey guys. I'm having a problem paging my results from a query. I've searched google, as well as these forums, and I still can't figure out what I'm doing wrong. The paging works fine if I am selecting everything from my database, but when I include a WHERE clause in the query it only returns the first page of results, and when I click 'next page' it shows up empty.

Basically I have a search.php page with forms and this is the results.php page code. I tried using a session to store the variable for Type but it's still not working when I include the WHERE clause it just returns the first page of results, next page links return nothing.

If anyone could shed some light on this that would be great. Thanks.

Code: Select all

 
<?php session_start(); session_register('Type'); $_SESSION['Type'] = $_POST['Type'];?>
<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php include("includes/header.php"); ?>
 
<table id="structure">
    <tr>
        <td id="navigation">
            
                <li>Home</li>
                <li>Search</li>
                <li>About Us</li>
                <li>Sell Your Car</li>
                <li>Info</li>
                <li>Agreement</li>
            
                
            <br />
        </td>
        <td id="page">
            
 
        <?php   
        
            $Type = $_SESSION['Type'];
            echo $_SESSION['Type'];
            /*
            $Type = $_POST['Type'];
            $Make = $_POST['Make'];
            $Model = $_POST['Model'];
            $Year = $_POST['Year'];
            $Mileage = $_POST['Mileage'];
            $Price = $_POST['Price'];
            $Color = $_POST['Color'];
            */
            $rowsPerPage = 5;
            
            
            
            if(!isset($_GET['page'])) {
                $pageNum = 1;
            } else {
                $pageNum = $_GET['page'];
            }
            
            $offset = ($pageNum - 1) * $rowsPerPage;
        
            //paging works fine without WHERE query...
            
            $query = "SELECT * FROM autos "; /* WHERE Type like '$Type' ";
                
                if ($Make) {
                    $query .= "&& Make='$Make' ";
                }
                if ($Model) {
                    $query .= "&& Model='$Model' ";
                }
                if ($Year) {
                    $query .= "&& Year<='$Year' ";
                }
                if ($Mileage) {
                    $query .= "&& Mileage<='$Mileage' ";
                }
                if ($Price) {
                    $query .= "&& Price<='$Price' ";
                }
                if ($Color) {
                    $query .= "&& Color='$Color' ";
                }       */
                    $query .= "LIMIT $offset, $rowsPerPage";
            $result_set = mysql_query($query, $connection) or die('Error, query failed');
            echo $offset, $rowsPerPage;
            
            //OUTPUT RESULTS
            while($row = mysql_fetch_array($result_set))
            {
                echo $row['Make'] . '<br>';
                echo $row['Price'] . '<br>';
                echo "<br />";
            }
        
            
            //ORIGINAL
            /*
            do { if($row) { ?>
            <table width="789" border="1">
            <tr>
            <td>
            <img src="<?php echo $row['res_thumbSource']; ?>" </img>
            <?php echo $row['Price']; ?>
            </td>
            </tr>
            </table>
            <br />
            
    <?php     } }  while ($row = mysql_fetch_array($result_set));   */
            //ORIGINAL
    ?> <?php
            // how many rows we have in database
            $query   = "SELECT COUNT(id) AS numrows FROM autos";
            $result  = mysql_query($query) or die('Error, query failed');
            $row     = mysql_fetch_array($result, MYSQL_ASSOC);
            $numrows = $row['numrows'];
            echo $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=\"$self?page=$page\">$page</a> ";
               }
            }
 
            
    ?> <?php
            // 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=\"$self?page=$page\">[Prev]</a> ";
 
               $first = " <a href=\"$self?page=1\">[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=\"$self?page=$page\">[Next]</a> ";
 
               $last = " <a href=\"$self?page=$maxPage\">[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 $first . $prev . $nav . $next . $last;
    
    ?>
    
    
        
        </td>
    </tr>
</table>
 
<?php require("includes/footer.php"); ?>
 

Re: Pagination - works without WHERE

Posted: Tue Sep 29, 2009 9:23 pm
by requinix
And you're sure there should be more than one page of results?

Re: Pagination - works without WHERE

Posted: Tue Sep 29, 2009 9:31 pm
by freeworld
Yea, I have the results set to show 5 per page. I'm searching for things with Type that have 6+ entries in the database. When I take the WHERE clause out it all works fine... I have my LIMIT values echoing, and they are changing like they should be. When I click 'next page' should it be running the same query again with different LIMIT values?

Re: Pagination - works without WHERE

Posted: Tue Sep 29, 2009 9:42 pm
by requinix
You're losing the value of $Type after the first page.

The first page you have $_POST["Type"] but if you're using links for the previous and next pages then you don't have it on those pages. Then $_SESSION["Type"] is blank, $Type is blank, and your query is

Code: Select all

SELECT * FROM autos WHERE Type LIKE '' LIMIT 5, 5
freeworld wrote:When I click 'next page' should it be running the same query again with different LIMIT values?
Yes. Same query, different LIMIT.

Re: Pagination - works without WHERE

Posted: Tue Sep 29, 2009 9:54 pm
by freeworld
Ok, I see why that is happening. But shouldn't session hold the $Type variable? The links I believe are just 'refreshing' the result page and appending a page number on the end. (i.e. results.php?page=1, results.php?page=2, and so on) Shouldn't this run the query again everytime a page link is clicked?

I'm pretty new to PHP so perhaps I'm setting up the session wrong. Isn't that what I would use to get the variable to flow from page to page?

Re: Pagination - works without WHERE

Posted: Tue Sep 29, 2009 10:06 pm
by freeworld
Ahh I see what I was doing wrong. That was fairly simple. When I was clicking 'next page' it was reposting the type variable into the session variable.. which was basically clearing the session variable of what I wanted.

Here is what I changed it to and it works great! Thanks for the help.

Code: Select all

<?php session_start(); session_register('Type'); 
 
if(!isset($_SESSION['Type'])) {
    $_SESSION['Type'] = $_POST['Type'];
} 
?>