Quick Pagination query (I hope!)

[Bazzaah]

Hi there

 

I adapted the pagination tutorial from here on PHP Freaks so that it can deal with a db query.

 

The pagination itself works just fine but I have a small issue, hopefully someone can help me please.

 

I have set up the query so that it won't allow blank searches. The script works perfectly well without the pagination.

 

With the pagination, the script now returns a few results as well as the 'you didn't enter a search-term' message - can someone help me tidy it up please? I'm guessing that I've put the search=$search term in too many places in the pagination script.

 

This is the query

 

if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
if (strlen($searchTerms) < 1) {
      $error[] = '<div class="messagebox">You didn\'t specify a search term</div></div>';
      $error[] = '<div class="small"><a href="search2.php"><p>Search Again?</p></a></div>';
   } else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }

$sql = "SELECT * FROM pro_words WHERE word LIKE '%{$searchTermDB}%' ORDER BY word LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$num_rows = mysql_num_rows($result);

//display results

if (mysql_num_rows($result) < 1) {
         $error[] = '<div class="messagebox">Sorry, your search term yielded no results.</div>';
         $error[] = '<div class="small"><a href="search.php"><p>Search Again?</p></a></div>';
      } else {
      echo '<div class="messagebox">Here are your search results.</div>';		

	while ($list = mysql_fetch_array($result)) {
   // echo data
echo '<div class="small"><a href="word.php?w=' . $list['word'] . '">' . $list['word'] . '</a></div>';  
  } //end while	

  			}	
	}	

 

This is the pagination

 

// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$totalpages'>>></a> ";
} 

 

Thanks in advance for any help.

[Buddski]

Can you post the code in its merged form. Im finding it difficult to see exactly where one script starts and one ends.

[Bazzaah]

Sure, here it is.

 

<?php

session_start();

if (isset($_SESSION['first_name']))
{	
echo '<div id="header">';  
include ('includes/header_loggedin.html');
echo '</div>';  
echo '<div id="centered">';

// database connection info
$conn = mysql_connect('localhost','vvv',mmmm') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('db',$conn) or trigger_error("SQL", E_USER_ERROR);

if(isset($_GET['search'])){
$search = $_GET['search'];
}

// find out how many rows are in the table 

if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
if (strlen($searchTerms) < 1) {
      $error[] = '<div class="messagebox">You didn\'t specify a search term</div></div>';
      $error[] = '<div class="small"><a href="search.php"><p>Search Again?</p></a></div>';
   } else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
  
$error=array();

$sql = "SELECT COUNT(*) FROM pro_words WHERE word LIKE '%{$searchTermDB}%'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];
}
// number of rows to show per page
$rowsperpage = 5;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
if (isset($_GET['search'])) {
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
if (strlen($searchTerms) < 1) {
      $error[] = '<div class="messagebox">You didn\'t specify a search term</div></div>';
      $error[] = '<div class="small"><a href="search2.php"><p>Search Again?</p></a></div>';
   } else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }

$sql = "SELECT * FROM pro_words WHERE word LIKE '%{$searchTermDB}%' ORDER BY word LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$num_rows = mysql_num_rows($result);

//display results

if (mysql_num_rows($result) < 1) {
         $error[] = '<div class="messagebox">Sorry, your search term yielded no results.</div>';
         $error[] = '<div class="small"><a href="search.php"><p>Search Again?</p></a></div>';
      } else {
      echo '<div class="messagebox">Here are your search results.</div>';		

	while ($list = mysql_fetch_array($result)) {
   // echo data
echo '<div class="small"><a href="word.php?w=' . $list['word'] . '">' . $list['word'] . '</a></div>';  
  } //end while	

  			}	
	}	

echo (count($error) > 0)?"<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; 

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
} else {
include ('includes/header.html');
echo '<div class="box">If you want to access this part of the site, please register or log-in.</div>';
  }
?>

[WebStyles]

you're missing a single quote on line 14 before mmmm.

[Bazzaah]

woops, yeah that's just a typo from where I removed my db connection details.

[Buddski]

There is so many things that need to be done to get this to work correctly.

Firstly, you are adding items to an array that doesnt exist yet and before you use the array you set it back to empty..

if (strlen($searchTerms) < 1) {
      $error[] = '<div class="messagebox">You didn\'t specify a search term</div></div>';
      $error[] = '<div class="small"><a href="search.php"><p>Search Again?</p></a></div>';
   } else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
  
$error=array();

[Bazzaah]

OK, thanks for the answer.

 

My intention there was to sanitise any query that's made as soon as it's made. I guess it would be easily changed.

 

Could you give me an idea of the errors so that I can attempt to correct them please?

 

[Bazzaah]

I changed the code around and it does pretty much what I want it to now. It's probably an ugly hack though.

 

<?php

session_start();

if (isset($_SESSION['first_name']))
{	
echo '<div id="header">';  
include ('includes/header_loggedin.html');
echo '</div>';  
echo '<div id="centered">';

// database connection info
$conn = mysql_connect('','','') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('',$conn) or trigger_error("SQL", E_USER_ERROR);

if(isset($_GET['search'])){
$search = $_GET['search'];
}

//sanitise the query
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms);
$searchTermDB = mysql_real_escape_string($searchTerms);

// find out how many rows are in the table 
$sql = "SELECT COUNT(word) FROM pro_words WHERE word LIKE '%{$searchTermDB}%'";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 5;

// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) ) {
   // cast var as int
   $currentpage = $_GET['currentpage'];
} else {
   // default page num
   $currentpage =  1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// resanitise the search (just in case - is it necessary?)
$searchTerms = trim($_GET['search']);
$searchTerms = strip_tags($searchTerms);
$searchTermDB = mysql_real_escape_string($searchTerms);

/******  make query and build the pagination links ******/
// range of num links to show
$range = 3;

//handle attempt at null search term

if (strlen($searchTerms) < 1) {
      echo '<div class="messagebox">You didn\'t specify a search term</div>';
      echo '<div class="small"><a href="search2.php"><p>Search Again?</p></a></div>';
   } else {   	
   //formulate the query
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
      $sql = "SELECT * FROM pro_words WHERE word LIKE '%{$searchTermDB}%' ORDER BY word LIMIT $offset, $rowsperpage";
      $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
   

if (mysql_num_rows($result) < 1) {

 echo '<div class="messagebox">Sorry, your search term yielded no results.</div>';
    echo '<div class="small"><a href="search2.php"><p>Search Again?</p></a></div>';

} else {
//display results	
	echo '<div class="messagebox">Here are your search results.</div>';		

	while ($list = mysql_fetch_array($result)) {
// echo data
echo '<div class="small"><a href="word.php?w=' . $list['word'] . '">' . $list['word'] . '</a></div>';  
  } 
// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " <b>$x</b> ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?search=$search&currentpage=$totalpages'>>></a> ";
}
} 
}// end if
/****** end build pagination links ******/
} else {
include ('includes/header.html');
echo '<div class="box">If you want to access this part of the site, please register or log-in.</div>';
  }
?>

 

So I solved it. Asking a question can be instructional.