SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't find FULLTEXT index matching the column list

    here is the code im usin for the search..

    PHP Code:
    $query mysql_query("SELECT article, title, page_title FROM news 
    WHERE MATCH (article,title) AGAINST ('keyword')"
    ) or die(mysql_error()); 
    and the error

    Can't find FULLTEXT index matching the column list.

    columns

    FULLTEXT KEY title (title),
    FULLTEXT KEY article (article)

    mysql version 3.23.37

    any ideas why i get the error?
    http://redgoals.com ... my site ... nuff said

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Can't find FULLTEXT index matching the column list

    PHP Code:
    $query mysql_query("SELECT article, title, page_title FROM news 
    WHERE MATCH (article,title) AGAINST ('keyword')"
    ) or die(mysql_error()); 
    you've only these 2 indices:
    FULLTEXT KEY title (title),
    FULLTEXT KEY article (article)

    but you're matching (article,title) which requires a FULLTEXT index of (article,title)...

    you'd need to create a new FULLTEXT index for (article,title)

  3. #3
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi, thnx for the reply.

    i didnt quite understand what you meant by:

    "create a new FULLTEXT index for (article,title)"

    i thought i already did that when i altered the table?

    anyway i ve got this far with the query and it seems to be workin but not printing anything out:

    PHP Code:
    <?
    $query 
    "select article,title FROM news where MATCH(title) AGAINST ('manchester')" or die(mysql_error());
    ?>
    now how do i go about echoing the result of that query?
    http://redgoals.com ... my site ... nuff said

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    read the manual here: http://www.mysql.com/doc/F/u/Fulltext_Search.html

    excerpt from Tech Times:
    Say you have a table called Products with columns called Title and Description (among others), and you want visitors to be able to enter keywords to search based on those two columns. First, add a FULLTEXT index to your Products table for those two columns:


    Code:
    ALTER TABLE Products ADD FULLTEXT (Title,Description)
    you need another FULLTEXT index (article,title)... having 2 separate indices FULLTEXT KEY title (title),
    FULLTEXT KEY article (article) is different from one index based on 2 columns...

  5. #5
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh i c thnx for pointing that out redemption, sometimes i can be a little...u know.
    http://redgoals.com ... my site ... nuff said

  6. #6
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone know how i can highlight the matched word?
    http://redgoals.com ... my site ... nuff said

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this thread should help you...

    i myself needed that too so thanks for making me go search for it

  8. #8
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi there ...

    can i know about Full-text Search ?

    can u plz post simple code about it ?

  9. #9
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i only learnt about it through the SitePoint tech times newsletter.

    its very fast though and heres some simple code:

    PHP Code:
    <?
    $query 
    mysql_query("SELECT article, title FROM news WHERE MATCH (article,title) AGAINST ('$keyword')") or die(mysql_error());
    while(
    $r=mysql_fetch_array($query)) {
    $title=$r["title"];
    $article=$r["article"];
    echo 
    "$article | $title";
    }
    ?>
    you have to add a fulltext index (refer to redemtion's post above) on the two columns article,title or whatever your columns are called.
    http://redgoals.com ... my site ... nuff said

  10. #10
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i could't get the highlight code to work for me, did it work for you redemption?

    $article = str_replace('$keyword', '<b>$keyword</b>', $article);
    http://redgoals.com ... my site ... nuff said

  11. #11
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i had made phonebook, now there more than 1500, include every thing ..

    i write search code with using like '%$search%' ..

    if i rewrite it with full text search will be fast ?

    full hightlighting the searched word :
    PHP Code:
    $print .= "<td><font color=\"#ff6600\">"str_replace ("$search","<font color=\"#ff0000\"><B>$search</B></font>",$name) ."</td>"
    i think the same as u wrote there !

  12. #12
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FULLTEXT search has improved the speed of my searches by about 40% - 50%.

    id definately choose it.

    i cant get the highlight code to work, any one else have any other ways of highlighting?
    http://redgoals.com ... my site ... nuff said

  13. #13
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ur code is ok as i see ...

    but how u printed out, maybe u miss something there !

  14. #14
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok fixd it now, thanx for the nudge in the right direction Richi


    PHP Code:
    echo ""str_replace ("$keyword","<font color=\"#ff0000\"><B>$keyword</B></font>",$article) ." | $title"
    http://redgoals.com ... my site ... nuff said

  15. #15
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about case highlighting?

    i want it to highlight both cases of the keyword?
    http://redgoals.com ... my site ... nuff said

  16. #16
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm... what u mean ?

    to hiightling more than one wrod ?

  17. #17
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what i mean is the str_replace wont match "PHP" if the keyword is "php"

    i want it to match both cases of the same keyword not seperate keywords.
    http://redgoals.com ... my site ... nuff said

  18. #18
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    u need to use strtolower ..

    so when u query the data from db u need to make it lower case, and also the $keyword to lower case ..
    strtolower
    (PHP3 , PHP4 )

    strtolower -- Make a string lowercase
    Description

    string strtolower (string str)


    Returns string with all alphabetic characters converted to lowercase.

    Note that 'alphabetic' is determined by the current locale. This means that in i.e. the default "C" locale, characters such as umlaut-A () will not be converted.

    Example 1. Strtolower() example

    $str = "Mary Had A Little Lamb and She LOVED It So";
    $str = strtolower($str);
    print $str; # Prints mary had a little lamb and she loved it so



  19. #19
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    or u can make it as an option for the user, with radio button (uppercase, lowercase) ..

    and make 2 function for that !

  20. #20
    And your point is.... redgoals's Avatar
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    496
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i decided to turn to my good old friend preg_replace..

    PHP Code:
    $article preg_replace("/($keyword)/i"'<b>\\1</b>'$article); 
    thnx for the help though Richi, much appreciated.
    http://redgoals.com ... my site ... nuff said

  21. #21
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •