simple Search Function

[bccamp]

I'm trying to write a search routine for my DB. I have a field, Title, that contains multiple pipe delimeted statements for each row of data. I have a field, KeyWord, that contains the 'key words' from Title, Description, and Header. I can search using a single query, explode the pipe delimeted statements, and extract the statement that contains the single term I'm looking for:

$query="SELECT Title FROM company WHERE KeyWord LIKE '%$key%'";
$result=mysql_result($query);
...assign all values to a string
...loop the results
$Title2=(explode("|",$Title));
sort($Title2);
$cnt=count($Title2);
for ($a=0;$cnt>$a;$a++){
  $Title3=stristr($Title2[$a],$key);
  if ($Title3!=NULL){
    echo "$Title2[$a]"
  }
}

Now, after all that, what would be the best way to attack using multiple search terms? My KeyWord field contains more than just the data from the Title field. Using

...WHERE KeyWord LIKE '%$key1%' AND KeyWord LIKE '%$key2%'";

would not work correctly. Is there a "best" place to extract and compare the results from 2, 3, or 4 search terms? Is there a way to dynamically search 2,3,or 4 terms using the $query routine? I thought about writing the Search Results from the first KeyWord to a file, then using a loop routine to extract results from that based on each keyword, but that seems a little excessive. Better Way?

 

[sleipnir214]

I would first recommend a change in database schema.

Any time you have a single record which can have multiple associated values (as you appear to have in your columns that contain "multiple pipe-delimited statements" and "'key words'"), then that data should be in a related table.

Thus, you don't have to expand keywords, you just look for them in the related table. Also, you don't have to use the "like" operator, as you'll be searching for an entire indexable field.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[bccamp]

That would work great, but the data that is there is from a web spider. The pipe delimited statements are Title Pages and Meta data from the pages in question... And there are A LOT of them. I've already written the routine to parse the grose data into unduplicated ones and write them to the database under the corresponding Company it represents. It would be ideal to have them separated into categories and saved under different tables, but my spider doesn't have that kind of power. I guess I'm going the more difficult approach to keep from creating a much more difficult problem to tackle, like separating the statements into categories. Thanks for the input.

 

[sleipnir214]

I'm not sure my proposal would take that much extra power in your spider. Simply create multiple records across related tables instead of concatenating data.

Generally speaking, normalizing your database will make it operate faster and with less code.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Kozusnik]

Although I agree with sleipnir214, you could use another query with the statement "WHERE KeyWord IN $Title2".

Mark