Pagination

overflo · Oct 12, 2008

Hi I have this function that works great but the volume of photos could get really large. I've tried numerous tutorials on pagination but with no luck.

Does anyone know a simple way to have 15 instances on each page. Any ideas much appreciated

Code:

function show_homepage(){
		
		include_once('../../jj/connect_to_db.php');
		
				$sql=mysql_query("SELECT *
							     FROM tblphoto
								 ORDER BY RAND()");
				$num_rows=mysql_num_rows($sql);
if($num_rows==0){
  $content = "<h2>The Wedding of Jodi & Jimmy</h2>
									  <p>October 10, 2008</p>
									<p>There are currently no photos, please try again later</p>";
}else{
$content = "<h2>The Wedding of Jodi & Jimmy</h2>
										<p>October 10, 2008</p>
										<p>Click on photo to view larger image</p>
									<div id='gallery'>";				 
									 
while(list($ID, $photo, $nameID,$layout)=mysql_fetch_row($sql)){ 
						$qry=mysql_query("SELECT name
										  FROM tblname
										  WHERE ID = '$nameID'");
						list($name)=mysql_fetch_row($qry);
						if($layout=='l'){
							$class = 'land';
							$pos = 'width = 160';
}else{
							$class = 'port';
							$pos = 'height = 150';
						}
$link = $_SERVER['PHP_SELF']."?jj=image_large&ID=$ID&name=$name";
$content .="<div class='imageholder'>
<a href=$link><img src=$photo alt='Wedding Photo' $pos class=$class /></a>
									<div class='text'>
										Taken by: $name
									</div>
								</div>	";		 
	}		
			

$content .=  "</div>";		
			}
		echo $content;
	}

jpadie · Oct 12, 2008

does this code work for you? i've not tested it

Code:

<?php
function show_homepage(){
	//variable for number of records per page
	$numPerPage = 15;

	//get the db connection information
	require_once '../../jj/connect_to_db.php';

	//get the number of records in the resultset
	$result=mysql_query("SELECT count(*) as cnt FROM tblphoto ");
	list($numRecords) = mysql_fetch_array($result);

	//get the number of pages
	$numPages = ceil($numRecords/$numPerPage);
	
	//get current page offset
	$offset = !empty($_GET['offset']) ? $_GET['offset'] : 1;
	
	//calculate record offset
	$recordOffset = ($offset-1) * $numPerPage;
	
	
	//retrieve dataset
	$result = mysql_query("Select p.*, n.name from tblphoto p join tblname n on (p.nameID = n.ID) limit 15 offset $recordOffset");
	
	//check for returns
	if (mysql_num_rows($result) == 0){
		$content = <<<HTML
		
	<h2>The Wedding of Jodi & Jimmy</h2>
	<p>October 10, 2008</p>
	<p>There are currently no photos, please try again later</p>
	
HTML;
	} else {
		$content = <<<HTML
	<h2>The Wedding of Jodi & Jimmy</h2>
	<p>October 10, 2008</p>
	<p>Click on photo to view larger image</p>
	<div id='gallery'>
	
HTML;
		//iterate the recordset
		while ($row = mysql_fetch_assoc($result)){
			if ($row['layout'] == 'l'){
				$class = 'land';
				$pos = 'width = 160';
			}else{
				$class = 'port';
				$pos = 'height = 150';
			}
			$link = $_SERVER['PHP_SELF']."?jj=image_large&ID=$ID&name=$name";
			$uname = urlencode($row['name']);
			$content .= <<<HTML
			
	<div class='imageholder'>
		<a href="{$_SERVER['PHP_SELF']}?jj=image_large&ID={row['ID']}&name=$uname">
		<img src="{$row['photo']}" alt='Wedding Photo' $pos class=$class />
		</a>
		<div class="text">
		Taken by: {$row['name']}
		</div>
	</div>
HTML;
			//now get a clickable list of links for pages
			//with some styled boxes
			$content .= <<<CSS
			
	<style type="text/css">
	.page {display:block; float:left; width:2em; padding: 2px; margin:1px; border:thin solid black; background-color:transparent;}
	.current {background-color:red;}
	</style>
	<div id="pageLinks">
	
CSS;
			for ($i=1; $i<=$numPages; $i++){
				$class = ($offset == $i) ? 'page current' : 'page';
				$content .= <<<HTML
		<span class="$class"><a href="{$_SERVER['PHP_SELF']}?action=getPage&offset=$i">$i</a></span>

HTML;
			} //end for loop
			$content .= "\r\n </div>\r\n"; //close pagelinks div
		} //end of while clause
	$content .=  "</div>"; 	
	}//end of if else
	echo $content;
}	//end of function
?>

ingresman · Oct 13, 2008

Pagination is often a challenge.
For example in the example code the same query is run every time a page is required. Depending on how complex the query is this can become a performance bottleneck quite quickly and that impacts your ability to scale. It does mean you always get an up to date view of the data which is good. However if you can handle some data being a bit stale you could run the query once and put the output into a temporary table and then page on that without having the overhead of the original query, you could also put it into a temporary file but you might run into issues if your in a web farm.
Another hit you get when you query against the original data is you always do a count(*) which is another potentialy performance hit. I can't recall if mysql always scans the table or takes a guess from the table statistics. If it does guess you might not get to see all the records (or not too many !!!) anyhow.

spamjim · Oct 13, 2008

You could do just one query to list the items. The query could be limited to 16 items but PHP would only count and display 15. If the 16th item was present in the MySQL output, you can render a 'more' link.

jpadie · Oct 13, 2008

count(*) is optimised provided there are no joins and no where clause. at least on myIsam tables.

at spamjim: that solution would not provide a list of pages, which is what I understood by the OP's question. You could reduce the query overhead a fractional amount by using SQL_FOUND_ROWS methodology.

@ingresman: if there is no where clause then surely a temp table is going to be a simply mirror of the original table? That is not to say that cacheing in general would not be a good thing.

Motolix · Oct 13, 2008

Not sure if this will work, I don't have my IDE with me.

Code:

$numPerPage = 15;

//We'll pass the page through our query string. So check it,
//and assign it to a var.
$page = (!empty($_GET['pg'])&&eregi("^[0-9]{1,9}$", $_GET['pg'])) ? $_GET['pg'] : 0;

$start = $page * $numPerPage;
$end = $start + $numPerPage;

$results = mysql_query("SELECT * FROM `table_name` LIMIT $start,$end");

$count = mysql_num_rows($results);

if(empty($count)) {
   //echo 'Sorry, no items to be displayed';
} else {
   while($rows = mysql_fetch_array($results)) {
      //This will list off your data. 
      //mysql_fetch_array() is quick, and returns an array
      //echo $rows['your_field'];
   }
}
//If you want to display page numbers;
//First you have to find out how many pages, simple math:
$numOfPages = ciel($count / $numPerPage);

for($i=0; $i<=$numOfPages; $i++) {
   $pages[] = '<a href="yourpage.php?pg='.$i.'">'.($i+1).'</a>';      
}

echo join(PHP_EOL, $pages); //Print a list of page links with your server's "End Of Line" (Windows and Unix use different ends \r\n and \n (respectively, I believe))

Haven't tested it, but if anything the concept should work for what you need. I hope it helps.

jpadie · Oct 13, 2008

@motolix
that's not going to work for a whole bunch of reasons

1. you've misused the LIMIT OFFSET part of the query string
2. you're calculating the number of pages based on the number of records returned from a LIMIT query
3. ciel should be ceil

can you help the future readers by explaining how your code works and perhaps where it differs and improves on the code that I posted?

Motolix · Oct 13, 2008

1. LIMIT will work with the second parameter.

LIMIT 0,3;
LIMIT 3 OFFSET 0; works just as well though. Nothing against it.

2. My mistake, apologizes to anyone who was confused. $count should be a mysql query to retrieve the full count. Personally I use count(*).

3. Sorry.

My script was not meant to differ from or improve upon yours, more just a non-descript way to handle pages. If he had trouble finding this in a tutorial, then I hoped to help any future users with a very basic example. I wrote it in the reply window though, so sorry for the errors.

ingresman · Oct 14, 2008

I would say that even if you limit the query to only return 16 rows, mysql will still run the entire query before it gives you the rows. Even you have an order by it gets even worse as believe mysql will start returning the rows as soon as possible.
@jpaddie, yes I agree if the select was from one table the cache version would be the same (perhaps smaller) but the query given in th OP code does a join tblphoto and tblname which is where I think the cache would give some benefit

jpadie · Oct 14, 2008

that's interesting. i don't know enough about the internal workings of rdbs to opine further. might be a good topic for the mysql forum?

ingresman · Oct 14, 2008

yes good idea, the cache thing is realy interesting memcached comes with an engine for mysql so you can in effect create cached tables directly.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Pagination

overflo

Technical User

jpadie

Technical User

ingresman

IS-IT--Management

spamjim

Instructor

jpadie

Technical User

Motolix

Programmer

jpadie

Technical User

Motolix

Programmer

ingresman

IS-IT--Management

jpadie

Technical User

ingresman

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor