need to get pagination working again

kilbey1

I just need to get pagination working again in my code, after having made some changes. Pagination works for the first page, but for no pages after. Any ideas?

Here's a section of the code; first portion and final portion are directly pagination related.

		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); 

	if($_SESSION['state'] != '') {
		$sql = "SELECT State, fullstatename FROM states WHERE State='" . $_SESSION['state'] . "'";
		$result = mysql_query($sql) or die(mysql_error()); 
		while($row = mysql_fetch_array($result)) {
			$state = $row['State'];
			$fullstatename = $row['fullstatename'];
		}//end while
	}//end if
		//set up SELECT statement
			$aSelect = array(); 
			if($_SESSION['skills'] != '') {
			  $aSelect[] = 'resume.Section_Value'; 
			  $aSelect[] = 'resume.Candidate_ID';
			}
			if($_SESSION['firstname'] != '') {
			  $aSelect[] = 'candidate.First_Name'; 
			}
			if($_SESSION['lastname'] != '') {
			  $aSelect[] = 'candidate.Last_Name';
			} 
			if($_SESSION['city']  != '' || $_SESSION['state'] != '') {
			  $aSelect[] = 'candidate.Location';		
			}
			$aSelect[] = 'candidate.Candidate_ID';
			$aSelect[] = 'candidate.Last_Name';
			$aSelect[] = 'candidate.First_Name';

			//set up FROM portion of sql statement
			$aFrom = array(); 
			if($_SESSION['skills'] != '') {
			  $aFrom[] = 'resume';
			} 
			  if($_SESSION['firstname']  != '' || $_SESSION['lastname']  != '' || $_SESSION['city']  != '' || $_SESSION['state'] != '' || $_SESSION['state'] == '') {
			  	if($_SESSION['skills'] != '') {
					$aFrom[] = 'INNER JOIN candidate'; 
			  	} else {
			  		$aFrom[] = 'candidate';
			  	}
			}

			//set up WHERE portion of sql statement
			$aWhere = array(); 
			if($_SESSION['skills'] != '') {
					$chars_temp = explode(" ", $_SESSION['skills']);
					//print_r($chars_temp);
					//print_r($chars);
					$chars = array();
					foreach($chars_temp as $key => $value) {
						//only include if array is not empty
						//(ie. catches empty spaces in text field entry)					
						if (!empty($value)) {
							$chars[] = strtolower($value);
						}
					}
					$chars = implode(" ",$chars);
			//pull keywords from Section_ID 1
			  $aWhere[] = "resume.Section_ID = '1'";
			  //match Section_Value against keywords
			  $aWhere[] = "MATCH (resume.Section_Value) AGAINST ('" . $chars . "' IN BOOLEAN MODE)";  
			  //match Candidate_ID between tables 'candidate' and 'resume'
			  $aWhere[] = 'candidate.Candidate_ID = resume.Candidate_ID';
			}
			if($_SESSION['firstname'] != '') {
			  $aWhere[] = "candidate.First_Name LIKE '" . $_SESSION['skills'] . "%'"; 
			}
			if($_SESSION['lastname'] != '') {
			  $aWhere[] = "candidate.Last_Name LIKE '" . $_SESSION['skills'] . "%'"; 
			}
			if($_SESSION['city']  != '' && $_SESSION['state'] != '') {
				$aWhere[] = "candidate.Location LIKE '%" . $_SESSION['city'] . "%' AND (candidate.Location = '".$state."' OR candidate.Location = '".$fullstatename."')";
			}
			else {
				if($_SESSION['city'] != '') {
				  $aWhere[] = "candidate.Location LIKE  '%" . $_SESSION['city'] . "%'";
				}
				if($_SESSION['state'] != '') {
					$aWhere[] = "candidate.Location IN ('".$state."', '".$fullstatename."')";
				}
			}

			//build SELECT clause
			$sSelect = 'SELECT ' . implode(', ', $aSelect);

			// build FROM clause
			if($_SESSION['skills'] != '') {
				//eliminate imploding on , to give proper structure with INNER JOIN in statement
				$sFrom = 'FROM ' . implode(' ', $aFrom);
			} else {
				$sFrom = 'FROM ' . implode(', ', $aFrom);				
			}						

			//build where clause
			$sWhere = 'WHERE ' . implode(' AND ', $aWhere);

			$testsql = "SELECT SQL_CALC_FOUND_ROWS * ".$sFrom." ".$sWhere." LIMIT ".$from." , ".$max_results."";
			echo $testsql."<BR>";								

			// Perform MySQL query on only the current page number's results
			// Select, Where and From clause done - add it to the SQL skeleton
			$sql = mysql_query("SELECT SQL_CALC_FOUND_ROWS * ".$sFrom." ".$sWhere." LIMIT ".$from." , ".$max_results."");
			//$sql = "SELECT SQL_CALC_FOUND_ROWS * " . $sFrom . " " . $sWhere . " LIMIT " . $from . " , " . $max_results . "";
			//$sql = mysql_query($sql);
			$sql2 = mysql_query("SELECT FOUND_ROWS()");

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

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

			//echo $sql . "<P>";

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

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

			} else {

tsinka

Hi,

please post the code that prints the pagination links.

Thomas

kilbey1

That would be...

	// 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']."?goto=showcandidates&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']."?goto=showcandidates&page=$i\">$i</a>&nbsp;";
	}
}

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

tsinka

In which way doesn't it work ?

One problem could be be the << characters in <<Previous (and Next>>).

Replace <<Previous by &lt;&lt;Previous and Next>> by Next&gt;&gt;

Thomas

kilbey1

Ok, I have zipped up a Word document, pagination_errors.zip, with document pagination_errors.doc inside it.

Here's the issue:

From my search form, I enter something like '-unix +html' in a 'skills' field. In the resulting Page 1, you will see my session for skills exists: -unix +html (I've echoed it out before the SQL). Immediately following, you will see I've echoed out the SQL statement. It returns:

-unix +htmlSELECT SQL_CALC_FOUND_ROWS * FROM resume INNER JOIN candidate WHERE resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('-unix +html' IN BOOLEAN MODE) AND candidate.Candidate_ID = resume.Candidate_ID AND candidate.Location IN ('CA', 'California') LIMIT 0 , 10

When I hit page 2, it returns...

SELECT SQL_CALC_FOUND_ROWS * FROM candidate WHERE LIMIT 10 , 10

Therefore, nothing gets passed to the second (or other) pages.

ropic

I'm preffer to use adodb, i recommend u a lot.

http://php.weblogs.com/ADODB

tsinka

Seems to be a session problem. Please post the complete script (as attachment). Maybe a missing session_start() or something like that. Additionally, post the PHP session settings.

Thomas

kilbey1

Here's the complete code. Forms are 'included' outside for easier maintenance.

tsinka

In line 80:

Replace:
if(!isset($SESSION['somename']) && isset($POST)) {

with

if(!empty($_POST)) {

EDIT: This is the short answer ... rather check the different POST variables individually ...

Problem:

where does $_SESSION['somename'] come from ? (could it be that it comes from a piece of code I posted in another thread ... I know it from somewhere). But that is not the (main) problem
isset($POST): The $POST array exists always. But it is empty if nothing has been posted. I think the if statement is executed every time you request the page. And that leads to problem number
3.
if (preg_match('/[a-z+-"]*/', $_POST['skills'])) {

[a-z+-"] also means no character at all (because of the ). Rather use + (at least one). In this case, the session variable skills is overwritten with an empty string as soon as you click on one of the pagination links.

I hope I'm not completely wrong ...

Thomas

kilbey1

By jove, that WORKS! Yes, session['somename'] actually no longer exists after I made a bunch of changes. You may be right that you helped me out on this earlier.

Thanks for the tip on the preg. I wish I knew reg. expressions much better.