Problem With Pagination Script 10.4 - Count Function

[Wagtail]

Hi Larry and forum members,

 

I'm using the pagination script (script 10.4 from chapter 10) and have run into a few problems. When I click next or another number, the page will load but not display any records. If I then click the number one, page one will load but also not display any records. The first time around the page will load and display the number of records as per $display =somenumber;

 

My query returns columns from multiple tables. Ordinarily, my query looks something like this:

$q = "SELECT a number of columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age'";

The above query has been modified below, but I'm not sure if the query correctly counts the number of records in the database. The users table includes the foreign keys from the other tables. Must I also use the COUNT() function on these columns?

$q = "SELECT COUNT(user_id), other columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age' LIMIT $start, $display";

What I'm looping out consists of a paragraph, an image and other elements which are contained within div tags. Don't know if this is relevant but I thought I'd mention it.   

 

I hope the information I provided is not too cryptic.

 

 

 

Thank you in advance!

 

 

[Wagtail]

Hi all,

 

I have figured it out. Subsequent pages weren't working because the query no longer received the values from the $name and $age variables.

 

I thus appended these to the end of the URLs for the previous button, all the numbered pages and the next button.

echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&name='.$name.'&age='.$age.'">Previous</a> ';

There might be another solution, but this works.

 

Thanks.

[Larry]

Yep, that's exactly what you need to do. Kudos for figuring it out and thanks for sharing!

[Wagtail]

Hi Larry,

 

I do need some help after all. I've reached my 'PHP limit' so to speak.

 

What I'm now attempting is to use check boxes to filter the paginated results. The code does work, albeit not completely. When a check box has been checked, the page will load with the desired results but I still have links to subsequent pages when there shouldn't be any. I again added a variable (the filter variable) to the end of the URLs in case there is more than one page. Do you know what the problem could be?

 

Thank you very much.

// Number of records to show per page:
$display =3;

// Determine how many pages there are...
if (isset($_GET['p']) && is_numeric($_GET['p'])) { // Already been determined.
	$pages = $_GET['p'];
	
} else { // Need to determine.
 	// Count the number of records:
	$q = "SELECT COUNT(user_id), other columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age'";
	$r = @mysqli_query ($dbc, $q);
	$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
	$records = $row[0];
	// Calculate the number of pages...
	if ($records > $display) { // More than 1 page.
		$pages = ceil ($records/$display);
	} else {
		$pages = 1;
	}
} // End of p IF.

// Determine where in the database to start returning results...
if (isset($_GET['s']) && is_numeric($_GET['s'])) {
	$start = $_GET['s'];
} else {
	$start = 0;
}



// if a check box has been checked, it is then assigned to the $string variable

if (isset($_POST['city'])) {
$start = 0; // added this here to start filtering from page1	
$string = "name = '{$_POST['city']}'";
}

// if the check box 'all' has been checked, all the records are returned

if (isset($_POST['all'])) { 
$q = "SELECT columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age' LIMIT $start, $display";
}

// if the $string variable is set, then the query will be used with the $string variable, otherwise all the records are returned

if (isset($string)) {

{
$q = "SELECT columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age' && '.$string.' LIMIT $start, $display";
}

else {
$q = "SELECT columns FROM
table A INNER JOIN table B JOIN_CLAUSE WHERE
name = '$name' && age = '$age' LIMIT $start, $display";
}
}
 
      
$r = @mysqli_query($dbc, $q);	
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
 Print '<p>loop content here</p>';
 }
 
// Make the links to other pages, if necessary.
if ($pages > 1) {
	
	echo '<br /><p>';
	$current_page = ($start/$display) + 1;
	
	// If it's not the first page, make a Previous button:
	if ($current_page != 1 && isset($string))  {
		echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&name='.$name.'&age='.$age.'&'.$string.'">Previous</a> ';
	} else {
	echo '<a href="view_users.php?s=' . ($start - $display) . '&p=' . $pages . '&name='.$name.'&age='.$age.'">Previous</a> ';	
		
	}
	
		
	// Make all the numbered pages:
	for ($i = 1; $i <= $pages; $i++) {
		if ($i != $current_page && isset($string)) {
			echo '<a  href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&name='.$name.'&age='.$age.'&'.$string.'">' . $i . '</a> ';
		}
		elseif ($i != $current_page) {
		echo '<a  href="view_users.php?s=' . (($display * ($i - 1))) . '&p=' . $pages . '&name='.$name.'&age='.$age.'">' . $i . '</a> ';
		}
		
		
		else {
			echo $i . ' ';
		}
	} // End of FOR loop.
	
	
	
	// If it's not the last page, make a Next button:
	if ($current_page != $pages && isset($string)) {
		echo '<a  href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&name='.$name.'&age='.$age.'&'.$string.'">Next</a>';
	} else
	{
		echo '<a  href="view_users.php?s=' . ($start + $display) . '&p=' . $pages . '&name='.$name.'&age='.$age.'">Next</a>';
	}
	
	
	
	
	
	echo '</p>'; // Close the paragraph.
	
} // End of links section.