Number of occurences in search!

[SLauren]

Hi,

I am trying to find the records based on a search string which can be anything and can match any of the varchar fields in the table. Right now i am getting those records by using the combinations of "LIKE" and "OR" operators.But as per the requirements i also need to show the number of occurences of the search string that could match different varchar fields in a record?? Can anyone please suggest me how can i prepare this query??

Thanks,
Lauren

[daniel2335]

Number of occurence of the string or number of records the string occurs on? Post your current query?

For number of record mysql_num_rows( $result)

If its counting the number of occurences of the string in the fields of all records. You could loop through the records it detected the string in and do a string level search.

Hope I understood the question correctly. :D

[SLauren]

Below is the query:

SELECT DISTINCT Master_A.ID,Mas ter_A.Name,Mast er_A.Descriptio n FROM Master_A INNER JOIN Details_A ON Master_A.ID = Details_A.A_ID WHERE Master_A.Status = 1 AND Details_A.Statu s = 1 AND (Master_A.Name LIKE '%pattern%' OR Master_A.Descri ption LIKE '%pattern%' OR Details_A.Code LIKE '%pattern%');

The above query is returning back all those records based on the conditions and the search pattern, which is performed on all the probable varchar fields contained by the tables. As per the requirements i need to show the number of occurences at record level, i.e. ,if the search string is contained by Master_A.Name,M aster_A.Descrip tion for a record, then the number of occurences should be equal to 2 for that record.

Thanks,
Lauren

[daniel2335]

Whats your server side programming language?

Im affraid Im only experienced in PHP and would do it like this...

$pattern = "cheese";
 
$query = "SELECT DISTINCT Master_A.ID,Master_A.Name,Master_A.Description FROM Master_A 
INNER JOIN Details_A ON Master_A.ID = Details_A.A_ID 
WHERE Master_A.Status = 1 AND Details_A.Status = 1 AND 
(Master_A.Name LIKE '%pattern%' OR Master_A.Description LIKE '$pattern' OR Details_A.Code LIKE '$pattern')";
 
$result = mysql_query($query);
 
$occurences = 0;
while($row = mysql_fetch_assoc($result))
{
   $occurences += substr_count($row['Name'],$pattern);
   $occurences += substr_count($row['Description'],$pattern);
}
 
echo $occurences;

Im sure this can be done in any server side code.