Page 1 of 1

Full-Text Searches & Pagination

Posted: Sun Nov 06, 2005 6:41 pm
by BZorch
feyd | Please use

Code: Select all

and

Code: 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 thumbnails
This is the escape data function:

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

and

Code: 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]

Posted: Sun Nov 06, 2005 9:12 pm
by yum-jelly
Where are you assigning your MySQL search operators (+,-,~,*,...). You can't place one them in a URL value because the server will convert any * + * it finds to a * space *. encode the value before attaching it to your pagination URL(S)


use..... in all your pagination URL(S)

Code: Select all

&keyword=' . ( isset ( $_GET['keyword'] ) ? rawurlencode ( $_GET['keyword'] ) : '' ) . '
Also you need to be doing some validation and just shut off magic_quotes instead of doing all them silly stripslashes() every where!

yj

Posted: Sun Nov 06, 2005 10:17 pm
by BZorch
Thanks. I will try that. I do plan to do validation (i.e. I assume you are talking about eregi & using regular expression), and I am glad you mentioned the magic quotes. I was using some of the script dogmatically based on the tutorials from which I have been teaching myself PHP. Obviously, altering examples to my needs. Is it just the stripslashes? Do I still need to use the mysql_real_escape_string if I am only searching and the permissions do not allow anyone to alter tables? I figured it is good practice to use it anyway.

So much to learn. Thanks again for the help.

Also, I noticed the comment (it looked like it was from a moderator of the forum) about how I inserted my scripts. I used the [syntax=php][/syntax] when inserting. It looked correct. Can someone tell me what I could have done wrong? I read the FAQ about posting and I thought I got it right. Using [syntax=php][/syntax] does not doing anything in the preview.

I apologize for my ignorance if it wrong. I will reread the FAQ.

Posted: Sun Nov 06, 2005 10:36 pm
by feyd
BZorch wrote:Using does not doing anything in the preview.
It works in the preview...