Page 1 of 2

PHP based Pagination

Posted: Tue Apr 27, 2010 5:50 am
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

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 1:32 pm
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.

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 1:44 pm
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?

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 1:52 pm
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';
 

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 2:18 pm
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?

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 2:20 pm
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()); 

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 2:37 pm
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?

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 2:42 pm
by Christopher
I looks like on page 0 the code "$lim_start = 15 * ($page - 1);" will produce the invalid -15 start value.

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 2:47 pm
by memsis
what should i do to get around this?

i really appreciate everyones help on this!

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 3:08 pm
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()); 
 

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 3:28 pm
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";
					
				}
?>

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 3:34 pm
by jraede
Where are you setting $total?

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 3:45 pm
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 :(

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 3:58 pm
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.

Re: PHP based Pagination

Posted: Tue Apr 27, 2010 4:06 pm
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?