Pagination Help

[RalphLeMouf]

Hello,

 

I am just about complete with my first pagination project, however I am missing a few steps or not doing something quite right. I would prefer to keep what I have and just tweak it as opposed to a new structure suggestion.

 

Thanks in advance.

 

On my site we have a feature call "Airwaves" Its more or less a status update. I want to limit 40 per page

 

$query = "SELECT COUNT(*) FROM `CysticAirwaves`";
			$request = mysql_query($query,$connection);
			$result = mysql_fetch_array($request);
			$ammount_of_waves = $result['COUNT(*)'];

			$currentPage  = 1;  
			$wavesPerPage = 40;
			$totalPages = ceil($ammount_of_waves / $wavesPerPage);
			$offset = ($wavesPerPage * ($currentPage - 1)) + 1;

			$waves_query = "SELECT * FROM `CysticAirwaves` LIMIT $offset, $wavesPerPage";
			$request = mysql_query($waves_query,$connection);
			$result = mysql_fetch_array($request);


			?>
			 <?php	if ($totalPages > 1) { ?>
				<div id="all_page_turn">
			        <ul>
						<li class="PreviousPageBlog round_10px">

		    				<?php if ($currentPage > 1) { ?>
		        				<a href="http://www.cysticlife.org/Airwave_build.php?page=$currentPage - 1">Previous</a>
		    				<?php } ?>
						</li>
							<?php
		    				for ($i = 1; $i <= $totalPages; $i++) {
		        				$class = ($i == $currentPage) ? 'selected' : null ?>
								<a href="http://www.cysticlife.org/Airwave_build.php?page=$i" class="$class">Page $i</a>
		  					<?php  } ?>
						 <li class="NextPageBlog round_10px">
		    				<?php if ($currentPage < $totalPages) { ?>
		        				<a href="http://www.cysticlife.org/Airwave_build.php?page=$currentPage + 1">Next</a>

							<?php }
				    	}?>
						?>
					</li>
				</ul>
			</div>

[Maq]

What exactly is the problem?

[RalphLeMouf]

It's not doing what it should be doing. It does not limit the page to 40 and the link that says "next" is broken. Its show at least 40 or 50 blue links that say "Page$i"

[Maq]

It's not doing what it should be doing. It does not limit the page to 40 and the link that says "next" is broken. Its show at least 40 or 50 blue links that say "Page$i"

First off, none of the variable are interpolating because you have them literally as HTML.  When you're using PHP variables in HTML, you need to put them in tags or echo the HTML out, i.e.

 

			    				 1) { 
		        				echo "Previous";
		    				   } ?>

Fix all of those first.

[Psycho]

1. The offset is incorrect. The +1 at the end would cause the records to be shifted forward one record. So, page one would start at the 2nd record and every page would be shifted by one as well.

$offset = ($wavesPerPage * ($currentPage - 1)) + 1;

 

2. You are hard-coding the current page, so you would never be able to get to any other page. I.e. you next/prev and individual page links would all show page 1

$currentPage  = 1; 

 

3. You can't do math in HTML

<a href="http://www.cysticlife.org/Airwave_build.php?page=$currentPage - 1">

<a href="http://www.cysticlife.org/Airwave_build.php?page=$currentPage + 1">Next</a>

That will create URLs that would look like this

<a href="http://www.cysticlife.org/Airwave_build.php?page=2 - 1">

<a href="http://www.cysticlife.org/Airwave_build.php?page=2+ 1">Next</a>

You need to do the math in PHP for the prev/next page and then use that in the URL. Also, referring to #2 above these parameters for the page number on the URL would have no effect since you are hard-coding the page to display.

 

#4. Again, HTML does not do math

<a href="http://www.cysticlife.org/Airwave_build.php?page=$i" class="$class">Page $i</a>

Try this

<a href="http://www.cysticlife.org/Airwave_build.php?page=$i" class="$class">Page <?php echo $i; ?></a>

 

I will say that the logic in your code isn't terrible, but it is far from ideal (even when the defects are fixed). Your statement about

I would prefer to keep what I have and just tweak it as opposed to a new structure suggestion.

shows, to me at least, that you are not really interested in learning.

[RalphLeMouf]

Thanks guys, this is great. How would I correct the offset?

[Psycho]

How would I correct the offset?

 

Really? Did you read what I posted.

1. The offset is incorrect. The +1 at the end would cause the records to be shifted forward one record. So, page one would start at the 2nd record and every page would be shifted by one as well.

[RalphLeMouf]

Yes I read and took off the +1. Also I AM WILLING to learn. I just thought I was super close but if you have a better more "ideal" suggestion and am more than open for it. Ifnot, I am a little confused on your description of hard coding $currentPage. Can't really figure out what I should put there instead. Thanks again fro working with me on this

[Psycho]

There is a tutorial on pagination on this site: http://www.phpfreaks.com/tutorial/basic-pagination

 

I sei no need to try and explain everything that has already been explained there (as well as other tutorials freely available). Not trying to be an a**, but I'd suggest reading that tutorial and then go back to what you have. If you have questions after reading that, then by all means post them.

 

I will respond to this statement though

I am a little confused on your description of hard coding $currentPage

I'm not sure I understand your confusion. You have this line in your code

$currentPage  = 1; 

 

So, every time the script is run you are setting the current page to page 1. If you click one of your pagination links you are setting a value on the query string which set a value for $_GET['page']. But, since you don't do anything with that value the "current page" will always be 1. Take a look at the tutorial, it shows you have to incorporate $_GET['page'] into the pagination.

[RalphLeMouf]

@mjdamato -  I read and understood the logic of the tutorial and adapted it to my needs  with no avail. It is yielding no results what so ever. 

 

Here is the code:

 

// find out how many rows are in the table 
		$query = "SELECT COUNT(*) FROM `CysticAirwaves`";
		$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
		$r = mysql_fetch_row($result);
		$numrows = $r[0];

		// number of rows to show per page
		$rowsperpage = 40;
		// find out total pages
		$totalpages = ceil($numrows / $rowsperpage);

		// get the current page or set a default
		if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
		   // cast var as int
		   $currentpage = (int) $_GET['currentpage'];
		} else {
		   // default page num
		   $currentpage = 1;
		} // end if

		// if current page is greater than total pages...
		if ($currentpage > $totalpages) {
		   // set current page to last page
		   $currentpage = $totalpages;
		} // end if
		// if current page is less than first page...
		if ($currentpage < 1) {
		   // set current page to first page
		   $currentpage = 1;
		} // end if

		// the offset of the list, based on current page 
		$offset = ($currentpage - 1) * $rowsperpage;

		// get the info from the db 
		$query2 = "SELECT `id` FROM `CysticAirwaves` LIMIT $offset, $rowsperpage";
		$result = mysql_query($query2, $conn) or trigger_error("SQL", E_USER_ERROR);

		// while there are rows to be fetched...
		while ($list = mysql_fetch_assoc($result)) {
		   // echo data
		   echo $list['id'] . " : " . $list['number'] . "<br />";
		} // end while

		/******  build the pagination links ******/
		// range of num links to show
		$range = 3;

		// if not on page 1, don't show back links
		if ($currentpage > 1) {
		   // show << link to go back to page 1
		   echo " <a href='http://www.cysticlife.org/Airwaves_build.php?currentpage=1'><<</a> ";
		   // get previous page num
		   $prevpage = $currentpage - 1;
		   // show < link to go back to 1 page
		   echo " <a href='http://www.cysticlife.org/Airwaves_build.php?currentpage=$prevpage'><</a> ";
		} // end if 

		// loop to show links to range of pages around current page
		for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
		   // if it's a valid page number...
		   if (($x > 0) && ($x <= $totalpages)) {
		      // if we're on current page...
		      if ($x == $currentpage) {
		         // 'highlight' it but don't make a link
		         echo " [<b>$x</b>] ";
		      // if not current page...
		      } else {
		         // make it a link
		         echo " <a href='http://www.cysticlife.org/Airwaves_build.php?currentpage=$x'>$x</a> ";
		      } // end else
		   } // end if 
		} // end for

		// if not on last page, show forward and last page links        
		if ($currentpage != $totalpages) {
		   // get next page
		   $nextpage = $currentpage + 1;
		    // echo forward link for next page 
		   echo " <a href='http://www.cysticlife.org/Airwaves_build.php?currentpage=$nextpage'>></a> ";
		   // echo forward link for lastpage
		   echo " <a href='http://www.cysticlife.org/Airwaves_build.php?currentpage=$totalpages'>>></a> ";
		} // end if
		/****** end build pagination links ******/
		?>
		</div>

[TrueColors]

I have a little class you may use.

 

The class uses a db handler aswell... which is what $db is. It features functions like "execute" and a few others. You may look in the class to see what is used. You can replace them with mysql_* or whatever you desire if you do not have a $db handler. It'll require some changes but it would be fine.

 

the 20 inside the instancing of the class, is the amount of rows per page to be displayed. The "page" is the ID, so ... url would be www.site.com/index.php?page=1 (note the "page" in the url)

 

The query below also uses the FULL query. The class handles it and uses some changes to create the count() function in mysql.

 

The rest is self explanatory. Here is this little bit:

$page = new Pagination ( $db, 20, "page" );
$page -> buildQueryString ();

$page -> setQuery ( "SELECT column FROM table" );

$page -> setNumberRows ();
$page -> setPages ();
$page -> setCurrentRow ();

foreach ( $page -> getResults () as $result )
{
    // your content here... $result is an array of the columns from your database.
}

 

Here is the class...

<?php
/**
* Name: Pagination Object
* Author: Andrew Judd
* Date: May 14, 2011
*/
class Pagination
{
    /**
     * An instance of the database object
     */
    private $db = NULL;

    /**
     * The query which will be run.
     */
    private $query = NULL;

    /**
     * The total number of rows that the full query will return.
     */
    private $numRows = NULL;

    /**
     * The number of rows to return per page.
     */
    private $perPage = NULL;
    
    /**
     * The number of the current page.
     */
    private $curPage = NULL;
    
    /**
     * The number of the first page.
     */
    private $firstPage = 1;
    
    /**
     * The number of the last page.
     */
    private $lastPage = NULL;
    
    /**
     * The number of the current row.
     */
    private $curRow = NULL;
    
    /**
     * The $_GET page identifier
     */
    private $pageID = NULL;
    
    /**
     * The url QUERY string
     */
    private $queryString = NULL;

    /**
     * The three-argument constructor which takes in the connection to the database.
     * @param mixed $db - The database object
     * @param int $perPage - The number of rows per page
     * @param string $pageIdentifier - The page identifier
     */
    public function __construct ( $db, $perPage = 20, $pageIdentifier = 'page' )
    {
        $this -> db = $db;
        $this -> perPage = $perPage;
        $this -> pageID = $pageIdentifier;
    }
    
    /**
     * This function will build the URL Query String and remove any current page ID's
     */
    public function buildQueryString ()
    {
        if ( isset ( $_SERVER [ 'QUERY_STRING' ] ) && !empty ( $_SERVER [ 'QUERY_STRING' ] ) )
        {
            $ex = explode ( "&", $_SERVER [ 'QUERY_STRING' ] );
            
            foreach($ex as $k => $query)
            {
                if ( preg_match ( "/" . $this -> pageID . "(.*)\b/", $query ) )
                    unset ( $ex [ $k ] );
            }
            
            if ( count ( $ex ) > 0 )
            {
                $this -> queryString = implode ( "&", $ex ) . '&';
            }
        }    
    }

    /**
     * The setter for the query.
     * @param string $query - The query which will be run
     */
    public function setQuery ( $query )
    {
        $this -> query = $query;
    }
    
    /**
     * This function will set the current and last page.
     */
    public function setPages ()
    {
        $this -> lastPage = ceil ( $this -> numRows / $this -> perPage );
        
        if ( !isset ( $_GET [ $this -> pageID ] ) || $_GET [ $this -> pageID ] < $this -> firstPage)
        {
            $this -> curPage = $this -> firstPage;
        }
        else if ( $_GET [ $this -> pageID ] > $this -> lastPage)
        {
            $this -> curPage = $this -> lastPage;
        }
        else
        {
            $this -> curPage = $_GET [ $this -> pageID ];
        }
    }
    
    /**
     * This function will set the current row based upon
     * the current page and rows per page
     */
    public function setCurrentRow ()
    {
        $this -> curRow = ( $this -> curPage - 1 ) * $this -> perPage;
    }

    /**
     * This function is used in order to set the number of rows
     * that will be returned from the full query.
     */
    public function setNumberRows ()
    {
        /* Make sure that the query is set */
        if ( $this -> query == NULL )
        {
            /* Query wasn't set, so the number of rows is -1 */
            $this -> numRows = -1;
        }
        else
        {
            /* Build the query to retrieve how many rows are present */
            $sql = preg_replace ( '/SELECT (.*) FROM\b/'
                        , 'SELECT COUNT(*) AS NumRows FROM'
                        , $this -> query );
            $query = $this -> db -> execute ( $sql );
            
            $r = $this -> db -> fetchassoc ( $query );
            
            /* Return the number of rows that are present */
            $this -> numRows = $r [ 'NumRows' ];
        }
    }
    
    /**
     * This function is used in order to retrieve an array of results
     * that will be returned from the full query
     */
    public function getResults ()
    {
        /* Build and execute the query to retrieve the amount of rows set */
        $sql = $this -> query . ' LIMIT ' . $this -> curRow . ', ' . $this -> perPage;
        $query = $this -> db -> execute ( $sql );
        
        $results = array ();
        
        while ( $row = $this -> db -> fetchassoc ( $query ) )
        {
            $results[] = $row;
        }
        
        return ( $results );
    }
    
    /**
     * This function is used to retrieve a list of Pages which would
     * be used for the page navigation
     */
    public function getPageNavigation ()
    {
        $links = '';
        
        if ( ( $this -> curPage - 5 ) > $this -> firstPage )
            $links .= '<a href="?' . $this -> queryString . $this -> pageID . '=' . $this -> firstPage . '">First</a> ... ';    
            
        for ( $i = ($this -> curPage - 5 ); $i <= ( $this -> curPage + 5); $i++ )
        {
            if ( $i >= $this -> firstPage && $i <= $this -> lastPage )
            {
                $links .= '<a href="?' . $this -> queryString . $this -> pageID . '=' . $i . '">' . ( ( $i == $this -> curPage ) ? "[$i]" : $i ) . '</a> ';
            }
        }
        
        if ( ( $this -> curPage + 5 ) < $this -> lastPage )
            $links .= ' ... <a href="?' . $this -> queryString . $this -> pageID . '=' . $this -> lastPage . '">Last</a>';
            
        return($links);
    }
}
?>

 

If you don't want to use it, it's fine, don't worry about it. If you have a database handler already... just pass it through when you instance the class... and there is no need to alter the class at all.

 

Just thought I'd share my little ... pagination

 

[Psycho]

Are you getting any errors? If so, what are they?

 

If you are not getting any errors and there are no records displayed then I would assume the main query (to get the records)  is not returning any results. Although, I do notice that your query is only selecting the ids and you are trying to display the value of 'number' which wasn't pulled in the query.

 

Anyway, here is a rewrite of your script. I made a lot of changes. I even changed some variable names to create some sort of consistency.

You may not agree with the names I chose, but frankly, I don't care. Your structure and naming conventions suck. Additionally, I created a debugging section to echo some details to the page so you can see if the variables and results are what you expect. If something is not an appropriate value (or no value is shown) then you know where to start looking for the problem. You should always include debugging logic in your code. Otherwise when you don't get the results you expect you have no clue where to start looking.

 

FYI: one of the possible reasons you were not seeing anything is that you were creating the prev/next links using < and > characters. Those might be interpreted as HTML tag enclosures, so you should escape them using < and > when you want them interpreted as text.

<?php

// find out how many rows are in the table 
$query = "SELECT COUNT(*) FROM `CysticAirwaves`";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);
$row = mysql_fetch_row($result);
$total_records = $row[0];

// number of records to show per page
$records_per_page = 40;
// find out total pages
$total_pages = ceil($total_records / $records_per_page);

// get the current page or set a default
if (isset($_GET['page']) && is_numeric($_GET['page']))
{
    //Cast var as int
    $current_page = (int) $_GET['page'];
    //Ensure $current_page is within min/max values
    $current_page = max($current_page, 1);
    $current_page = min($current_page, $total_pages);
}
else
{
    // default page num
    $current_page = 1;
}

// the offset of the list, based on current page 
$offset = ($current_page - 1) * $records_per_page;

// get the info from the db 
$query = "SELECT `id`, `number` FROM `CysticAirwaves` LIMIT $offset, $records_per_page";
$result = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

## DEBUG INFO ##
echo "<b>Debug Info</b><br><br>\n";
echo "Total records: {$total_records}<br>\n";
echo "Records per page: {$records_per_page}<br>\n";
echo "Total pages: {$total_pages}<br>\n";
echo "Current page: {$current_page}<br>\n";
echo "Offset: {$offset}<br>\n";
echo "Data Query: {$query}<br>\n";
echo "Records returned: " . mysql_num_rows($result) . "<br>\n";

// while there are rows to be fetched...
while ($row = mysql_fetch_assoc($result))
{
    // echo data
    echo "{$row['id']} : {$row['number']}<br />\n";
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

//function to create the links, to simplify the script
$baseURL = "http://www.cysticlife.org/Airwaves_build.php?page=";
function createPageLink($baseURL, $pageNo, $linkText)
{
    return "<a href='{$baseURL}{$pageNo}'>{$linkText}</a>";
}

// if not on page 1, show back links
if ($current_page > 1)
{
    // show << link to go back to page 1
    echo createPageLink($baseURL, 1, "<<");
    // show < link to go back to 1 page
    echo createPageLink($baseURL, ($current_page-1), "<");
} // end if 

// loop to show links to range of pages around current page
$page_start = max(($current_page-$range), 1);
$page_end   = min(($current_page + $range), $total_pages);
for ($x = $page_start; $x <= $page_end; $x++)
{
    // if we're on current page...
    if ($x == $current_page)
    {
        // 'highlight' it but don't make a link
        echo " [<b>$x</b>] ";
        // if not current page...
    }
    else
    {
        // make it a link
        echo createPageLink($baseURL, $x, $x);
    }
} // end for

// if not on last page, show next and last page links
if ($current_page < $total_pages)
{
    // show > link to go forward 1 page
    echo createPageLink($baseURL, ($current_page+1), ">");
    // show >> link to go to last page
    echo createPageLink($baseURL,  $total_pages, ">>");

} // end if

/****** end build pagination links ******/
?>

</div>