#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    153 Taylor St
    Posts
    2
    Rep Power
    0
    Need more than a simple single keyword query into MySQL database.

    Need to perform a multiple keyword query in multiple MySQL database fields. How do I do that?

    Thnaks,
    Ed
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    15
    Hi Ed,

    Can you give us an example of what you want?

    Peter
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Location
    153 Taylor St
    Posts
    2
    Rep Power
    0
    Yes, here are a couple of examples:
    * ProLiant 8500 Quickspecs
    * Linux applications
    * AlphaServer storage adapters
    * multiple keyword query
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    15
    Hi Ed,

    Sorry, I was a bit fuzzy yesterday. Got the clue now.

    One way to solve your problem is to have a form field in which visitors can put in more than one word (e.g. the altavista style). You can also use a multiple select-box, but I presume you want people to fill in the words themselves.
    Like: <INPUT TYPE="TEXT" NAME="SearchWords">

    Let's say you want them to type the words seperated by a space (" ").

    The submitted result returns a string variable named $SearchWords containing >=0 words.
    Then you have to separate the words again and check them against the database field. You also have to decide whether the words are in an AND or OR clause.
    Let's assume you use only the AND clause, you could do:

    //Putting all individual words in an array
    if ($SearchWords)
    {
    $SearchWords = trim($SearchWords); // delete surrounding spaces
    $SearchWord = explode(" ", $SearchWords); // make array of individual words
    }

    //Definition of the query
    selectquery = "SELECT * FROM table_name";

    //Cycle through the words-array if there are word(s) filled in
    if (isset($SearchWord))
    {
    selectquery .=" WHERE ";
    for($i = 0; $i < count($SearchWord); $i++)
    {
    $selectquery .= "search_column LIKE '%" . $SearchWord[$i] . "%'";
    if ($i < count($SearchWord)-1)
    {
    $selectquery .= " AND ";
    }
    }
    }

    Succes, Peter




    [This message has been edited by PAV (edited January 25, 2000).]
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    6
    Rep Power
    0
    Hi Peter,

    I'm trying to do the same thing with 3 columns (any one of the 3 columns can have the search words in it). Like, if someone does a search for "big red dog", it will look through the columns and it will show records that contains the words the user submitted. The words can be in any order. Such as the following records:

    "that is a big and red dog"
    "big and red dogs are mean!"
    "i hate big, red dogs"
    "my name is red big dog"
    etc. etc.

    Any idea on how to do this? I'm not very good at PHP yet to do this.

    Also, what is "isset"?

    Also, is it possible for one column to have one of the words and another column can contain the other 2.

    [This message has been edited by asdf1237 (edited January 25, 2000).]

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

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    15
    Hi Ed,

    The isset() function checks whether or not a variable is set. I.E. the $SearchWord array is only set when there is a value in $SearchWords.

    From your comment I presume you want to use the OR clause (word(s) in one OR more columns).
    Just rewrite the code, to cycle through the 3 colums, as follows:

    //Make array of the columns you want to search in
    $column = ("column_name1", "column_name2", "column_name3");

    //Cycle through the words-array if there are word(s) filled in
    if (isset($SearchWord))
    {
    selectquery .=" WHERE ";
    // round 1: cycle through the columns
    for($j = 0; $j < count($column); $j++)
    {
    //Round 2: within column cycle through word(s)
    for($i = 0; $i < count($SearchWord); $i++)
    {
    $selectquery .= "$column[$j] LIKE '%" . $SearchWord[$i] . "%'";
    if ($i < count($SearchWord)-1)
    {
    $selectquery .= " OR ";
    }
    }
    if ($j < count($column)-1)
    {
    $selectquery .= " OR ";
    }
    }
    }

    Give it a try and tell me if this works.

    Peter

  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    6
    Rep Power
    0
    Nope, not working. I'm getting parse errors. I think this line is causing problems:

    $column = ("column_name1", "column_name2", "column_name3");
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    6
    Rep Power
    0
    Hey, I got it working. I forgot to copy 2 lines and you forgot a $ It works great, but I was wondering how would I modify it so that if I did a search for "big dog" and in column 1 was the word "big" and column 2 had "dog", it would return results like this, too?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    15
    I think it should return a result when BIG is in column 1 and DOG in column 2, because of the OR clause.
    The best thing to do (if you are familiar with SQL), is to first get the right SQL syntax and then build the PHP application to fill it.
    Just play around!

    Peter
  18. #10
  19. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    6
    Rep Power
    0
    Nope, it doesn't work if one word is in one column and the other is in another. Do you tink you know what's wrong? Thanks for the help so far though

Similar Threads

  1. Inserting elements of multiple arrays in database
    By jkoll in forum PHP Development
    Replies: 1
    Last Post: February 19th, 2004, 11:47 AM
  2. Replies: 0
    Last Post: October 31st, 2002, 11:14 PM
  3. Replies: 2
    Last Post: September 13th, 2002, 01:08 AM
  4. fields in database are empty
    By Searcher in forum PHP Development
    Replies: 2
    Last Post: April 9th, 2002, 04:43 PM
  5. A "smart" SQL query
    By sosed in forum MySQL Help
    Replies: 0
    Last Post: July 17th, 2000, 08:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo