full-text boolean search

mssmanian

Hi ,
I have just started working on full-text boolean search.I have coded the program but I get an error :

You have an error in your SQL syntax near 'boolean mode)' at line 1

The fields in mysql table 'pubs' are :id,author,title,year.

Here's my code:

<html>
<head><title>Search</title></head>
<body>

<?php
$cnx = mysql_connect('localhost', 'user', 'password') or die ("Could not connect");
mysql_select_db('db', $cnx) or die (mysql_error());

function searchForm()
{

$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,year,MATCH(author,title) AGAINST ('$searchstring') AS score FROM pubs WHERE MATCH(title) AGAINST ('$searchstring')";
break;

case "boolean":
$sql = "SELECT * FROM pubs WHERE MATCH(title) AGAINST('$searchstring' IN BOOLEAN MODE) AS score FROM pubs WHERE MATCH(title) AGAINST ('$searchstring' IN BOOLEAN MODE)";
break;
}

$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 '<hr size="1" />';
}
break;
}
?>

</body>
</html>

I am not able to figure the problem.Is it with the coding?I have also created a full text index:

CREATE FULLTEXT INDEX full_index ON pubs(author,title,year);

Please help me out.

Roger_Ramjet

You got 2 where clauses.

mssmanian

Thanks for replying...Sorry for posting two threads.I wanted to know if newbies can contain forums with problem in database.

I have managed to get the search results.But,I am not able to print a particular field.I want to know the reason.My commands are as follows:

CREATE TABLE mytable(mytable_id int (25) NOT NULL auto_increment,mytable_title varchar(100),mytable_dts varchar(25),mytable_caption tinytext NOT NULL,mytable_full_body text NOT NULL,PRIMARY KEY(mytable_id));

CREATE FULLTEXT INDEX full_index ON mytable(mytable_title,mytable_caption,mytable_full_body);

My code is as follows🙁Sorry for the long post)

<html>
<head><title>Search</title></head>
<body>

<?php

$cnx = mysql_connect('localhost', 'user', 'password') or die ("Could not connect");
mysql_select_db('dbname', $cnx) or die (mysql_error());

function searchForm()
{

// 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 mytable_id, mytable_title, mytable_caption, mytable_dts, 
           MATCH(mytable_title, mytable_caption, mytable_full_body) 
           AGAINST ('$searchstring') AS score FROM mytable 
           WHERE MATCH(mytable_title, mytable_caption, mytable_full_body) 
           AGAINST ('$searchstring') ORDER BY score DESC";
  break;

  case "boolean":
    $sql = "SELECT mytable_id, mytable_title, mytable_caption, mytable_dts, 
           MATCH(mytable_title, mytable_caption, mytable_full_body) 
           AGAINST ('$searchstring IN BOOLEAN MODE') AS score FROM mytable 
           WHERE MATCH(mytable_title, mytable_caption, mytable_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>Year: '.stripslashes(htmlspecialchars($row->mytable_title)).'</strong><br />';
  #echo 'Score:'. number_format($row->score, 1).' Title: '.date('m/d/y', $row->mytable_full_body).'<br />';
  echo '<strong>Author:<p>'.stripslashes(htmlspecialchars($row->mytable_caption)).'</p>';
  echo '<strong>Title:<p>'.stripslashes(htmlspecialchars($row->mytable_full_body)).'</p>';
  echo '<hr size="1" />';
}

break;
}
mysql_close($cnx);
?>

</body>
</html>

I am not able to understand why I am not able to print mytable_full_body.Please help me out.

Roger_Ramjet

Cos you don't select it in either query:

case "normal":
$sql = "SELECT mytable_id, mytable_title, mytable_caption, mytable_dts,

case "boolean":
$sql = "SELECT mytable_id, mytable_title, mytable_caption, mytable_dts,

mssmanian

Hi!
Thank you for pointing out my mistake.But,it doesn't seem to work properly.Suppose I search for a title which goes like:
Analytical feedback microwave computer aided engineering.
When I enter analytical/feedback,it works fine.But,when I enter microwave it doesn't seem to give any search results.But the title is one and the same.I don't understand why this happens.Can you please help me out.
Also I find out,that there are no ft_min_word_len and ft_max_word_len in mysql variables.Does that make any difference?Please guide me.

mssmanian

Hi!
Can someone please tell me how to get rid of this problem?I have been stuck and I am not able to proceed.The search engine works for linited words in a single sentence.I have entered 4 records in my database.
For example:

Analytical study of an Error Envelope Feedback Linearizer accepted for publication on International Journal of RF and Microwave Computer-Aided Engineering

If I enter analytical/error/envelope it works.But if I enter microwave/engineering,it isn't working...
Please help me out...

autocolismo

Case Sensitive

Microwave
Engineering

Roger_Ramjet

Sorry I did not reply before, holiday season and all that 🆒

FTS is NOT case sensitive: look at the examples in the manual to see this 😃

The reason your query does not work for all words is also in the manual:

The search result is empty because the word “MySQL” is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large datasets, this is the most desirable behavior — a natural language query should not return every second row from a 1GB table. For small datasets, it may be less desirable.

A word that matches half of the rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that rows containing the word are assigned a low semantic value for the particular dataset in which they occur. A given word may exceed the 50% threshold in one dataset but not another.

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode; see Section 12.7.1, “Boolean Full-Text Searches”.

As it says, when you tets with only a few records, the chnaces of a particular word being in too many rows is high - especially if they are on the same subject area. Just add more records with more variety, it's only test data so make up stuff.