pagination and search

[wood1e]

<?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 '';
echo '';
echo 'Search for: ';
echo 'Mode: ';
echo '';
echo 'Normal';
echo 'Boolean';
echo ' ';
echo '';
echo '';
}

// Create the navigation switch
$cmd = (isset($_GET['cmd']) ? $_GET['cmd'] : '');

switch($cmd)
{
default:
echo 'Search Database!';
searchForm();

break;

case "search":
searchForm();
echo 'Search Results:
';

$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 'Title: '.stripslashes(htmlspecialchars($row->title)).'
';
echo 'Author: '.stripslashes(htmlspecialchars($row->author)).'

';
echo 'Score:'. number_format($row->score, 1).' Date: '.date('d/m/y', $row->dts).'
';
echo ''.stripslashes(htmlspecialchars($row->caption)).'

';
echo '';
}
break;
}

?>

[zippee]

See http://www.php-mysql-tutorial.com/php-mysql-paging.php

[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:

[Troy]

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:

There is not a difference between the two. Paginating all data (select *) is the same as paginating a subset (select name, age, make, model from widgets where foo = 'bar'). All that is different is the SQL statement. Your search is just a sql statement that returns a recordset.

[wood1e]

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!!!!????!!!!

[Troy]

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: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! :cool:

[JohnyQ]

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

[PoA]

There are good tutorials on phpfreaks. You may want to try them http://www.phpfreaks.com/tutorial_cat/25/Page-Number--Pagination.php

[JohnyQ]

Thanks, I have already read that. Interesting, but not covering my problem. Any other idea?

[Troy]

You can put the query in a hidden TEXTAREA to pass it between pages.

<textarea name="sql"><?= $sql ?></textarea>

[bgharper]

Thanks so much for the "hints" on paging search results!

[TerryAlly]

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...

The basic script for pagnination is below but it needs some tweaking.

Because you are using a search term the section below to "Determine how many records there are" has to be placed AFTER the QUERY way below.

Secondly, you have to change the URL for the "previous", "next", and "page" links to include the start position and the search term.

My example is:
echo 'Previous ';

I hard coded in the following:
'&cmd=search&words='.$searchstring.'&mode='.$_GET['mode'].'

Unfortunately, I cannot show you my page because it is password protected because of the contents.

Rgds/
Terry

---------- SCRIPT ---------------
// Number of records to show per page:
$display = 10;

// Determine how many records there are.
if (isset($_GET['np'])) {
$num_pages = $_GET['np'];
} else {
$query = "SELECT CONCAT(last_name, ', ', first_name) AS name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr FROM users ORDER BY registration_date ASC";
$query_result = mysql_query ($query) or die (mysql_error());
$num_records = @mysql_num_rows ($query_result);

if ($num_records > $display) {
$num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
}

// Determine where in the database to start returning results.
if (isset($_GET['s'])) {
$start = $_GET['s'];
} else {
$start = 0;
}

// Make the query.
$query = "SELECT CONCAT(last_name, ', ', first_name) AS name, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr FROM users ORDER BY registration_date ASC LIMIT $start, $display";
$result = @mysql_query ($query); // Run the query.
$num = mysql_num_rows ($result); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

echo "Registered Users";

// Make the links to other pages, if necessary.
if ($num_pages > 1) {

echo '';
// Determine what page the script is on.
$current_page = ($start/$display) + 1;

// If it's not the first page, make a Previous button.
if ($current_page != 1) {
echo 'Previous ';
}

// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '' . $i . ' ';
} else {
echo $i . ' ';
}
}

// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
echo 'Next ';
}

echo '

';

}
echo 'NameDate Registered
';

// Fetch and print all the records.
$bg = '#eeeeee';
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
echo '', stripslashes($row[0]), '', $row[1], '
';
}

echo '';

mysql_free_result ($result); // Free up the resources.

} else { // If it did not run OK.
echo 'There are currently no registered users.

';
}

mysql_close(); // Close the database connection.
?>

[DrClue]

So what one wants to do is search for a particular record/row in the
database table and calculate an initial offset value to
feed into the typical pagination routine,

While there may be better ways to do this , what I cobbled
together was this little bit of MySql syntax that seems to work just fine
on my little 40,000 row table.

set @row=-1;select foo.Row,foo.company_name from (select @row:= @row+1 AS Row ,company_name from TheCompanyDatabaseTable ) AS foo where foo.company_name='whatever';

Given the fact I searched in vein for this answer , I figured I'd
post the answer I hacked up.

Cheers