Hi i have a table called 'BookPage' with column: 'PageText'
i have set up a search on the column 'PageText' (see code below). now when the user does a search for a keyword, all the result pages are displayed one below the other.
Is there a way if i could display each pagetext on its individual pages by clicking some sort of 'Next' button link they move to the next page.
form.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search</title>
<link href="default.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
<?php
// include MySQL-processing classes
require_once 'mysql.php';
try{
// connect to MySQL
$db=new MySQL(array('host'=>'','user'=>'','password'=>'','database'=>''));
$searchterm=$db->escapeString($_GET['searchterm']);
$result=$db->query("SELECT * FROM BookPage WHERE (PageText) like \"%$searchterm%\" ");
if(!$result->countRows()){
echo '<div class="maincontainer"><h2>No results were found. Go back and try a new search.</h2></div>'."n";
}
else{
// display search results
echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."n";
while($row=$result->fetchRow()){
echo '<div class="rowcontainer"><p><strong>Book Id:
</strong>'.$row['BookId'].'<p><p><strong>Page Id:
</strong>'.$row['PageId'].'</p><p><strong>Page Text:
</strong>'.$row['PageText'].'</p></div>'."n";
}
}
echo '</div>';
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
</body>
</html>
Hi please see modified code below not working could you let me know where i am gone wrong.
modified code
<?php
// include MySQL-processing classes
require_once 'mysql.php';
try{
// connect to MySQL
$db=new MySQL(array('host'=>'',''=>'','password'=>'','database'=>''));
//page
if (isset($_GET['page'])) {
$page_no = $_GET['page'];
$next_pg = $page_no + 1;
} else {
$page_no = 0;
$next_pg = 1;
}
$searchterm=$db->escapeString($_GET['searchterm']);
$result=$db->query("SELECT * FROM BookPage WHERE (PageText) like \"%$searchterm%\" ORDER BY BookId ASC LIMIT 0, $page_no");
if(!$result->countRows()){
echo '<div class="maincontainer"><h2>No results were found. Go back and try a new search.</h2></div>'."n";
}
else{
// display search results
echo '<div class="maincontainer"><h2>Your search criteria returned '.$result->countRows().' results.</h2>'."n";
while($row=$result->fetchRow()){
$searchvalue = implode('<span style="color:green;font-weight:800;background-color:yellow;">'.$_GET['searchterm'].'</span>',explode($_GET['searchterm'],$row['PageText']));
echo '<div class="rowcontainer"><p><strong>Book Id:
</strong>'.$row['BookId'].'<p><p><strong>Page Id:
</strong>'.$row['PageId'].'</p><p><strong>Page Text:
</strong>'.$searchvalue.'</p></div>'."n";
}
}
echo '</div>';
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
You can try changing your query to something like:
if (isset($_GET['page'])) {
$page_no = $_GET['page'];
$next_pg = $page_no + 1;
} else {
$page_no = 0;
$next_pg = 1;
}
$result=$db->query("SELECT * FROM BookPage WHERE (PageText) like \"%$searchterm%\" ORDER BY id ASC LIMIT 1, $page_no");
Then insert a link like:
<a href="thispage.php?page=<?php echo $next_pg; ?>">Next page</a>
What this will do is get one result at a time and offset the result depending on whichever page you are on. The only important thing to do when doing this is to order the results by something so each query puts the results in the same order. For example, order by ID ASC (per my example).
You use the LIMIT clause in MySQL. Keep track of which result row you're on in PHP and pass the variable through your link to use in the next query.
From the MySQL docs (http://dev.mysql.com/doc/refman/5.1/en/select.html):
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)