Jump to content


Photo

How do you search databases?


  • Please log in to reply
3 replies to this topic

#1 onemind

onemind
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 24 November 2005 - 10:45 AM

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


#2 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 24 November 2005 - 10:25 PM

[!--quoteo(post=321689:date=Nov 24 2005, 05:45 AM:name=onemind)--][div class=\'quotetop\']QUOTE(onemind @ Nov 24 2005, 05:45 AM) View Post[/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
[/quote]

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%'
;

#3 slkhlaw

slkhlaw
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 25 November 2005 - 12:52 PM

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.


#4 onemind

onemind
  • Members
  • PipPip
  • Member
  • 28 posts

Posted 26 November 2005 - 02:22 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users