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,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%');

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,Master_A.Description 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...

Expand|Select|Wrap|Line Numbers

 $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.

Number of occurences in search!

re: Number of occurences in search!

re: Number of occurences in search!

re: Number of occurences in search!

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about