How do you search databases?

[onemind]

Hi,

 

I have just about finished my site and have many tables filled with users, articles ect and am now trying to implement a search feature.

 

Basicaly, i have a text box that the user enters keywords they want to search for then they click search.

 

Is it just a matter of parsing the keywords then performing several select queries on the database or is there a proper way to search mysql databases?

 

Any tips would be great.

 

Thanks

 

[AV1611]

[!--quoteo(post=321689:date=Nov 24 2005, 05:45 AM:name=onemind)--][div class=\'quotetop\']QUOTE(onemind @ Nov 24 2005, 05:45 AM) 321689[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Hi,

 

I have just about finished my site and have many tables filled with users, articles ect and am now trying to implement a search feature.

 

Basicaly, i have a text box that the user enters keywords they want to search for then they click search.

 

Is it just a matter of parsing the keywords then performing several select queries on the database or is there a proper way to search mysql databases?

 

Any tips would be great.

 

Thanks

 

This is maybe overly simplistic, but,

Select * from table where

field1 like '%keyword1%' or

field1 like '%keyword2%' or

field1 like '%keyword3%' or

field1 like '%keyword4%' or

field1 like '%keyword5%' or

field1 like '%keyword6%'

;

[slkhlaw]

SELECT Title, Body, MATCH (Title, Body) AGAINST ($SearchString) AS Relevancy FROM articles WHERE

MATCH (Title, Body) AGAINST ($SearchString) ORDER BY Relevancy DESC LIMIT 100;

 

Add more conditions if you need to before the "ORDER BY", e.g. Author='Peter'

 

You may have to change your database engine if your current engine does not support Full Text Search.

Also be sure to index your table for full text search and use LIMIT command.

 

When your database grew to a few million records, the retrieval time for each search between using index+limit and not using index+limit is 1 sec vs 20 min.

 

[onemind]

thanks guys,

 

I am having some trouble using the right sql command.

 

This is what i have so far:

 

I send the search string to my script

 

$words = $_POST[words];

 

Then i strip non letter and number characters, remove whitespace and seperate each word into an array.

 

$letters = ereg_replace("[^A-Za-z0-9 ]", "", $words); //removes non letters and numbers

$keywords = explode(" ", $letters); //seperates into words

 

foreach($keywords as $key => $value) //removes blanks from array

{

if($value == "" || $value == " " || is_null($value))

{

unset($keywords[$key]);

}

}

 

This generates a keyword string to use in sql

 

for($i=0; $i<count($keywords); $i++)

{

if($i != (count($keywords)-1))

$keywords_s .= "'%" . $keywords[$i] . "%'" . " || ";

else

$keywords_s .= "'%" . $keywords[$i] . "%'" ;

 

}

 

Which creates something like so: '%Search%' || '%for%' || '%this%'

 

I then plug this into my query like so: $get_username = "SELECT id, dname FROM users WHERE dname LIKE $keywords_s";

 

And it works fine.

 

The trouble i am having now is, i cant use multiple WHERE clauses. What i would like to do is the following:

 

$get_username = "SELECT id, dname FROM users WHERE dname || location || lname LIKE $keywords_s";

 

But this is invalid sql.

 

Does anyone know a way to compare my keywords with multiple columns?

 

Thanks again