#1
  1. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    I have an input box where users can input word(s) to search through the database and I'm using Perl DBI and placeholders for the WHERE LIKEs for each field, but how would you get this to work for multiple word searches?

    For example, as I have it set up right now, if a user searched for "John Doe" (and a record exists that has "John" in the firstName field and "Doe" in the lastName field, 0 matches will be returned because not one of the fields contains the entire string of "John Doe".

    Obviously, this is not good. Is there any way around this?

    Thanks,
    RyanP

    [This message has been edited by RyanP (edited June 29, 2000).]
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    13
    Rep Power
    0
    Why don't you just put two search field: firstName & lastName?


    ------------------
    ---------
    Stories OnLine
    http://www.stories-online.net/

    WARNING: adult stories!
  4. #3
  5. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    There are several other fields in the table too and I have included an optional pull down menu to select a particular field to search in, but I also want to provide the option to search in all fields with multiple word searches.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    15
    if the $searchstring is "John Doe"....

    # split $searchstring between occurrence(s) of spaces

    @search_list=split(/ +/, $searchstring);

    $sql="select * from table_name where ";

    for($i=0; $i<@search_list; $i++)
    {
    $sql.="field_name like '%$search_list[$i]%' OR ";
    }

    # now remove trailing "OR "

    $sql=substr($sql, 0, -3);

    execute the query....

    [This message has been edited by Kyuzo (edited June 30, 2000).]
  8. #5
  9. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    Thanks Kyuzo, that is just what I was looking for. Although I would prefer to use placeholders and bind_params, this is probably the only way to do this. Because I will be inserting variables directly into the query statement, what characters do I need to filter out of the search string? Just single quotes with ' ?

    [This message has been edited by RyanP (edited June 30, 2000).]
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    43
    Rep Power
    15
    Actually there is one other better way to do this using regular expressions.
    $string = "John Doe";
    $string =~ s/ /|/g;
    this replaces spaces with the | character. You end up with John|Doe
    Then the query is:
    $sql = "SELECT * FROM table_name WHERE lcase(field_name) REGEXP lcase($string)";
    The query can be extended to search multiple fields by using OR AND etc.

Similar Threads

  1. Replies: 28
    Last Post: December 25th, 2003, 02:28 PM
  2. Using PHP to get a word count of a MSword doc
    By spelchek in forum PHP Development
    Replies: 6
    Last Post: September 6th, 2003, 12:25 PM
  3. variable does not get the full word
    By dinakar in forum PHP Development
    Replies: 8
    Last Post: March 31st, 2003, 06:32 AM
  4. Replies: 0
    Last Post: November 30th, 2000, 07:50 PM
  5. search in a databse with more than one variable
    By Roland_Berlin in forum PHP Development
    Replies: 0
    Last Post: May 8th, 2000, 04:47 AM

IMN logo majestic logo threadwatch logo seochat tools logo