Pagination - works without WHERE

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
freeworld
Forum Newbie
Posts: 4
Joined: Tue Sep 29, 2009 9:13 pm

Pagination - works without WHERE

Post 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"); ?>
 
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Pagination - works without WHERE

Post by requinix »

And you're sure there should be more than one page of results?
freeworld
Forum Newbie
Posts: 4
Joined: Tue Sep 29, 2009 9:13 pm

Re: Pagination - works without WHERE

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Pagination - works without WHERE

Post 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.
freeworld
Forum Newbie
Posts: 4
Joined: Tue Sep 29, 2009 9:13 pm

Re: Pagination - works without WHERE

Post 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?
freeworld
Forum Newbie
Posts: 4
Joined: Tue Sep 29, 2009 9:13 pm

Re: Pagination - works without WHERE

Post 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'];
} 
?>
Post Reply