Mysql query for searching a value, add weightage on the number of occurances and sort based on the weightage

Consider a table named 'data' which has columns named 'Name', and 'Content', We need to search these columns, if 'Name' colomn has a match it should have a weightage of '.08' and if 'Content' has a match it should have a weightage of '0.2' ,

Suppose 'Name' has one match and 'Content' has 3 match, then it should have a total weightage of (.08*1)+(.02*3) = .14

For that we can use the following query to get the result.

SELECT *
FROM (
SELECT * , (
(
length( `Content` ) - length( replace( LOWER(`Content`) , "SEARCHKEY", "" ) ) ) / length("SEARCHKEY" ) * .08
) + (
(
length( `Content` ) - length( replace( LOWER(`Content`) ,"SEARCHKEY", "" ) ) ) / length("SEARCHKEY" ) * .02
) AS weight
FROM data
)a
WHERE weight > 0
ORDER BY weight DESC
LIMIT 0,10

Creating a custom VtigerCRM Module

Step 1.
Download the module creation script from the VtigerCRM or copy it from below

name = 'Phpqa';
$module->save();

// Initialize all the tables required
$module->initTables();
// Initialize Webservice
$module->initWebservice();

// Add the module to the Menu (entry point from UI). Will display under Tools menu
$menu = Vtiger_Menu::getInstance('Tools');
$menu->addModule($module);

// Add the basic module block
$block1 = new Vtiger_Block();
$block1->label = 'LBL_PHPQA_INFORMATION';
$module->addBlock($block1);

// Add custom block (required to support Custom Fields)
$block2 = new Vtiger_Block();
$block2->label = 'LBL_CUSTOM_INFORMATION';
$module->addBlock($block2);

/** Create required fields and add to the block */
$field1 = new Vtiger_Field();
$field1->name = 'Sales';
$field1->label = 'Sales';
$field1->table = $module->basetable;
$field1->typeofdata = 'V~O';
$block1->addField($field1); /** Creates the field and adds to block */

// Set at-least one field to identifier of module record
$module->setEntityIdentifier($field1);

$field2 = new Vtiger_Field();
$field2->name = 'Name';
$field2->label = 'Name';
$field2->typeofdata = 'V~O';// Varchar~Optional
$block1->addField($field2); /** table and column are automatically set */

$field3 = new Vtiger_Field();
$field3->name = 'InvoiceType';
$field3->label= 'Invoice Type';
$field3->typeofdata = 'V~O'; // Date~Mandatory
$block1->addField($field3);  /** table, column, label, set to default values */

$field4 = new Vtiger_Field();
$field4->name = 'InvoiceID';
$field4->label= 'Invoice ID';
$field4->typeofdata = 'V~O';
$block1->addField($field4);

/** END */

// Create default custom filter (mandatory)
$filter1 = new Vtiger_Filter();
$filter1->name = 'All';
$filter1->isdefault = true;
$module->addFilter($filter1);

// Add fields to the filter created
$filter1->addField($field1)->addField($field2, 1)->addField($field5, 2);

// Create one more filter
$filter2 = new Vtiger_Filter();
$filter2->name = 'All2';
$module->addFilter($filter2);

// Add fields to the filter
$filter2->addField($field1);
$filter2->addField($field2, 1);

// Add rule to the filter field
$filter2->addRule($field1, 'CONTAINS', 'Test');

/** Associate other modules to this module */
//$module->setRelatedList(Vtiger_Module::getInstance('Accounts'), 'Accounts', Array('ADD','SELECT'));

/** Set sharing access of this module */
$module->setDefaultSharing('Private'); 

/** Enable and Disable available tools */
$module->enableTools(Array('Import', 'Export'));
$module->disableTools('Merge');

?>

Step 2
Save it in your vigercrm root folder. and run the script .
Step 3
Copy the desired version of sample vtigercrm module from the vtlib/ModuleDir and paste it into the /modules directory
Step 4
Rename the folder name into the desired name you have given in the module creation script. (Phpqa)
Step 5
Rename the ModuleFile.php, ModuleFileAjax.php, ModuleFile.js to your module name. Here the ModuleFile indicates your module name. So names are like Phpqa.php, PhpqaAjax.php and PhpqaFile.js
Step 6
Open the Phpqa.php and rename the class name into Phpqa. Change the all modulename occurance into your custom module name
Change the following

 
var $table_name = 'vtiger_phpqa';
var $table_index= 'phpqaid';

var $customFieldTable = Array('vtiger_phpqacf', 'phpqaid');

var $tab_name = Array('vtiger_crmentity', 'vtiger_phpqa', 'vtiger_phpqacf');

var $tab_name_index = Array(
 'vtiger_crmentity' => 'crmid',
 'vtiger_phpqa'   => 'phpqaid',
 'vtiger_phpqacf' => 'phpqaid');

Also the change the other listed variables in the page as per our need.
Step 7
Change the Language file variable based on the module name. Its located in /language folder of the module .

 

'Phpqa' => 'Phpqa',
'LBL_CUSTOM_INFORMATION' => 'Custom Information',
'LBL_PHPQABLOCK_INFORMATION' => 'Phpqa Block Information'

 
 

Step 8
You can see your module is listed in the vtigercrm admin console. Settings > Module Manager page under the custom module. Here we can disable/enable and export your module for future use. if you click on the export buttom, a Zip file will be downloaded as an installtion file. and we are reuse it in other vtiger applications.

Can we compare equality of two floats in php ?

The answer is NO. Its not encouraged in php programming. The reason is, its related to system confugurations. We can not trust these values, it may vary in different systems. Consider this example

if( 0.111 + 0.222 == 0.333 ){
 echo 'a and b are same';
}
else {
 echo 'a and b are not same';
} 

Here the value 0.333 is A literal but a float(calculated sum) using to compare it. So it may result some variations in the result. some times it will show the 'true' part, sometime it will show the 'false' part.
You can see its more explantions on php.net
here are the links

http://php.net/manual/en/language.operators.comparison.php 
http://in.php.net/float 

 To overcome this, we can use the precition checking

  function floatCheck($x,$y,$precision=0.0000001) 
    { 
        return ($x+$precision >= $y) && ($x-$precision <= $y); 
    }

floatCheck(1.0002541,1.0002540,$precision=0.0000002);