Page 1 of 1

3 Character Search Failure

Posted: Tue May 08, 2007 11:29 pm
by ripcurlksm
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Can anyone help me figure out why this MySQL query is not returning any results when a three-letter search or smaller is tried (ie- "red" or "cat")

This search is suppose to be a VERY BROAD keyword search if anyone can suggest an improvement I would appreciate it:
[syntax="sql"]SELECT u.id
     , u.username
	 , p.user_id
	 , p.report_id
     , r.id
     , r.company 
     , r.description
     , r.market1
     , r.market2
     , r.market3
     , r.market4
     , r.market5
     , r.market6
     , r.location
     , r.date_year
     , r.date_month
     , r.source
     , r.video
     , r.audio
     , r.pp
     , r.execsum
     , r.report_url
     , r.exec_url	  
  FROM user as u
INNER
  JOIN user_reports as p
    ON p.user_id = u.username
INNER
  JOIN emt_report as r
    ON r.id = p.report_id
WHERE username = '$username' AND MATCH(r.company, r.description, r.market1, r.market2, r.market3, r.market4, r.market5, r.market6, r.source) AGAINST ('%$P_search*%' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed May 09, 2007 6:23 am
by mentor
MySQL FULLTEXT search does not index words less then 4 characters by default. But this behaviour can be changed. Visit the MySQL manual