SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching multiple words in database

    im trying to create a simple related search using php, but i cant get it to work with multiple words. using food as an example, lets say im trying to find something thats related to cheese burgers in my database and i have this in my database

    id food name
    1 cheese burgers
    2 pizza
    3 burger
    4 veggie burger
    5 cheese pizza

    i would imagine it would look for both words, cheese and burger so it would end up finding


    burger
    veggie burger
    cheese pizza

    but i can only get it to find exact matches. tried using LIKE but it didnt work either

  2. #2
    SitePoint Enthusiast scarecrow-rye's Avatar
    Join Date
    Nov 2005
    Location
    Cornwall, UK
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about...

    SELECT id, food_name FROM foods WHERE food_name LIKE '%cheese%' OR food_name LIKE '%burgers%';

    ...off the top of my head?

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want it to find matches for both words use the or operator and split the word.
    PHP Code:
    <?php
    $search_term 
    "cheese burger pizza";

    if ( 
    strpos$search_term" " ) ) {
        
    $terms explode" "$search_term );
        
    $query "select * from foods where food like '";
        
    $i 0;
        
    $ub count$terms );
        foreach( 
    $terms as $V ) {
            if ( 
    $i == ) {
                
    $query .= $V ."'";
            }else {
                
    $query .= " or food like '" .$V ."'";
            }
            
    $i++;
        }
    }

    echo 
    $query;
    Yields :
    select * from foods where food like 'cheese' or food like 'burger' or food like 'pizza'
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  4. #4
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    If you want it to find matches for both words use the or operator and split the word.
    PHP Code:
    <?php
    $search_term 
    "cheese burger pizza";

    if ( 
    strpos$search_term" " ) ) {
        
    $terms explode" "$search_term );
        
    $query "select * from foods where food like '";
        
    $i 0;
        
    $ub count$terms );
        foreach( 
    $terms as $V ) {
            if ( 
    $i == ) {
                
    $query .= $V ."'";
            }else {
                
    $query .= " or food like '" .$V ."'";
            }
            
    $i++;
        }
    }

    echo 
    $query;
    Yields :
    select * from foods where food like 'cheese' or food like 'burger' or food like 'pizza'
    wow, okay thanks a lot man

  5. #5
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i bumped into a little problem with that one, apperantly if the search term only has one word it doesnt recognize the query variable. i keep getting this warning

    Notice: Undefined variable: query

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put you an else clause to the first if statement ( the one that checks for a space in the keywords). if there is no space the query would be :
    PHP Code:
    query "select * from foods where food like '" .$search_term"'"
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  7. #7
    SitePoint Zealot glowdot's Avatar
    Join Date
    Jun 2007
    Location
    Santa Monica, CA
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You guys know MySQL has full-text search built in, right? That's the way to do this. Then you end up with a query like:

    Code:
    SELECT * FROM foods WHERE MATCH (food) AGAINST ('+burger +pizza' IN BOOLEAN MODE);
    You just need to enable full-text search for the fields you want to query:

    Code:
    ALTER TABLE foods ADD FULLTEXT(food);
    Advertise on Glowfoto
    banners as low as $25/month, text ads $10/month
    Share 10 million impressions per month!

  8. #8
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    Put you an else clause to the first if statement ( the one that checks for a space in the keywords). if there is no space the query would be :
    PHP Code:
    query "select * from foods where food like '" .$search_term"'"
    yeah but if you change it to

    $search_term = "cheese";

    it doesnt echo anything

  9. #9
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did not use "=" I used "like"
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  10. #10
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by byron3@earthlink View Post
    I did not use "=" I used "like"
    yeah i know, i meant if you put it like this

    HTML Code:
    <?php
    
    $search_term = "cheese";
    
    
    
    if ( strpos( $search_term, " " ) ) {
    
        $terms = explode( " ", $search_term );
    
        $query = "select * from foods where food like '";
    
        $i = 0;
    
        $ub = count( $terms );
    
        foreach( $terms as $V ) {
    
            if ( $i == 0 ) {
    
                $query .= $V ."'";
    
            }else {
    
                $query .= " or food like '" .$V ."'";
    
            }
    
            $i++;
    
        }
    
    }
    
    
    
    echo $query;

  11. #11
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow, i forgot about this thread... anyone else know how i could get this to work?

  12. #12
    SitePoint Zealot glowdot's Avatar
    Join Date
    Jun 2007
    Location
    Santa Monica, CA
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I realize you just glossed right over my post, so I'll just point at it again. Use full-text search for this.
    Advertise on Glowfoto
    banners as low as $25/month, text ads $10/month
    Share 10 million impressions per month!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i do not do php, but it appears that the sql generating script is too complex

    the SQL should look like this --
    Code:
    select ...
      from ...
     where 1=0
        or food like '&#37;cheese%'
        or food like '%burger%'
    so you simply append additional conditions using ORs

    if you want the results to have all words (instead of any), change the sql to say WHERE 1=1 and then use ANDs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by glowdot View Post
    I realize you just glossed right over my post, so I'll just point at it again. Use full-text search for this.
    no i didnt just gloss over your post, i was just working with the other code at the moment, and your post wasnt really that specific

    but ill give it a try, its just kinda annoying having to go into my database


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
  •