PHP Keyword Search

[wuz]

Hello,

I have just started PHP programming, and am stuck on keyword searching. I'm trying to create a basic search box that would allow a visitor to type multiple keywords, and have the system search in a specific database field for those keywords. I've done a similar thing in ColdFusion, in which I used the following code to loop through the products, attributes, and company for each keyword:

<cfloop list=&quot;#Form.Item#&quot; index=&quot;searchterm&quot; delimiters=&quot; &quot;>
AND (Items.Product LIKE '%#searchterm#%'
OR Items.Attributes LIKE '%#searchterm#%'
OR Companies.Company LIKE '%#searchterm#%')
</cfloop>

Does anyone know of an equivalent in PHP? I thought about using split(&quot; &quot;, $searchterm) to split the keywords into an array and searching through the array in a loop, but I'm not sure where to go from there. Any ideas, or a better suggestion?

Thanks greatly in advance for your assistance.

Susan

 

[rycamor]

Are you searching through a database, such as MySQL, or a text file?

If its a database (the correct way), then you implement the search in a very similar way to the ColdFusion Method above: you write an SQL query that simply searches the database for those terms using the LIKE operator or several other search operators. Its really a database question, not a programming question.

If you are searching a text file, then you could split the keywords into an array, but its really better to use a database, which is optimized for that sort of thing.

 

[wuz]

Thanks for the info.

Actually, I am using a database, and the above code is just part of my sql query in CF. But I didn't know the equivalent code format in PHP.

ie. instead of <cfloop>, would it be best to use a while statement in PHP? If so, how should I break up the search string if it has several keywords so that the system can loop through it? That's why I thought of splitting the search string into an array so that it could be searched in PHP. Problem is that PHP is a little new to me, so I'm not as comfortable with the syntax.

Thanks.

Susan

 

[rycamor]

You're making it more complicated than it has to be. Yes, you can select a whole recordset from a database, load it into a PHP arrary, then use PHP to search for keywords, but that is drastically inefficient.

You shouldn't think in terms of PHP loops or while statements until AFTER you have done all your filtering and searching at the database level. Let the database do the work it's optimized for. Not really knowing ColdFusion, I believe the PHP version of what you did in CF looks something like:

     $db = mysql_connect("localhost", "username", "password");


     $query = "SELECT * FROM Items,Companies WHERE Items.Product LIKE '%$searchterm%' OR Items.Attributes LIKE '%$searchterm%' OR Companies.Company LIKE '%$searchterm%'";

     echo &quot;Search results:<br><br>&quot;;

     $result = mysql_db_query(&quot;database_name&quot;, $query);
	while($r = mysql_fetch_array($result))
        {
        $fieldname1 = $r[&quot;fieldname1&quot;]
        $fieldname2 = $r[&quot;fieldname2&quot;]
        //etc....
         echo &quot;HTML text here $fieldname1 moretext $fieldname2&quot;;
        }

(You might have needed a JOIN somewhere above depending on your database structure)

 

[wuz]

Thanks for the info.

Unfortunately, I wanted to check for one or more keywords, so it wouldn't work until the keyword was split. I finally used the following:

$searchterm = split(&quot; &quot;, $keywords);
$countsearch = count($searchterm);

$viewquery = &quot;SELECT * FROM Items, Companies WHERE Active = 'Yes' AND (Companies.Company LIKE '%$searchterm[0]%' OR Items.Product LIKE '%$searchterm[0]%'&quot;;
for ($counter = 1; $counter < $countsearch; ++$counter)
{
$viewquery .= &quot;OR Companies.Company LIKE '%$searchterm[$counter]%' OR Items.Product LIKE '%$searchterm[$counter]%'&quot;;
}
$viewquery .= &quot GROUP BY Website ORDER BY Company&quot;;
$viewresult = mysql_query($viewquery);

Not the most efficient, I'm sure, but it works for now. Thanks a bunch for your help.

Susan