another search function help 1

[deecee]

I have a simple search that goes through a mysql dbase and browses a keyword that clients put in and then spits out items that match that keyword, they problem is for example a client types in

line -- results 1 answer
phone -- results 2 answer
linephone -- results 1 answer
line phone -- restults no answer

how can i make it so that the search breaks each word into seperate searches then combines them for the results. Is this a complicated task?

[Hammer]
Nike Failed Slogans -- "Just Don't Do It!"

 

[sleipnir214]

Create an SQL query of the form:

SELECT * FROM thetable WHERE columname like &quot;%<firstword>%&quot; OR columnname like &quot;%<secondword>%&quot;



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[deecee]

but its all in one box....when i pass the variable its passed as 'variable' your technique would require 2 boxes right?

[Hammer]
Nike Failed Slogans -- &quot;Just Don't Do It!&quot;

 

[sleipnir214]

No....

That's why PHP provides functions like split(). You can split a single input into an array on an arbitrary character string, like a space. You can then concatenate those array elements together with enough other strings to create the SQL query I suggested.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[deecee]

man i have so much to learn about php...damn econ degree!

[Hammer]
Nike Failed Slogans -- &quot;Just Don't Do It!&quot;

 

[jimoblak]

You might also try the MATCH (full text search) function of newer MySQL databases.

http://www.mysql.com/doc/en/Fulltext_Search.html

 

[deecee]

here is my current sql statement

mysql_select_db($database_connCorelist, $connCorelist);
$query_corelist = sprintf("SELECT * FROM products WHERE description1 LIKE '%%%s%%' OR description2 LIKE '%%%s%%'  OR description3 LIKE '%%%s%%'  OR description4 LIKE '%%%s%%'  OR description5 LIKE '%%%s%%'  OR description6 LIKE '%%%s%%'", $colname_corelist,$colname_corelist,$colname_corelist,$colname_corelist,$colname_corelist,$colname_corelist);
$query_limit_corelist = sprintf("%s LIMIT %d, %d", $query_corelist, $startRow_corelist, $maxRows_corelist);
$corelist = mysql_query($query_limit_corelist, $connCorelist) or die(mysql_error());
$row_corelist = mysql_fetch_assoc($corelist);

how does match work into this one...also when it comes to split do i do that before i run my sql statement or after?

[Hammer]
Nike Failed Slogans -- &quot;Just Don't Do It!&quot;

 

[jimoblak]

Following is what I used on my site. It has been a while ago since I did this and a little late in the day so I cannot remember all of the logic behind it. At second glance, I can see it may need more work. Suggestions are welcomed...

//IF THE SEARCH TERM IS ONE SHORT WORD, CLEAN UP PLURAL SEARCH TERM AND DO A BASIC QUERY (MATCH DOES NOT WORK WELL ON SHORT TERMS) 
$length=strlen($_GET[searchterm]);
if ($length<=6) {
$smallsearch=1;

if ((substr($_GET[searchterm],-2,2))=='es') {
$length=($length-2);
$mysearchterm=substr($_GET[searchterm],0,$length);
} elseif ((substr($_GET[searchterm],-2,2))=='ES')
{
$length=($length-2);
$mysearchterm=substr($_GET[searchterm],0,$length);
} elseif ((substr($_GET[searchterm],-1,1))=='s')
{
$length=($length-1);
$mysearchterm=substr($_GET[searchterm],0,$length);
} elseif ((substr($_GET[searchterm],-1,1))=='S')
{
$length=($length-1);
$mysearchterm=substr($_GET[searchterm],0,$length);
} else {
$mysearchterm=$_GET[searchterm];
}


} else {
$mysearchterm=$_GET[searchterm];
}
//END CLEAN UP SEARCH TERM




$Link = mysql_connect ($Host, $User, $Password);

if ($smallsearch=='1') {
$Query = &quot;SELECT * FROM SiteContents WHERE ((Document LIKE '%$mysearchterm%') OR (Subhead LIKE '%$mysearchterm%') OR (PageTitle LIKE '%$mysearchterm%') OR (PageSubtitle LIKE '%$mysearchterm%') OR (Summary LIKE '%$mysearchterm%') OR (Keywords LIKE '%$mysearchterm%')) ORDER BY PageTitle ASC&quot;;
} else {
$Query = &quot;SELECT *, MATCH (Document,Subhead,PageTitle,PageSubtitle,Summary,Details,Keywords) AGAINST ('$mysearchterm') AS SCORE FROM SiteContents WHERE MATCH (Document,Subhead,PageTitle,PageSubtitle,Summary,Details,Keywords) AGAINST ('$mysearchterm')&quot;;
}