[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query is returning an error

Posted on 2010-01-08
8
Medium Priority
?
175 Views
Last Modified: 2012-05-08
I have a query that is returning an error.  The query is based on FULLTEXT searching.  The error I am getting is:

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 ' 'maryam') OR MATCH(school.name) AGA' at line 5
SELECT faculty.id fID, faculty.name, faculty.school, faculty.department, faculty.research_topic, faculty.country_region, faculty.picture, faculty.bio, school.id sID, school.name AS sName, department.id dID, department.name AS dName, 
MATCH (

department.name
)
AGAINST (
''
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'43',  'maryam'
)
OR MATCH (
school.name
)
AGAINST (
''
) 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 (
''
)
OR MATCH (
faculty.name, faculty.research_topic, faculty.country_region, faculty.bio
)
AGAINST (
'43',  'maryam'
)
OR MATCH (
school.name
)
AGAINST (
''
)
OR WHERE faculty.id =43

Open in new window

0
Comment
Question by:pingeyeg
  • 4
  • 3
8 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26211912
Hmm... I wonder if this repeated sequence could have anything to do with it?

AGAINST (
'43',  'maryam'
)
OR MATCH (
school.name
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26211965
Am I not to have the MATCH...AGAINST in there twice?  Is that what you are referring to?
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 26212082
Just a wild guess, but does the MATCH portion of the syntax need the same number of items as the AGAINST portion?
0
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!

 
LVL 1

Author Comment

by:pingeyeg
ID: 26212092
Based on the research I have done, I don't think so.
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26212839
Did you guys give up on me?
0
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 26212919
If I'm reading this reference correctly (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match), the AGAINST clause is not expecting a comma and another search term.
The AGAINST clause syntax appears to be:
AGAINST (expression [search modifier])
where expression would be '43' OR 'maryam' (not both) and search modifier (it is optional) would either: IN BOOLEAN MODE or WITH QUERY EXPANSION
0
 
LVL 1

Author Comment

by:pingeyeg
ID: 26212999
Ok, so that makes sense and the error is gone, but why would the 43 not be found in the database, when the id for one of the members is 43?
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 26213449
Ah, but are you searching the correct field (MATCH) for 43 (AGAINST)? I.e. are you sure 43 is in one of these fields: faculty.name, faculty.research_topic, faculty.country_region, faculty.bio? If you change '43' to 'maryam' do you find matches?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question