Command for trigger

Hi All,

In my app I want to do a generic search (search in all the database).

I found the syntax for doing it here but I don’t know how to use it in my Controller, how to use this complex query?




   public function actionTrigger(){

      .

      .

      .

      DELIMITER $$

      #Script to loop through all tables using Information_Schema

      DROP PROCEDURE IF EXISTS get_table $$

      CREATE PROCEDURE get_table(in_search varchar(50))

      READS SQL DATA

      BEGIN

      DECLARE trunc_cmd VARCHAR(50);

      DECLARE search_string VARCHAR(250);

      DECLARE db,tbl,clmn CHAR(50);

      DECLARE done INT DEFAULT 0;

      DECLARE COUNTER INT;

      DECLARE table_cur CURSOR FOR

      SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'`

      REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN

      ('dsms');

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

      #Truncating table for refill the data for new search.

      PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";

      EXECUTE trunc_cmd ;

      OPEN table_cur;

      table_loop:LOOP

      FETCH table_cur INTO search_string,db,tbl,clmn;

      #Executing the search

      SET @search_string = search_string;

      #SELECT search_string;

      PREPARE search_string FROM @search_string;

      EXECUTE search_string;

      SET COUNTER = @CNT_VALUE;

      #SELECT COUNTER;

      IF COUNTER>0 THEN

      # Inserting required results from search to table

      INSERT INTO temp_details VALUES(db,tbl,clmn);

      END IF;

      IF done=1 THEN

      LEAVE table_loop;

      END IF;

      END LOOP;

      CLOSE table_cur;

      #Finally Show Results

      SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details;

      END $$

      DELIMITER ; - See more at: http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql#sthash.ZCoJGvSR.dpuf

      .

      .

      .

   }