How do you search databases? - mysql

As in a search function for my website

[one_mind]

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

[wheelie34]

Hi one_mind

the easiest way is create a form in html to pass the users data to a script that will handle it, for example

<form name="form1" method="get" action="searchresults.php">
<input name="question" type="text" id="question" size="25" maxlength="50">
<input type="submit" name="Submit" value="Find answer"></form>

to handle the query create a page called searchresults.php and add the foillowing to it

$dblink=mysql_connect($dbhost, $dbuser, $dbpass)or die("System down!");
mysql_select_db($dbbase, $dblink)or die("Database down!");
$sql="select one_minds_data from his_table where HIS_DATA='$question'";
$result = mysql_query($sql, $dblink) or die("System down");

$question is posted by the form and used to locate data to suit, you can create an array for output or simply print the result to screen, depending how many tables you want results from.

That should get you started

[robsynnott]

That will expose you to SQL injection issues, though.

[physics]

Since you just want a site search you could filter out everything but word and space characters from $question, this would make it much more secure.

[one_mind]

Thanks guys,

Maybe it would help if i show what i have done 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

[arran]

one_mind,

Why not use MySQL's Fulltext Search [dev.mysql.com]?

It's easy to setup and the results aren't too bad.

arran.