How To: multiple keyword query in multiple database fields

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

Hi Ed,

Can you give us an example of what you want?

Peter

Yes, here are a couple of examples:
* ProLiant 8500 Quickspecs
* Linux applications
* AlphaServer storage adapters
* multiple keyword query

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).]

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).]

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

Nope, not working. I'm getting parse errors. I think this line is causing problems:

$column = ("column_name1", "column_name2", "column_name3");

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?

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

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