 |  |  |  |  |  |  |  |
pagination and search
Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
Hi I have used a tutorial on full text search which was brilliant...I now want to add in pagination to it....Bu ti can;t seem to work it from the pagination tutorial...
This is the code for my full text search...
[php]
<html>
<head><title>Search</title></head>
<body>
<?php
// Database Connection
include 'db.php';
// Create the search function:
function searchForm()
{
// Re-usable form
// variable setup for the form.
$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words']))
: '');
$normal = (($_GET['mode'] == 'normal') ? ' selected="selected"' : '' );
$boolean = (($_GET['mode'] == 'boolean') ? ' selected="selected"' : '' );
echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
echo '<input type="hidden" name="cmd" value="search" />';
echo 'Search for: <input type="text" name="words" value="'.$searchwords.'" /> ';
echo 'Mode: ';
echo '<select name="mode">';
echo '<option value="normal"'.$normal.'>Normal</option>';
echo '<option value="boolean"'.$boolean.'>Boolean</option>';
echo '</select> ';
echo '<input type="submit" value="Search" />';
echo '</form>';
}
// Create the navigation switch
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : '');
switch($cmd)
{
default:
echo '<h1>Search Database!</h1>';
searchForm();
break;
case "search":
searchForm();
echo '<h3>Search Results:</h3><br />';
$searchstring = mysql_escape_string($_GET['words']);
switch($_GET['mode'])
{
case "normal":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring') AS score FROM user
WHERE MATCH(author,title, caption, full_body)
AGAINST ('$searchstring') ORDER BY score DESC";
break;
case "boolean":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM user
WHERE MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC";
break;
}
// echo $sql;
$result = mysql_query($sql) or die (mysql_error());
while($row = mysql_fetch_object($result))
{
echo '<strong>Title: '.stripslashes(htmlspecialchars($row->title)).'</strong><br />';
echo '<strong>Author: '.stripslashes(htmlspecialchars($row->author)).'</strong><br />
';
echo 'Score:'. number_format($row->score, 1).' Date: '.date('d/m/y', $row->dts).'<br />';
echo '<p>'.stripslashes(htmlspecialchars($row->caption)).'</p>';
echo '<hr size="1" />';
}
break;
}
?>
</body>
</html>
[php]
And below is some code a I was given to add in to give pagination....but I can't seemt o get it to work...please anyone out there that can help...I am getting desparate...and my eyes are now hirting!!!
PHP:
--------------------------------------------------------------------------------
$sql = "select SQL_CALC_FOUND_ROWS * from news where approved='1' order by story_date desc limit $offset,$results_per_page";
$result = mysql_query($sql);
$sql = "select FOUND_ROWS()";
$count_result = mysql_query($sql);
$count = mysql_fetch_array($count_result);
--------------------------------------------------------------------------------
This will give you two things, a subset of results starting at record # offset and returning $results_per_page results. It will also give you the total number of rows that would have been returned had you not limited the result set.
You can then use the result set in your pagination, and use the number of results that woudl have been returned to display your page numbers at the bottom of the page.
Here's how I would display page numbers:
PHP:
--------------------------------------------------------------------------------
$pages = 0;
while($count > 0){
echo "<a href='index.php?module=news&action=news&offset=".($pages*$results_per_page)."'>" . ($pages + 1) . "</a> ";
$count = $count - $rpp;
$pages++;
}
This is the code for my full text search...
[php]
<html>
<head><title>Search</title></head>
<body>
<?php
// Database Connection
include 'db.php';
// Create the search function:
function searchForm()
{
// Re-usable form
// variable setup for the form.
$searchwords = (isset($_GET['words']) ? htmlspecialchars(stripslashes($_REQUEST['words']))
: '');
$normal = (($_GET['mode'] == 'normal') ? ' selected="selected"' : '' );
$boolean = (($_GET['mode'] == 'boolean') ? ' selected="selected"' : '' );
echo '<form method="get" action="'.$_SERVER['PHP_SELF'].'">';
echo '<input type="hidden" name="cmd" value="search" />';
echo 'Search for: <input type="text" name="words" value="'.$searchwords.'" /> ';
echo 'Mode: ';
echo '<select name="mode">';
echo '<option value="normal"'.$normal.'>Normal</option>';
echo '<option value="boolean"'.$boolean.'>Boolean</option>';
echo '</select> ';
echo '<input type="submit" value="Search" />';
echo '</form>';
}
// Create the navigation switch
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : '');
switch($cmd)
{
default:
echo '<h1>Search Database!</h1>';
searchForm();
break;
case "search":
searchForm();
echo '<h3>Search Results:</h3><br />';
$searchstring = mysql_escape_string($_GET['words']);
switch($_GET['mode'])
{
case "normal":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring') AS score FROM user
WHERE MATCH(author,title, caption, full_body)
AGAINST ('$searchstring') ORDER BY score DESC";
break;
case "boolean":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM user
WHERE MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC";
break;
}
// echo $sql;
$result = mysql_query($sql) or die (mysql_error());
while($row = mysql_fetch_object($result))
{
echo '<strong>Title: '.stripslashes(htmlspecialchars($row->title)).'</strong><br />';
echo '<strong>Author: '.stripslashes(htmlspecialchars($row->author)).'</strong><br />
';
echo 'Score:'. number_format($row->score, 1).' Date: '.date('d/m/y', $row->dts).'<br />';
echo '<p>'.stripslashes(htmlspecialchars($row->caption)).'</p>';
echo '<hr size="1" />';
}
break;
}
?>
</body>
</html>
[php]
And below is some code a I was given to add in to give pagination....but I can't seemt o get it to work...please anyone out there that can help...I am getting desparate...and my eyes are now hirting!!!
PHP:
--------------------------------------------------------------------------------
$sql = "select SQL_CALC_FOUND_ROWS * from news where approved='1' order by story_date desc limit $offset,$results_per_page";
$result = mysql_query($sql);
$sql = "select FOUND_ROWS()";
$count_result = mysql_query($sql);
$count = mysql_fetch_array($count_result);
--------------------------------------------------------------------------------
This will give you two things, a subset of results starting at record # offset and returning $results_per_page results. It will also give you the total number of rows that would have been returned had you not limited the result set.
You can then use the result set in your pagination, and use the number of results that woudl have been returned to display your page numbers at the bottom of the page.
Here's how I would display page numbers:
PHP:
--------------------------------------------------------------------------------
$pages = 0;
while($count > 0){
echo "<a href='index.php?module=news&action=news&offset=".($pages*$results_per_page)."'>" . ($pages + 1) . "</a> ";
$count = $count - $rpp;
$pages++;
}
•
•
Join Date: Jan 2005
Posts: 294
Reputation: 
Solved Threads: 6
Posting Whiz in Training
Ecommerce-Web-Store.com Building Your e-Business.
Hi,
Many thanks but I have worked through a Pagination tutorial...but what i am looking for is adding it to a search option...not just a show all data in a table search...
:lol:
Many thanks but I have worked through a Pagination tutorial...but what i am looking for is adding it to a search option...not just a show all data in a table search...
:lol:
•
•
•
•
Originally Posted by wood1e
Hi,
Many thanks but I have worked through a Pagination tutorial...but what i am looking for is adding it to a search option...not just a show all data in a table search...
:lol:
I know it is not meant to be to much difference....but i am meant to change the two SQL parts below?
[SQL/PHP]
$searchstring = mysql_escape_string($_GET['words']);
switch($_GET['mode'])
{
case "normal":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring') AS score FROM user
WHERE MATCH(author,title, caption, full_body)
AGAINST ('$searchstring') ORDER BY score DESC";
break;
case "boolean":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM user
WHERE MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC";
break;
}
[SQL/PHP]
Or am I meant o change just one...as everytime I try a combination of changes with the code the search just stops working!!!!????!!!!
[SQL/PHP]
$searchstring = mysql_escape_string($_GET['words']);
switch($_GET['mode'])
{
case "normal":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring') AS score FROM user
WHERE MATCH(author,title, caption, full_body)
AGAINST ('$searchstring') ORDER BY score DESC";
break;
case "boolean":
$sql = "SELECT id, author, title, caption, dts,
MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) AS score FROM user
WHERE MATCH(author, title, caption, full_body)
AGAINST ('$searchstring' IN BOOLEAN MODE) ORDER BY score DESC";
break;
}
[SQL/PHP]
Or am I meant o change just one...as everytime I try a combination of changes with the code the search just stops working!!!!????!!!!
I will not do your programming for you, but I will try to give you enough help so you can figure it out.
Try this, build a simple page that does the following:
Now, suppose your SQL statement was:
SELECT * from widgets ORDER BY widget_id
(This needs to be a table with more than 10 records.)
Change it to:
SELECT * from widgets ORDER BY widget_id LIMIT 0,5
When you run the code again, notice that you only see rows 1 - 5? (The row numbering actually starts at zero, so technically, the 5 rows you see are 0 through 4.
Now change the SQL statement to:
SELECT * from widgets ORDER BY widget_id LIMIT 5,5
Notice that you now see rows 5,6,7,8, and 9? CONGRATULATIONS! You are doing pagination!
The first numer after LIMIT is the record number to start on. The second number is the number of rows to return. So in your code, set a $rows_per_page variable equal to however many rows you want to display per page. As the user clicks to go back or forward a page, keep track of the page number. Your SQL statement would then be constructed:
[php]
$start_row = $rows_per_page * $page_num - $rows_per_page;
$sql = "SELECT * from widgets ORDER BY widget_id LIMIT $start_row,$rows_per_page";
[/php]
So just add the LIMIT part to your SQL statement. You'll also have to keep track of the page number ($page_num) in an HTML Form variable.
This is the guts of it. Really the hard part is making your interface work smooth and intuitive without possibility of error. But that discussion is beyond a forum post!
Try this, build a simple page that does the following:
- Connect to mysql database
- Select all records from a table in a specific order
- Output the records to the browser so you can see them in order.
Now, suppose your SQL statement was:
SELECT * from widgets ORDER BY widget_id
(This needs to be a table with more than 10 records.)
Change it to:
SELECT * from widgets ORDER BY widget_id LIMIT 0,5
When you run the code again, notice that you only see rows 1 - 5? (The row numbering actually starts at zero, so technically, the 5 rows you see are 0 through 4.
Now change the SQL statement to:
SELECT * from widgets ORDER BY widget_id LIMIT 5,5
Notice that you now see rows 5,6,7,8, and 9? CONGRATULATIONS! You are doing pagination!
The first numer after LIMIT is the record number to start on. The second number is the number of rows to return. So in your code, set a $rows_per_page variable equal to however many rows you want to display per page. As the user clicks to go back or forward a page, keep track of the page number. Your SQL statement would then be constructed:
[php]
$start_row = $rows_per_page * $page_num - $rows_per_page;
$sql = "SELECT * from widgets ORDER BY widget_id LIMIT $start_row,$rows_per_page";
[/php]
So just add the LIMIT part to your SQL statement. You'll also have to keep track of the page number ($page_num) in an HTML Form variable.
This is the guts of it. Really the hard part is making your interface work smooth and intuitive without possibility of error. But that discussion is beyond a forum post!
I solve a bit different problem. I want to add a more complex search engine to my website with more search criteria. There is no problem until I start to consider pagination of search results. Is there any best-practice how to preserve complex query for repeated querying the database (using SQL "LIMIT" clause to do the pagination)? Up to now I considered two ways - to encode query into URL (results in long and untidy URLs) or to store the query in session. Can anybody tell me which of these ways is better of if another way exists?
Thanks
Thanks
•
•
Join Date: Jul 2004
Posts: 234
Reputation:
Solved Threads: 8
Posting Whiz in Training
There are good tutorials on phpfreaks. You may want to try them http://www.phpfreaks.com/tutorial_ca...Pagination.php
You can put the query in a hidden TEXTAREA to pass it between pages.
PHP Syntax (Toggle Plain Text)
|
Similar Threads
- search and pagination (PHP)
- creating a forum (Site Layout and Usability)
- PHP Search pagination problem (PHP)
Other Threads in the PHP Forum
- Previous Thread: help with sessions
- Next Thread: fwrite every other time
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest PHP Posts
- Today's Posts
- Unanswered Threads
apache api array beginner binary body broken cakephp checkbox class cms code computing cron curl database date date/time delete display dynamic echo email error file files filter folder form forms function functions gc_maxlifetime global google host href htaccess html image include insert ip javascript joomla limit link list login mail memmory memory menu mlm msqli_multi_query multiple mycodeisbad mysql navigation oop parameter parsing paypal pdf php problem query radio random recourse recursion regex remote script search seo server sessions sms snippet source space sql static syntax system table thesishelp tutorial update upload url validator variable video web webdesign wordpress xml youtube
