...

View Full Version : Full Text Search - What have I done wrong?



Cacus
08-09-2006, 12:00 PM
Hi all

I have tried to do a full-text search with the following code that doesn't work. I know the $keywords string is getting the input from the form as I've echo'd it back and if you remove MATCH(caption) AGAINST('%keywords') then the query will run off a list of all the links. So what am I missing or am I way off base?


<p>
Full text search <br />
<form action="index.php" method="post">
Keywords:<br />
<input type="text" name="keywords" size="20" maxlength="40" value="" /><br />
<input type="submit" value="Search!" />
</form>
</p>

<?php

// If the form has been submitted with supplied keywords
if (isset($_POST['keywords'])) {
$keywords = ($_POST['keywords']);


$query = ("SELECT * FROM photographs MATCH(caption) AGAINST('%keywords') ORDER BY city_town, pic_title DESC");
$result = mysql_query($query);
$row = mysql_num_rows($result);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<p><a href="photographs.php?id=<?php echo $row['id']; ?>"><?php echo ($row['pic_title']); ?> - <?php echo ($row['pic_date']); ?></a></p>
<?php
}
}
mysql_free_result($result);
?>



Cheers
Steve

raf
08-09-2006, 01:57 PM
change
'%keywords'
into
'$keywords'

==> a full text search doesn't need the wildcard and the '%keywords' will be taken literal instead of that the posted keywords are injected in the query.
maybe you're just confusing full-text searches with keyword searches (where you woul have "caption LIKE '%$keywords%'")

for debugging, change your query into

$query = ("SELECT *, MATCH(caption) AGAINST('$keywords') AS match_score FROM photographs MATCH(caption) AGAINST('$keywords') ORDER BY city_town, pic_title DESC");
$result = mysql_query($query);

and then echo out the $row['match_score']

Cacus
08-09-2006, 02:47 PM
%keywords to $keywords made no difference. Incidently it was a typo.

I changed the query and nothing is output to match_score. Does this highlite a problem to you?

I have searched for text that I know exsist in multiple records.

Any how when you run a search it simply refreshes the page. If I echo $keywords in the if else statement then on refresh you'll get the search box and below what you entered. (So I know the string is getting the input)

As mentioned before if you remove the MATCH(caption) AGAINST('$keywords') from the SELECT statement the if else loop will spill the entire contents of the table (suggesting there is no problem with the connection to the Dbase)

With regards confusion over full-text/keywork searches. What I'm after is a simple search box on the site where if you search for the word Fred then the script would search every field, which I assume would be MATCH(*) AGAINST('$keywords') and echo back all the links to these pages, so would the LIKE statement be the better option. And would it work in the case of Fred Bloggs where it would kick up links to Fred Bloggs - Fred - or just Bloggs.

Cheers
Steve

Fumigator
08-09-2006, 05:03 PM
Question-- did you create a FULLTEXT index on the Caption column?

Cacus
08-09-2006, 05:19 PM
I have, and for most of the others

Cacus
08-10-2006, 12:51 PM
Becoming more confused!

Ok as mentioned before I have had no joy with the following query:

SELECT * FROM photographs WHERE MATCH (caption) AGAINST ('some text')

I have been to my PHPmyAdmin and run the query there.... No Joy.

Then I tried the following, through PHPmyAdmin:

SELECT id, MATCH (caption) AGAINST ('Town Name') FROM photographs

And I get a result it echos the id number and then a numerical value (which I assume is MATCH_SCORE)

Brilliant I assume (there's a lot of that I know!) that I can order my results by MATCH_SCORE getting the most relevant up top!

Ok now I run this:
SELECT id, MATCH (pic_title) AGAINST ('Town Name') FROM photographs

It works - Ok smug smile now growing.....
and finally this:
SELECT id, MATCH (pic_town,caption) AGAINST ('Town Name') FROM photographs

And get error message!!!
#1191 - Can't find FULLTEXT index matching the column list

What the hell. MySQL manual states match can be MATCH (col1,col2,....) and both individual selects run!!

Head hurts - may need a lie down!

Steve

guelphdad
08-10-2006, 01:32 PM
In order to use a FULLTEXT match across more than one column then you have to create that index across more than one column. You can't just create a fulltext index on column a and a separate one on column b and then try to use fulltext on column a, column b.

Post the results of the following:


show create table photographs

Cacus
08-10-2006, 04:21 PM
Hi guelphdad

The result is as follows, and indeed all cols are index separately.



Table Create Table
photographs CREATE TABLE `photographs` (\n `id` int(11) NOT NULL auto_increment,\n `image_ref` varchar(7) NOT NULL,\n `country` varchar(50) NOT NULL,\n `county_state` varchar(50) NOT NULL,\n `city_town` varchar(50) NOT NULL,\n `pic_title` varchar(100) NOT NULL,\n `pic_date` text NOT NULL,\n `pic_year` year(4) NOT NULL default '0000',\n `caption` longtext NOT NULL,\n `additional_info` longtext NOT NULL,\n `orig_file_name` varchar(50) NOT NULL,\n `orig_file_size` varchar(50) NOT NULL,\n `ava_purchase` char(3) NOT NULL,\n `hits` int(11) NOT NULL,\n `commentsNumber` int(3) NOT NULL,\n PRIMARY KEY (`id`),\n FULLTEXT KEY `country` (`country`),\n FULLTEXT KEY `county_state` (`county_state`),\n FULLTEXT KEY `pic_title` (`pic_title`),\n FULLTEXT KEY `pic_date` (`pic_date`),\n FULLTEXT KEY `caption` (`caption`),\n FULLTEXT KEY `additional_info` (`additional_info`),\n FULLTEXT KEY `orig_file_name` (`orig_file_name`),\n FULLTEXT KEY `orig_file_size` (`orig_file_size`),\n FULLTEXT KEY `city_town` (`city_town`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

Jumping ahead slightly if I remove all single index colums and replace with one

Table Create Table
photographs CREATE TABLE `photographs` (\n `id` int(11) NOT NULL auto_increment,\n `image_ref` varchar(7) NOT NULL,\n `country` varchar(50) NOT NULL,\n `county_state` varchar(50) NOT NULL,\n `city_town` varchar(50) NOT NULL,\n `pic_title` varchar(100) NOT NULL,\n `pic_date` text NOT NULL,\n `pic_year` year(4) NOT NULL default '0000',\n `caption` longtext NOT NULL,\n `additional_info` longtext NOT NULL,\n `orig_file_name` varchar(50) NOT NULL,\n `orig_file_size` varchar(50) NOT NULL,\n `ava_purchase` char(3) NOT NULL,\n `hits` int(11) NOT NULL,\n `commentsNumber` int(3) NOT NULL,\n PRIMARY KEY (`id`),\n FULLTEXT KEY `fullindex` (`country`,`county_state`,`city_town`,`pic_title`,`pic_date`,`caption`,`additional_info`,`orig_file_ name`,`orig_file_size`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
Then it errors even when I do a single select:
SELECT id, MATCH (pic_title) AGAINST ('Town Name') FROM photographs
or assuming you match the index:
SELECT id, MATCH (fullindex) AGAINST ('Town Name') FROM photographs

Assuming one index is required PHPmyAdmin requested a size on text fields. Is this for word size to be index or a space size in Kb?

Cheers

Cacus
08-11-2006, 10:01 AM
I think were getting some were!

Ok thanks to all so far, I seem to be getting my head round this a bit more!
But two more questions as of this morning.

I have created a new index for 3 colums and have altered the code to that below which now seems to produce 'some' results. But here's the rub! If I search for the town Drax it echos back the id and the city_town. Great! If I enter Drax and Airmyn it kicks up both records. (There's that big smile coming..!). If I search for Goole (of which there are many records) I get nothing! Which is bloody strange as when you search for tower (as there's are records on Goole's Water Tower, it with throw up 1Goole, 4Goole, 8Goole etc (Which is the id, city_town).

Ok question two the query fires back results in score order, which is good. but is there any way of getting the score number that you see through PHPmyAdmin as I could use it to make a 'relativity bar'.

Cheers

<p>
Full text search <br />
<form action="index.php" method="post">
Keywords:<br />
<input type="text" name="keywords" size="20" maxlength="40" value="" /><br />
<input type="submit" value="Search!" />
</form>
</p>
<?php

include('../phpmysql/config.php');
include('../phpmysql/connect.php');

if (isset($_POST['keywords'])) {

$keywords = ($_POST['keywords']);

$query = ("SELECT id, city_town, MATCH (city_town, pic_title,caption) AGAINST ('$keywords') AS match_score FROM photographs WHERE MATCH (city_town, pic_title,caption) AGAINST ('$keywords')");
$result = mysql_query($query);
$row = mysql_num_rows($result);

if ($row == 0) {
} else {
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $row['id']; echo ($row['city_town']); ?><br></br><?php
}
}
}
?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum