SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching...Problem

    How could I search for a word or phrase into mysql fields... like what search engines do.

    I have a mysql database with 10 fields all are text, in my site I want to include a search box where my visitor can type a word or phase then the script will search the keyword or phrase in the database fields.. could u please give me an example how to do this..


    Thanks

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the SitePont Anthology 1 book is the following example:
    Code:
    SELECT * FROM articles
    WHERE MATCH (title, body, author) AGAINST ('MYSQL');
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  3. #3
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $search_string $_POST["search"];

    $sql mysql_query("SELECT * FROM table WHERE text LIKE '%$search_string%'"); 

  4. #4
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys..

    Thanks for the quick reply i will let you know..if the above codes.. works... exactly what I want...


  5. #5
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I try to use :

    SELECT * FROM mytable
    WHERE MATCH (field1,field2,field3) AGAINST ('key word');
    but I got error:
    Error: Can't find FULLTEXT index matching the column list
    How could I use... it?

  6. #6
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    Code:
    $sql = "SELECT * FROM mytable
    WHERE MATCH (field1,field2,field3) AGAINST ('key word')";
    
    $result = @mysql_query($sql, $dbConn);
    
    $num_results = mysql_num_rows($result);
    
    if ($num_results == 0) {
      // do something here
    } else {
      // do something else here
    }
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  7. #7
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do your suggestion but I get the same error... do I need to create a FULLTEXT index? if so how could I do that with my existing table?

    Thanks

  8. #8
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Note to Self: When all else fails, read the book a little more closely.

    Herewith is what the book (PHP Anthology 1, page 98) says:
    To take advantage of FULLTEXT indexes, you first need to instruct MySQL to begin building an index of the columns you want to search:
    Code:
    ALTER TABLE table-name ADD FULLTEXT table_search (field1, field2, field3)
    Once you've done that, you need to INSERT a new record (or modify an existing one) to get MySQL to build the index. You also need at least three records in the database for FULLTEXT to work, because non-Boolean searches will only return results if the search string occurred in less than 50% of the rows in the table (if there are only two rows in the table, and your search matches one row, that makes 50%). One final thing to be aware of is that FULLTEXT searches will only match searches of more than three letters; the indexing mechanism ignores words of three characters or less, to avoid having to build a massive index. This is much like the index of a book; you'd be pretty surprised to discover in a book's index exactly which pages the word "the appeared on!
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  9. #9
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    :cheers: now it's working... but not very satisfied with the performance..

    Some times even do the keyword or phrase I type on my search box is exist on the fulltext field, it won't return a result for that keyword.

    example I type the key words business, there are more than 20 business word in the database but it returns 0 result.

    Do you know how can I solve this? Or is there a way to improve the performance of the script thanks...

  10. #10
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, you're getting way out of my area of expertise with questions like that. Hopefully, someone with a great deal more knowledge than me will come along and offer some help.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  11. #11
    SitePoint Member edpudol's Avatar
    Join Date
    Oct 2004
    Location
    philippines
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem the information you have been give are worth enough to start and improve... once again thanks...


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
  •