[Resolved] Help fixing page numbering code

[kilbey1]

I've got page numbering returning results coorrectly, but I can't figure out why it's not moving to the next page in the link. In my test data, I am pulling back 11 records; with a limit of 10, I am showing 2 pages total. However, when I click on the '2' link, it returns to my default case page.

Here's the relevant code...

	case "showcandidates":			

	if(!isset($_GET['page'])){
		$page = 1;
	} else {
		$page = $_GET['page'];
	} 

	// Define the number of results per page
	$max_results = 10;

	// Figure out the limit for the query based
	// on the current page number.
	$from = (($page * $max_results) - $max_results); 

<...snip...>

			// Perform MySQL query on only the current page number's results
			// Select, Where and From clause done - add it to the SQL skeleton
			$sql = "" . $sSelect . " " . $sFrom . " " . $sWhere . " LIMIT " . $from . ", " . $max_results;
			echo $sql . "<P>";

			$result = mysql_query($sql) or die("Error: " . mysql_error());

			// Figure out the total number of results in DB:
			$total_results = mysql_result(mysql_query("SELECT COUNT(*) ". $sFrom . " " . $sWhere .""),0);

			// Figure out the total number of pages. Always round up using ceil()
			$total_pages = ceil($total_results / $max_results);

			if(mysql_num_rows($result) == 0) {
				echo "<strong>No results to display.</strong> <BR>
				Try expanding your search be eliminating some fields in your search.";

			} else {


?>


 - results get displayed in while loop - 

<br>

	<?php

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
	$prev = ($page - 1);
	echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a>&nbsp;";
}

for($i = 1; $i <= $total_pages; $i++){
	if(($page) == $i){
		echo "$i&nbsp;";
		} else {
			echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a>&nbsp;";
	}
}

// Build Next Link
if($page < $total_pages){
	$next = ($page + 1);
	echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}
echo "</center>"; 

?>

<P>
	<a href="<?php echo $PHP_SELF;?>">Return</a>



<?php			
	} //end if $num_rows == 0
	break;

[AtroZ]

¿How do you get to case "showcandidates"?

I think the problem might be that the values that bring you to that case are missing when you reload your page with only the page number in your querystring.

If you have a switch(somevar) in your script, you should add the corresponding value to the var when you reload your page to get to the correct place in the script.

Maybe something like

if(isset($_GET["somevar"])) 
   $somevar="showcandidates";

would help you.

[kilbey1]

ok, I got somewhere by adding to my links '?goto=showcandidates&page=$page. However, when I echo out the SQL statement, I get this:

SELECT candidate.Candidate_ID, candidate.Last_Name, candidate.First_Name FROM WHERE LIMIT 10, 10

It's as if it doesn't pick up my FROM, from the last query - actually, the first query is quite different:

SELECT resume.Section_Value, resume.Candidate_ID, candidate.Location, candidate.Candidate_ID, candidate.Last_Name, candidate.First_Name FROM resume INNER JOIN candidate WHERE resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('-unix +solaris +html +sap +.net +c++ +dba +awk' IN BOOLEAN MODE) AND candidate.Candidate_ID = resume.Candidate_ID AND candidate.Location IN ('CA', 'California') LIMIT 0, 10

[AtroZ]

Where do you set the value of $sFrom?

Are you sure that when you reload the page your var gets its value again?

Could you post your code as an attachment please?

[kilbey1]

sure thing...

default case is a 'search' form; the one to look for is right up at the top, 'showcandidates'.

[AtroZ]

Mmhh, it seems that you are building your sql statement up from a $_POST variable that no longer exists.

/*Chek out this*/
if($_POST['skills'] != '')
				{
				  $aFrom[] = 'resume';
				} 
				if($_POST['firstname'] || $_POST['lastname']  || $_POST['city'] || $_POST['state'] != '')
				{
				  if($_POST['skills'] != '')
				  {
				  	$aFrom[] = 'INNER JOIN candidate'; 
				  }
				  else
				  {
				  	$aFrom[] = 'candidate';
				  }
				}
/*######*/

as you sure know, $_POST contins all the variables sent by the client using the POST method, in most cases an HTML form, but when you reload your page (using the links provided), those vars don't get sent again. You have to make sure those values don't dissapear, otherwise your script will not work as you wish.

One solution for this is using session variables.

You could assing the $_POST to a session array the first time you load the page and check out if that session var exists every time the page is loaded, something like

if(!isset($SESSION["somename"]) && isset($POST)) $SESSION["somename"] = $POST;

and use $SESSION instead of $POST in the building of your sql statement.

.....
.....

[tsinka]

Hi,

there is another way to get the total number of rows without having to execute a count(*) query (if you have MySQL >=4.0.0).

MySQL will have to check the complete result set but this is cheaper than doing a count query again.

Example:

<?PHP
$conn = mysql_connect("host","user","pass");
mysql_select_db("db",$conn);
$res = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM atable WHERE someconditions LIMIT begin,end");
$res2 = mysql_query("SELECT FOUND_ROWS()");

list($num) = mysql_fetch_row($res2);

echo $num."<br>\n";

while (list($id) = mysql_fetch_row($res)) {
  echo "$id<br>\n";
}
?>

Notice the SQL_CALC_FOUND_ROWS in the first query which is mandatory in this case so that FOUND_ROWS() returns valid data.

Thomas

[kilbey1]

tsinka,

I ran with your code and I'm returning correct results for 'next page' links. I'm pulling back the first 10 records, and there's one more to display - so I am correctly showing a page 2 link. When I hit page 2, I do go somewhere, but it displays nothing.

So do I still require sessions as in AtroZ's post? AtroZ, what you say does make sense, and notice my post IFs. I added what you suggested, but it didn't appear to do anything, so I'm probably missing something.

I'll attach the latest code. I've removed most of the forms to make it easier to edit the GUI parts of things.

[AtroZ]

O'k, in order to use session variables you have to start the session first using php's function

session_start();

and once you did this, and assigned the post variables to a session array, you have to use that session array INSTEAD of the post array in all your code, for example,

if($_POST['skills'])

has to be changed to

if($_SESSION['somename']['skills'])

for all the POST variables in the script.

Would be ok if you give a look to the Session Handling Functions section of the php manual.

[kilbey1]

I've been playing around with this and am not sure if I'm doing it correctly? I'm also not sure how much I should change; you mean change all references to $POSTs? And should it look like $SESSION['somename']['skills']?

See the latest code...

[AtroZ]

See the comments in the code...

[kilbey1]

Beautiful! I think I've got it. I'll be sure to give 'somename' something more descriptive. 🙂 Tested and works well. See attached code.

And many thanks!