Hi,

I am trying to Paginate Query Results.

The values are passed through a form allowing users to search a database based on certain criteria they select.
Now I got two problems though,
1) I can't get all the results from the database(as not all the criteria is working) ?
2) If there is more than 1 page, the next page does not carry over any more results, its just blank even though there should be more results.

Here is my code:

<?php
$page_title = 'Basic Search Results';
include ('../header.php');
	
     if (isset($_POST['submitted'])) {
          
         $g1 = $_POST['gender1'];
         $a = $_POST['age'];
         $a1 = $_POST['age1'];
         $c = $_POST['country'];

require_once('../Includes/myConn.php');
		
$display = 2;
			
if(isset($_GET['np'])) {
     $num_pages = $_GET['np'];
} else {
				
$q = "SELECT COUNT(*) FROM Members,Profile,MainPictures ORDER BY Date_Signed_Up ASC";
$r = mysql_query($q);
					
$row = mysql_fetch_array($r,MYSQL_NUM);
$num_records = $row[0];
					
if($num_records > $display) {
         $num_pages = ceil($num_records/$display);
} else {
         $num_pages = 1;
}
}
				 
if(isset($_GET['s'])) {
    $start = $_GET['s'];
} else {
    $start = 0;
}
				
$q = "SELECT Members.Member_ID,Members.Username,DATE_FORMAT(Members.Date_Signed_Up,'%a %b %d, %y - %T %p')AS RegDate,
Profile.Member_ID,Profile.Profile_Age,Profile.Profile_Gender,Profile.Profile_County,Profile.Profile_Country,MainPictures.Member_ID,MainPictures.SearchImage,MainPictures.PictureStatus  
FROM Members,Profile,MainPictures 
WHERE Profile.Profile_Gender='$g1' AND Profile.Profile_Age BETWEEN '$a' AND '$a1' AND Profile.Profile_Country='$c' 
ORDER BY RegDate DESC LIMIT $start, $display";
$r = mysql_query($q);
$num = mysql_num_rows($r);
if($num > 0) {

echo '<h2>Search Results</h2>
<p class="abt">There were&nbsp;'; 
echo "<font color='#2cb6e9'>$num</font>"; 
echo '&nbsp;results found based on your criteria</p><br />';
					
while($row = mysql_fetch_array($r,MYSQL_ASSOC)) {
echo '<div class="thumbnail">';
	if($row['PictureStatus'] == 'Accepted') {
		echo '<img src='.$row['SearchImage'].' />';
	} else if ($row['PictureStatus'] == 'Rejected') {
		echo '<img src="../Profiles/Images/Display/image_122x122.gif" />';
	} else {
		echo '<img src="../Profiles/Images/Display/image_122x122.gif" />';
}
echo '<p>';
	echo ''.$row['Username'].'<br />';
	echo ''.$row['RegDate'].'<br />';			echo ''.$row['Profile_Age'].'<br />';
	echo ''.$row['Profile_County'].'<br />';		             echo ''.$row['Profile_Country'].'<br />';
	echo '</p>';
	echo '</div>';
}
				
mysql_free_result($r);
mysql_close();
				
if($num_pages > 1) {
echo '<br />';
$current_page = ($start/$display) + 1;
echo '<div id="lnks">';
if($current_page != 1) {
echo '<a href="viewBasicResults.php?s='.($start - $display).'&np='.$num_pages.'&gender1='.urlencode($g1).'&age='.urlencode($a).'&age1='.urlencode($a1).'&country='.urlencode($c).'" class="alphabet">Previous</a>';
}
for($i = 1; $i <= $num_pages; $i++) {
if($i != $current_page) {
echo '<a href="viewBasicResults.php?s='.(($display * ($i - 1))).'&np='.$num_pages.'&gender1='.urlencode($g1).'&age='.urlencode($a).'&age1='.urlencode($a1).'&country='.urlencode($c).'" class="alphabet">'.$i.'</a>';
} else {
echo $i.' ';
}
}
if($current_page != $num_pages) {
echo '<a href="viewBasicResults.php?s='.($start + $display).'&np='.$num_pages.'&gender1='.urlencode($g1).'&age='.urlencode($a).'&age1='.urlencode($a1).'&country='.urlencode($c).'" class="alphabet">Next</a>';
}
echo '</div>';
}
} else {
	echo '<h2>Search Results</h2><p class="abt">
	Sorry no results have been found based on your search criteria</p>';
}
}

Any help would be much appreciated.

Thanks

Have you tried an OOP approach. I use an OOP paginator on my site, and I usually find OOP much easier to use. Why not try creating a class to hold a result and then create an array of the result objects. You can figure page numbers by using ceil(count($resultArray)/$itemsPerPage) . If you wish to take this approach, I can write a simple pagination class for you to take a look at. Let me know.

EDIT:
Just to clarify, to determine the number of pages, you need to round up since .5 a page would mean you need an extra page to show those extra results that don't fit perfectly on a page.

Hi,

Thanks for your reply, If you could write a simple pagination class for me to take a look at that would be much appreciated.

Thanks

Alright here's a little example. Simply create an instance of pmcPagination and call $instance->add(); with the only argument being an instance of paginationData() with one data entry to be included in the pagination or an array of these instances. To show the page call $instance->paginate() . You can pass the number of results per page and/or name of a get variable to use as the page number (and will also be used for all links..the default is "page", so the urls will be ?page=1). Most of the other functions of pmcPagination() are pretty self-explanatory. I've provided the file that holds the classes that you should include for pagination and a demo file..so showcase the features:

You can find the zip file: http://files.phpmycoder.net/1209da/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.