MySQL Fulltext query problem...

Discussion in 'OT Technology' started by sholnay, Oct 8, 2006.

  1. sholnay

    sholnay New Member

    Joined:
    Apr 24, 2005
    Messages:
    62,435
    Likes Received:
    0
    Location:
    Wellington NZ || Dallas TX
    Im having some trouble with my search.

    this is the code im using:



    PHP:
    $searchsql "SELECT *, 
    MATCH(title) AGAINST(
    $searchkey
    AS score 
    FROM moviedb WHERE 
    MATCH(title) AGAINST(
    $searchkey)
    ORDER BY score DESC"
    ;


    That's all fine and dandy. it works fine. the problem is when I introduce another column to search.

    THIS does not work:

    PHP:
    $searchsql "SELECT *, 
    MATCH(title,year) AGAINST(
    $searchkey
    AS score 
    FROM moviedb WHERE 
    MATCH(title,year) AGAINST(
    $searchkey)
    ORDER BY score DESC"
    ;
    the difference is in the MATCH(title,year). SQL returns this error:

    #1191 - Can't find FULLTEXT index matching the column list.

    I can assure you both YEAR and TITLE are fulltext.

    from phpMyAdmin:

    Code:
     title  	 FULLTEXT  	 None   	 Edit   	 Drop   	 title
    year 	FULLTEXT 	None  	Edit 	Drop 	year
    
    im so lost. This WAS working previously on an older version of PHP/MySQL... now im using the latest XAMPP distro and its descompuesto. I tried looking through the PHP/MySQL sites to no avail.

    Ohh.. Im ALSO able to do MATCH(year)... this works fine for searching the year alone. why can I not combine both? I have read many tutorials that have both in there. its so strange. :dunno:
     
  2. sholnay

    sholnay New Member

    Joined:
    Apr 24, 2005
    Messages:
    62,435
    Likes Received:
    0
    Location:
    Wellington NZ || Dallas TX
    okay - solved the problem for those who might be interested.

    Being the non guru that I am, it was all by coincidence.

    when adding FULLTEXT support to the database, if you want to search more than one column - you have to add the fulltext in the same query.
    ex:

    if you issue statements like this:
    ALTER TABLE tablename ADD FULLTEXT(title);
    ALTER TABLE tablename ADD FULLTEXT(year);
    your database will only be searchable by one of these at a time in your MATCH statement. so either MATCH(year) or MATCH(title) will work.

    If you want to match to both of them, you must issue this when creating fulltext indexing:
    ALTER TABLE tablename ADD FULLTEXT(title,year);
     
  3. AznRyda

    AznRyda キモかわいい!

    Joined:
    May 6, 2000
    Messages:
    28,632
    Likes Received:
    0
    Location:
    残業中
    Its because if you want to use full text searching on multiple columns you have to specify that in your table properties. >_<
     

Share This Page