#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,681
    Rep Power
    171

    Best "search" practice.


    Hello I am trying to make a generic search on top right corner of this page. It doesn't work too bad but it is not perfect. I wrote the code below and it woks most of the times but not all the times. For exmple for term "yoga" it works but for "Yoga voucher" it doesn't!

    What do you recommend?

    Thanks

    PHP Code:
     $sql "SELECT *,
           atless_deals.id  AS DID,
           photo_query.file AS file
    FROM   atless_deals
           LEFT OUTER JOIN (SELECT file,
                                   deal_id
                            FROM   atless_deal_photo
                            ORDER  BY main DESC) AS photo_query
                        ON photo_query.deal_id = atless_deals.id
    WHERE  atless_deals.status = :status
           AND atless_deals.description LIKE :description
    GROUP  BY atless_deals.id
    ORDER  BY atless_deals.id DESC"
    ;
    $args_deals = array('description'=>'%'.$_POST['generic_search'].'%'); 
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    PHP Code:
     $sql "SELECT *, 
           atless_deals.id  AS DID, 
           photo_query.file AS file 
    FROM   atless_deals 
           LEFT OUTER JOIN (SELECT file, 
                                   deal_id 
                            FROM   atless_deal_photo 
                            ORDER  BY main DESC) AS photo_query 
                        ON photo_query.deal_id = atless_deals.id 
    WHERE  atless_deals.status = :status 
           AND LOWER(atless_deals.description) LIKE :description 
    GROUP  BY atless_deals.id 
    ORDER  BY atless_deals.id DESC"

    $args_deals = array('description'=>'%'.strtolower(mysql_real_escape_string($_POST['generic_search'])).'%'); 
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Based on the code structure I assume he's using a prepared statement already.


    You could look into match_against. It's not perfect, but often better than LIKE. Writing a good search system is pretty hard beyond those two methods.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    Based on the code structure I assume he's using a prepared statement already.
    I know, I wrapped everything in UPPER calls.

    match_against
    This requires certain indexes and table types and I was too tired to write all that out, but it's the correct solution.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,681
    Rep Power
    171
    Imagine a Software Developement company with E-oreo, Requinx, Northie, Rudy, catacaustic, ManicDan.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    We'd never get anything done, we'd argue constantly about how to make it perfect.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

IMN logo majestic logo threadwatch logo seochat tools logo