Jump to content

Quick Pagination query (I hope!)

Bazzaah

By Bazzaah
in PHP Coding Help

 Share

Recommended Posts

Bazzaah Member

Bazzaah

Posted
Posted

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.

Link to comment
Share on other sites
Bazzaah Member

Bazzaah

Posted
  • Author
Posted

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>';
  }
?>

Link to comment
Share on other sites
Buddski Newbie

Buddski

Posted
Posted

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

Link to comment
Share on other sites
Bazzaah Member

Bazzaah

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.