PHP based Pagination

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

memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

PHP based Pagination

Post by memsis »

hey,

I've created a gallery page that displays images stored in a database dynamically using PHP. That all works fine but now as the site gets bigger I want to try split the images across several pages so as anyone on the site does'nt spend their entire time scrolling. Ideally I'd like 15 or so images per page but the code I'm using won't integrate too well with what I've already done as I'm new to this and not entirely sure what I'm doing.

Code In Header -

Code: Select all

<?php
include('includes/title.inc.php');
// include MySQL connector function
if (! @include('includes/connection.inc.php'))	{
	echo 'Sorry, database unavailable';
	exit;
}
// create a connection to MySQL
$conn = dbConnect('query');
// prepare SQL to retrieve image details
$sql = 'SELECT * FROM images';
// suvmit the query
$result = mysql_query($sql) or die (mysql_error());
// extract the first record as an array

?>
Code In Body -

Code: Select all

<?php
			//Image call start	
				while($row = mysql_fetch_assoc($result)){
					//get the name and caption for the main image
					$mainImage = $row['filename'];
					$caption = $row['caption'];
					// get the dimensions of the main image
					$imageSize = getimagesize('uploads/'.$mainImage);
					
					echo "<p><img src='uploads/".$mainImage."' alt='".$caption."' ".$imageSize[3]." /></p>";
					echo "<p>".$caption."</p>";
				}
				
			//Pagination Function Start			
				function navigate_images($total) {
					global $section, $pg;
					$pages = $total <= $section ? 1 : ceil($total / $section);
				 
					if(trim($_SERVER['QUERY_STRING']) != '') {
						if(stristr($_SERVER['QUERY_STRING'], 'pg='))
							$query = '?'.preg_replace('/pg=d+/', 'pg=', $_SERVER['QUERY_STRING']);
						else
							$query = '?'.$_SERVER['QUERY_STRING'].'&pg=';
					} else
						$query = '?pg=';
				 
					$first = '<a href="'.$_SERVER['PHP_SELF'].$query.'1">«</a>';
					$prev = '<a href="'.$_SERVER['PHP_SELF'].$query.($pg - 1).'">‹</a>';
					$next = '<a href="'.$_SERVER['PHP_SELF'].$query.($pg + 1).'">›</a>';
					$last = '<a href="'.$_SERVER['PHP_SELF'].$query.$pages.'">»</a>';
					
					echo '<div class="navigate_images">';
					echo $pg > 1 ? "$first : $prev :" : '« : ‹ :';
					
					// this is to display a max of 9 links
					$begin = $pg < 6 ? 1 : $pg - 4;
					$end = $begin + 8;
					while($end > $pages)
						$end--;
					for($i=$begin; $i<=$end; $i++)
						echo $i == $pg ? ' ['.$i.'] ' : ' <a href="'.$_SERVER['PHP_SELF'].$query.$i.'">'.$i.'</a> ';
					
					echo $pg < $pages ? ": $next : $last" : ': › : »';
					echo '</div>'."rn";
				}
?>
The page itself can be seen at http://www.thechristakeover.com/gallery.php

The links for the pagination appear at the bottom of the page but don't do anything in regards to navigating through anything. They just refresh the gallery. Any help would be fantastic!!

Chris
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

Where are you actually implementing the pagination? You have to use the LIMIT syntax in your SQL query...right now it seems like all you're doing is passing a page # through a URL query, and then not doing anything with it.
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

im trying to get it working here -
http://www.thechristakeover.com/gallery.php

as i said, i dont really know what im doing. how and were would i put the LIMIT code?
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

You'd need to add it to the end of your query. LIMIT allows two parameters, the starting row, and the rows to display. So, if you're on page 1, and you want 15 images per page, your query would be

Code: Select all

SELECT * FROM images LIMIT 0, 15
or, if you're on page 3, your query would be

Code: Select all

SELECT * FROM images LIMIT 30, 15
So, to take the page value from your get query, you'd do something like this:

Code: Select all

$page = $_GET['pg'];
$lim_start = 15 * ($page - 1);
$query = 'SELECT * FROM images LIMIT '.$lim_start.', 15';
 
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

thats fantastic! only problem now is iv tried putting the code after my query but it still isnt working. which exact line does it need to go after?
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

That code will replace your query. So, instead of

Code: Select all

// prepare SQL to retrieve image details
$sql = 'SELECT * FROM images';
// submit the query
$result = mysql_query($sql) or die (mysql_error()); 
...you'd have

Code: Select all

$page = $_GET['pg'];
$lim_start = 15 * ($page - 1);
$sql = 'SELECT * FROM images LIMIT '.$lim_start.', 15';
// submit the query
$result = mysql_query($sql) or die (mysql_error()); 
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

i replaced my code with yours but it brought up this error -

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-15, 15' at line 1"

is that just a version issue?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PHP based Pagination

Post by Christopher »

I looks like on page 0 the code "$lim_start = 15 * ($page - 1);" will produce the invalid -15 start value.
(#10850)
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

what should i do to get around this?

i really appreciate everyones help on this!
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

Oh, sorry. Forgot to add a check for if the page is 0. Should be this:

Code: Select all

$page = $_GET['pg'];
if($page > 0) {
     $lim_start = 15 * ($page - 1);
}
else {
     $lim_start = '0';
}
$sql = 'SELECT * FROM images LIMIT '.$lim_start.', 15';
// submit the query
$result = mysql_query($sql) or die (mysql_error()); 
 
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

Thats great! its breaks all of the pages up exactly the way i need it to!! The only problem that I have now is that the navigation at the bottom of the page doesnt work. iv tried renaming them and editing the code a little but cant get it to work. changing the page numbers in the address bar works fine though.

this is my code as it stands at the moment

Code: Select all

//Pagination Function Start			
				function navigate_images($total) {
					global $section, $pg;
					$pages = $total <= $section ? 1 : ceil($total / $section);
				 
					if(trim($_SERVER['QUERY_STRING']) != '') {
						if(stristr($_SERVER['QUERY_STRING'], 'pg='))
							$query = '?'.preg_replace('/pg=d+/', 'pg=', $_SERVER['QUERY_STRING']);
						else
							$query = '?'.$_SERVER['QUERY_STRING'].'&pg=';
					} else
						$query = '?pg=';
				 
					$first = '<a href="'.$_SERVER['PHP_SELF'].$query.'1">First Page</a>';
					$prev = '<a href="'.$_SERVER['PHP_SELF'].$query.($pg - 1).'">Previous</a>';
					$next = '<a href="'.$_SERVER['PHP_SELF'].$query.($pg + 1).'">Next</a>';
					$last = '<a href="'.$_SERVER['PHP_SELF'].$query.$pages.'">Last Page</a>';
					
					echo '<div class="navigate_images">';
					echo $pg > 1 ? "$first : $prev :" : 'First Page : Previous :';
					
					// this is to display a max of 9 links
					$begin = $pg < 6 ? 1 : $pg - 4;
					$end = $begin + 8;
					while($end > $pages)
						$end--;
					for($i=$begin; $i<=$end; $i++)
						echo $i == $pg ? ' ['.$i.'] ' : ' <a href="'.$_SERVER['PHP_SELF'].$query.$i.'">'.$i.'</a> ';
					
					echo $pg < $pages ? ": $next : $last" : ': Next : Last Page';
					echo '</div>'."rn";
					
				}
?>
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

Where are you setting $total?
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

the total amount of pages based on the amount of images in the database. its meant to then create the page navigation so as there arent blank pages. im guessing what iv done is very wrong :(
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: PHP based Pagination

Post by jraede »

You can get the total number of results like this:

Code: Select all

$total = mysql_num_rows($result) 
It's kind of hard to follow what you did for the pagination. From what you posted, it looks like you have the right code to change the page in the URL query, but you just weren't defining $total anywhere.
memsis
Forum Newbie
Posts: 15
Joined: Tue Apr 27, 2010 5:41 am

Re: PHP based Pagination

Post by memsis »

im kinda glad you said that. it took me ages to get as far as i have! i thought id have to start all over again. were do i need to define it then? can it just go in replace of what i have?
Post Reply