Link to home
Start Free TrialLog in
Avatar of pingeyeg
pingeyeg

asked on

Query is returning an error

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

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Hmm... I wonder if this repeated sequence could have anything to do with it?

AGAINST (
'43',  'maryam'
)
OR MATCH (
school.name
Avatar of pingeyeg
pingeyeg

ASKER

Am I not to have the MATCH...AGAINST in there twice?  Is that what you are referring to?
Avatar of Shaun Kline
Just a wild guess, but does the MATCH portion of the syntax need the same number of items as the AGAINST portion?
Based on the research I have done, I don't think so.
Did you guys give up on me?
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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?