• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

FULLTEXT search not working the way I'd like

I have developed a search engine that should grab results based on the keywords using the "+" symbol to more target the correct keywords.  Right now, when I run a search on a person's name, the results comes back with all 3 entries in the database, rather than that person's entry.  Any thoughts?
<?php
                    	$pattern = $_GET['pattern'];
                    	$record = 0;
                    	$getRecords = sprintf("SELECT faculty.id AS fID, faculty.name AS fName, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id AS sID, school.name AS sName, department.id dID, department.name AS dName
							FROM faculty
							LEFT JOIN school ON faculty.school = school.id
							LEFT JOIN department ON faculty.department = department.id
							WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio, school.name, department.name)
							AGAINST((+'%s') IN BOOLEAN MODE)", $pattern);
							echo $getRecords;
						$recordsFound = mysql_query($getRecords) or die("Records were not found because: " . mysql_error());
						$rNum = COUNT($recordsFound);
							if($record == 0) {
								echo "<tr>";
							}
							while($r = mysql_fetch_array($recordsFound)) {
								$fID = (int)$r['fID'];
								echo "<td><a href='faculty-database-admin-edit.php?rID={$fID}'>" . $r['fName'] . "</a></td><td>" . $r['sName'] . " " . $r['dName'] . "</td><td>" . $r['research_topic'] . "</td><td>" . $r['country_region'] . "</td>";
								if($rNum == 1) {
									echo "</tr><tr>";
								}
							}
							echo "</tr>";
                    ?>

Open in new window

0
pingeyeg
Asked:
pingeyeg
  • 18
  • 5
  • 3
  • +1
4 Solutions
 
Ray PaseurCommented:
I think we have another candidate for data visualization.  Can you please print the query after it is created and before it is executed?  I think you may want to use the percent sign in there somewhere...
0
 
pingeyegAuthor Commented:
yeah, yeah.  Here is the query:
SELECT faculty.id AS fID, faculty.name AS fName, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id AS sID, school.name AS sName, department.id dID, department.name AS dName FROM faculty LEFT JOIN school ON faculty.school = school.id LEFT JOIN department ON faculty.department = department.id WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio, school.name, department.name) AGAINST((+'Maryam') IN BOOLEAN MODE)

Open in new window

0
 
pingeyegAuthor Commented:

$pattern = $_GET['pattern'];
                    	$record = 0;
                    	$getRecords = sprintf("SELECT faculty.id AS fID, faculty.name AS fName, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id AS sID, school.name AS sName, department.id dID, department.name AS dName
							FROM faculty
							LEFT JOIN school ON faculty.school = school.id
							LEFT JOIN department ON faculty.department = department.id
							WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio, school.name, department.name)
							AGAINST((+'%s') IN BOOLEAN MODE)", $pattern);

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pingeyegAuthor Commented:
All MATCH fields are FULLTEXT enabled.
0
 
Ray PaseurCommented:
What is the plus sign supposed to do in that query?
0
 
pingeyegAuthor Commented:
And here is a visual of what is being ouput:
Screen-shot-2010-01-03-at-12.49..png
0
 
pingeyegAuthor Commented:
+ A leading plus sign indicates that this word must be present in every row returned.
0
 
Ray PaseurCommented:
My "Match" example looks like this...

SELECT title, MATCH(synopsis) AGAINST ('humor') AS score FROM books LIMIT 10

From that I get a results set with titles and scores - so I can see which matched best.
0
 
Ray PaseurCommented:
Maybe MATCH and its arguments belong in the SELECT part of this query instead of the WHERE part.
0
 
pingeyegAuthor Commented:
Ok, I tried that now, but no different results:
SELECT faculty.id AS fID, faculty.name AS fName, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id AS sID, school.name AS sName, department.id dID, department.name AS dName, MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio, school.name, department.name) AGAINST((+'Maryam') IN BOOLEAN MODE) FROM faculty LEFT JOIN school ON faculty.school = school.id LEFT JOIN department ON faculty.department = department.id

Open in new window

0
 
Ray PaseurCommented:
Interesting.  Have you tried this on a simple query against only one table and one column?
0
 
pingeyegAuthor Commented:
I must not fully understand the workings and syntax of the Match...Against query method because I'm not getting anything when I run the following query:
SELECT name, MATCH(name, school, research_topic) AGAINST((+'Maryam') IN BOOLEAN MODE) FROM faculty

Open in new window

0
 
pingeyegAuthor Commented:
Ok, I ran that query in phpMyAdmin and got the following so I know it's working.  Apparently I'm not outputting it correctly.
Screen-shot-2010-01-03-at-1.33.3.png
0
 
leakim971PluritechnicianCommented:
Hello,

>Apparently I'm not outputting it correctly.

Could you run the following and check if the fields values change ?

Regards.
SELECT school.*, department.* FROM faculty LEFT JOIN school ON faculty.school = school.id LEFT JOIN department ON faculty.department = department.id
WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio, school.name, department.name) AGAINST((+'Maryam') IN BOOLEAN MODE)

Open in new window

0
 
pingeyegAuthor Commented:
I've changed it a little bit since then:
$getRecords = sprintf("SELECT faculty.id, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id, school.name AS sName, department.id, department.name AS dName,
                    		MATCH(faculty.name, faculty.research_topic, faculty.country_region, school.name, department.name)
                    		AGAINST(('+%s') IN BOOLEAN MODE) AS relevance
							FROM faculty
							LEFT JOIN school ON faculty.school = school.id
							LEFT JOIN department ON faculty.department = department.id
							WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, school.name, department.name)
                    		AGAINST(('+%s') IN BOOLEAN MODE)", $pattern, $pattern);

Open in new window

0
 
pingeyegAuthor Commented:
Nothing is returned with your query.
0
 
Tony McCreathTechnical SEO ConsultantCommented:
The + should be inside the quotes not outside

+'word'

change to

'+word'
0
 
pingeyegAuthor Commented:
Does '+%s' not mean inside quotes?
0
 
pingeyegAuthor Commented:
This is the result from the query I posted above:
SELECT faculty.id, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id, school.name AS sName, department.id, department.name AS dName, MATCH(faculty.name, faculty.research_topic, faculty.country_region, school.name, department.name) AGAINST(('+Maryam') IN BOOLEAN MODE) AS relevance FROM faculty LEFT JOIN school ON faculty.school = school.id LEFT JOIN department ON faculty.department = department.id WHERE MATCH(faculty.name, faculty.research_topic, faculty.country_region, school.name, department.name) AGAINST(('+Maryam') IN BOOLEAN MODE)

Open in new window

0
 
Tony McCreathTechnical SEO ConsultantCommented:
Sorry, didn't fully check your last query example.

As a note, IN BOOLEAN mode does not return relevance. It returns the number of words from your search phrase that matched.

Not sure but I think you have to have a single FULLTEXT index that contains all the fields in your query, if you want it to be used...

"The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow."

Saying that, your problem maybe that your fields in the full text span multiple tables.

Maybe changing your WHERE clause to explicitly state "> 0" would help.

What is your current problem, no rows or all rows?
0
 
pingeyegAuthor Commented:
My current problem is all rows.
0
 
pingeyegAuthor Commented:
Ok, I'm getting closer, but not sure about the error here:

Records were not found because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH(faculty.name, faculty.research_topic, faculty.country_region, faculty.bio)' at line 3
SELECT faculty.id, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id, school.name AS sName, department.id, department.name AS dName, 
MATCH (

department.name
)
MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
MATCH (
school.name
)
AGAINST (
'Maryam'
) AS relevance
FROM faculty
LEFT JOIN school ON faculty.school = school.id
LEFT JOIN department ON faculty.department = department.id
WHERE MATCH (
department.name
)
MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
MATCH (
school.name
)
AGAINST (
'Maryam'
)

Open in new window

0
 
pingeyegAuthor Commented:
Last attempt.  Please help.

Can't find FULLTEXT index matching the column list
SELECT faculty.id, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id, school.name AS sName, department.id, department.name AS dName, 
MATCH (

department.name
)
AGAINST (
'Maryam'
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'Maryam'
)
OR MATCH (
school.name
)
AGAINST (
'Maryam'
)
FROM faculty
LEFT JOIN school ON faculty.school = school.id
LEFT JOIN department ON faculty.department = department.id
WHERE MATCH (
department.name
)
AGAINST (
'Maryam'
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'Maryam'
)
OR MATCH (
school.name
)
AGAINST (
'Maryam'
)

Open in new window

0
 
pingeyegAuthor Commented:
Ok, so it turns out I needed to run a SQL statement in phpMyAdmin in order to fully do a FULLTEXT index.  I was using the link next to the column instead so all the content was not being indexed.  Now the error is gone, but I'm not getting an results from the word 'Maryam' when there is a Maryam in the database under the name field of faculty.  Any reason why this would be?
SELECT faculty.id, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id, school.name AS sName, department.id, department.name AS dName, 
MATCH (
department.name
)
AGAINST (
'Maryam'
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'Maryam'
)
OR MATCH (
school.name
)
AGAINST (
'Maryam'
) AS relevance
FROM faculty
LEFT JOIN school ON faculty.school = school.id
LEFT JOIN department ON faculty.department = department.id
WHERE MATCH (
department.name
)
AGAINST (
'Maryam'
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'Maryam'
)
OR MATCH (
school.name
)
AGAINST (
'Maryam'
)

Open in new window

0
 
pingeyegAuthor Commented:
Ok, I'm now good to go.  I forgot the Match...Against will not work until there are at least 3 records in the db.  Woops... my bad.
0
 
pingeyegAuthor Commented:
I'm not really sure who I should award the points to though since I never received a solution to my question.
0
 
Tony McCreathTechnical SEO ConsultantCommented:
Maybe award points where answers helped you on your way and give it a low grade.

http://www.experts-exchange.com/help.jsp#hs=29&hi=403 

Or you could request for attention and ask the zone advisor for advice.

Thanks for adding your final solution as that will help others.

You might be able to award points to your own answer?

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now