SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help excluding certain terms from database search.

    Hi folks -thanks for any input.

    I'm running a MySQL database client version: 3.23.58

    The database contains a lot of comic strips and is designed to be searchable by keyword (among other things). Each strip's record contains keywords related to its content, and it also includes a list of every character who appears in that strip.

    However, I want to restrict people from searching for specific keywords, for example "George", because searching for the word George would bring up nearly all of the strips in the database, as George is in almost all of them - something we're hoping to avoid. With thousands of records, it wouldn't make sense for me to remove all instances of George from the keywords field, either.

    Here's the way my query is presently structured. I capture the keywords from an input form, trim the whitespace and then run the query.

    Code:
    $query = "select * from table where description like \"%$trimmed%\" order by date";
    This works well to let people search for the terms we want to allow, like "flower", "flowers", "flowering", but I want to refuse to let people enter "George", "Georg", "orge" etc. and having every George-related strip show up.

    I started experimenting with if statements to deny the search based on the value of %$trimmed%, but as you can see this gets problematic quickly especially for characters with longer names. There's got to be a better method.

    Code:
    if (
    $trimmed == "George" or
    $trimmed == "george" or
    $trimmed == "eorge" or
    $trimmed == "orge" 
    )
     {
    echo "<p>Generate message denying the search</p>";
    exit;
    }
    Any ideas? Thanks so much.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,897
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    PHP Code:
    $forbidden = array("George","george","eorge","orge");
    if (
    in_array($trimmed$forbidden))
    {
      echo 
    "<p>Generate message denying the search</p>";
      exit;

    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you - that's certainly a more economical way of doing the coding; I'm hoping to avoid having to come up with a list of all possible combinations of the letters in the characters' names. Is there a way of setting up the array so that $forbidden = "George" or any sequential combination of the letters in the word without having to come up with all the permutations by hand?

    For a name like Victoria, there could be hundreds of combinations of search terms used to get around the restriction: ictoria, cto, ictor, vic, vi, ria, you get the idea. Thanks.

  4. #4
    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)
    stick all the keywords you don't want used into a "forbidden" table

    just the whole words, like George and Victoria, not the fragments

    then use
    Code:
    WHERE keyword LIKE '&#37;$trimmed%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks folks. I ended up restricting the search to a max of 50 results for now...there didn't seem to be any way, with the current table structure, to get the effect that was necessary. At least this way I can make them refine their keyword search if they didn't get the results they wanted in the first shot, without exposing all the contents.

    I'm going to try Rudy's suggestion next (thanks!) and probably buy your book. Appreciate your help.


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
  •