Full-Text Searches & Pagination
Posted: Sun Nov 06, 2005 6:41 pm
feyd | Please use
This is the escape data function:
feyd | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
I probably should ask this as two separate topics, but they may be related so here I go.
First, I have created a mysql database with photo ID, photo name, photo location, and Categories (keywords). I indexed the Category field only. The format of the database is MyISAM.
My keywords are stored using commas to separate them like the following:
Example #1: brick, column, precast
Example #2: light rail, mass transportation
Example #3: handrail, steel, powdercoated
When I search using one word (i.e. bench, gate, etc) I the results come up and the pagination works as expected. When I use the Boolean functions (i.e. +brick +column), my first page has perfect results and then the following paginated results begin to be a mix of both words (i.e. some having brick and others column but sometimes not both). Then if that was not annoying enough, when I click on the second page or any other page and then click back to page 1. The results are not the same. It reverts to the mixed results.
So my questions are:
1. How can I get better results?
2. Why is the first page results changing?
Writing this and through some testing I have determined that my problem is probably how I am passing the keyword variable through the paginated results. I am seeing that if I search +column +brick that my links $get variable in the page link changes it to column brick. Without the "+" I would get results all over the board. The escape_date function is below the script which is included in my mysql connection script. I am not sure if I a stripping the "+" off through the escaping of the data.
Any insight is appreciated:
My pagination scripted is as follows:Code: Select all
<?php
//Set the page title and include header
require_once ('../example/error_reporting.inc');
$page_title='Results!';
include ('../example/header.html');
$kwd=stripslashes (trim($_GET['keyword']));
if ($kwd){
require_once ('../example/mysql_connect.php'); // Connect to the db.
// Number of records to show per page:
$display = 12;
$kwd=escape_data ($kwd);
// Determine how many pages there are.
if (isset($_GET['np'])) { // Already been determined.
$num_pages = $_GET['np'];
} else { // Need to determine.
$query = "SELECT File_Name, ID FROM photo_db WHERE MATCH (Categories) AGAINST ('$kwd' IN BOOLEAN MODE)
ORDER BY MATCH (Categories) AGAINST ('$kwd' IN BOOLEAN MODE)"; // Standard query.
$query_result = mysql_query ($query);
$num_records = @mysql_num_rows ($query_result);
if ($num_records > $display) { // More than 1 page.
$num_pages = ceil ($num_records/$display);
} else {
$num_pages = 1;
}
}
// Determine where in the database to start returning results.
if (isset($_GET['s'])) { // Already been determined.
$start = $_GET['s'];
} else {
$start = 0;
}
//search for keyword
$query= "SELECT File_Name, ID FROM photo_db WHERE MATCH (Categories) AGAINST ('$kwd' IN BOOLEAN MODE)
ORDER BY MATCH (Categories) AGAINST ('$kwd' IN BOOLEAN MODE) LIMIT $start, $display";
$result = @mysql_query ($query); // Run the query.
$num = mysql_num_rows ($result); // How many photos are there?
if ($num > 0) { // If it ran OK, display the records.
echo '<div id="results_wrapper">';
// Make the links to other pages, if necessary.
if ($num_pages > 1) {
echo '<p>';
// 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 '<a href="results.php?s=' . ($start - $display) . '&np=' . $num_pages . '&keyword='.$_GET['keyword']. '">Previous</a> ';
}
// Make all the numbered pages.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $current_page) {
echo '<a href="results.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages .'&keyword='.$_GET['keyword']. '">' . $i . '</a> ';
} else {
echo $i . ' ';
}
}
// If it's not the last page, make a Next button.
if ($current_page != $num_pages) {
echo '<a href="results.php?s=' . ($start + $display) . '&np=' . $num_pages .'&keyword='.$_GET['keyword']. '">Next</a>';
}
echo '</p><br />';
} // End of links section.
//The rest of the script displays thumbnailsCode: Select all
function escape_data ($data) {
global $dbc;
if (ini_get('magic_quotes_gpc')) {
$data = stripslashes($data);
}
return mysql_real_escape_string (trim ($data), $dbc);feyd | Please use
Code: Select all
andCode: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]