For Dynamic find_in_set with JOIN Query

    function dynamic_find_in_set($rtoLocations='2,1,1327') {
        $rtoLocations = explode(',', $rtoLocations);
        foreach ($rtoLocations as $row) {
                $listOfIds .= $row.',';
            }
        $rtoLocationStr = rtrim($listOfIds,',');
        $tablename = 'rto_tb_leads';
        $selectedFields = 'lead_id,bname,bcontact,sname,scontact,veh_reg_no,case_category,follow_up_date_time,ofc.rto_office_location as rto_location';
        $matchColumnName = 'rto_tb_leads.rto_location';
        $details = $this->db->query("SELECT $selectedFields from $tablename LEFT JOIN rto_office as ofc ON ofc.id = rto_tb_leads.rto_location where lead_status='2' AND mfcw_exe_status='0' AND FIND_IN_SET($matchColumnName,'".$rtoLocationStr."')")->result_array();
        if( is_array($details) && count($details) > 0 ) {
            return $details;
                 }
            return false;
    }

For Dynamic find_in_set and CREATE INDEX and Full Index Search in MYSQL and event_scheduler

foreach($rechargePlanDetails->result() as $row){
$mainCatIds .= $row->main_cat_id.',';
}
$listOfIds = rtrim($mainCatIds,',');
$tableName = SUB_CATEGORY;
$matchColumnName = 'main_cat_id';
$selectedFields = 'sub_cat_name,value,image';

$extraSubCatDetails = $this->apis_model->dynamic_find_in_set($tableName,$selectedFields,$matchColumnName,$listOfIds);
     
        // for model
        public function dynamic_find_in_set($tableName='',$selectedFields='',$matchColumnName='',$listOfIds=''){
$Query = "select $selectedFields from ".$tableName." where FIND_IN_SET($matchColumnName,'".$listOfIds."')";
$resultVal=$this->ExecuteQuery($Query);
return $resultVal;

}
*************************************************************

Syntax:
CREATE INDEX index_name
ON table_name (column_name, ...);
Ex:
CREATE INDEX HotelName ON activepropertylist (Name, Address1)
***********
How do I check if an index exists on a table field in MySQL?
SHOW INDEX FROM [tablename]

DROP INDEX index_name ON table_name;
*************************************************




For full text index search:


Full Index Search in MYSQL

Introduction to MySQL Full-Text Search:
* MySQL supports text searching by using the LIKE statement and regular expression. However, when the text column is large and the number of rows in a table is increased, using these methods has some limitations.Because of these limitations, MySQL extended a very nice feature so-called full-text search. Technically, MySQL creates an index from the words of the enabled full-text search column and performs searches on this index. MySQL uses a sophisticated algorithm to determine the rows matched against the search query.

Defining FULLTEXT index for existing tables:
* ALTER TABLE  table_name ADD FULLTEXT(column_name1, column_name2,…)
     after that to check whether index is created or not the syntax is " show index from table_name"
   
Removing full-text search columns:
    ALTER TABLE offices DROP INDEX address
 
MySQL Boolean Full-Text Searches:
    SELECT productName, productline FROM products WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE )
 
    totally 3 methods are there among theme i am using MySQL Boolean Full-Text Searches
    1.MySQL Natural Language Search
    2.MySQL Boolean Full-Text Searches
    3.Using MySQL Query Expansion
 
 
    <?php
$HotelName=array();
$Address=array();
$RegionNameLong=array();
$b = str_replace(" "," +",$searchTerm);
/* $resultSet = $this->db->query("select s.RegionNameLong as HotelName, Null as Address from rs_parentregionlist s where MATCH(s.RegionNameLong) AGAINST('+".$b."'IN BOOLEAN MODE) union select a.Name as HotelName, a.Address1 as Address from rs_activepropertylist a where MATCH(a.Name) AGAINST('+".$b."'IN BOOLEAN MODE) OR MATCH(a.Address1) AGAINST('+".$b."'IN BOOLEAN MODE) LIMIT 10");
foreach($resultSet->result() as $row){
$HotelName[] = $row->HotelName;
$Address[] = $row->Address;
}
$datas = array_merge($HotelName,$Address);
$aa= array_unique($datas);
$uniquesValues = array_values($aa);
echo json_encode($uniquesValues);*/

$resultSet1 = $this->db->query("select Name from rs_activepropertylist where MATCH(Name) AGAINST('+".$b."'IN BOOLEAN MODE) LIMIT 50");
$resultSet2 = $this->db->query("select Address1 from rs_activepropertylist where MATCH(Address1) AGAINST('+".$b."'IN BOOLEAN MODE) LIMIT 50");
$resultSet3 = $this->db->query("select RegionNameLong from rs_parentregionlist where MATCH(RegionNameLong) AGAINST('+".$b."'IN BOOLEAN MODE) LIMIT 50");

foreach($resultSet1->result() as $row){
$HotelName[] = $row->Name;
  }
foreach($resultSet2->result() as $row){
$Address[] = $row->Address1;
  }
foreach($resultSet3->result() as $row){
$RegionNameLong[] = $row->RegionNameLong;
  }
$datas = array_merge($HotelName,$Address,$RegionNameLong);
$aa= array_unique($datas);
$data = array_values($aa);
echo json_encode($data);
?>
 
*****************************************************
For event_scheduler:
<?php

include('db.php');
echo mysql_query("SET GLOBAL event_scheduler = ON;");
//echo mysql_query("CREATE EVENT test_event_03 ON SCHEDULE EVERY 10 SECOND STARTS CURRENT_TIMESTAMP DO TRUNCATE trending");
echo mysql_query("CREATE EVENT test_event1 ON SCHEDULE EVERY 1 MONTH STARTS CURRENT_TIMESTAMP DO TRUNCATE trending");
//echo mysql_query("CREATE EVENT test_event_03 ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
//DO INSERT INTO messages(message,created_at)VALUES('Test MySQL recurring Event',NOW());");
echo "Executed";
die;
?>
****************************************