Search any word in entire tables of mysql database

Search any word in entire tables of mysql database

Suppose there are lot of tables in the database. and you want to search a word or sentence in the tables.
For this you need to look into each table, which having varchar or text or any character based type fields.
It is very lazy, tough and time killing task to look into each table for those fields manually and then generate it's sql manually.

I have recently worked on this type of task,so I have created this PHP code. It will show the sql in where it will find the search term. You can convert it in any other sql for example sql server, oracle etc.



 <?php

class DBUtil {
    public $utilMySqlServerIP;
    public $utilDB ;
    public $utilUser;
    public $utilPass;
    public $link,$isDBConnect;
    public function __construct() {
        $this->utilMySqlServerIP = "localhost"; $this->utilUser = "root"; $this->utilPass = "";
        $this->utilDB = "realated_se4"; $this->utilAns=false;
        try {
            $this->link = mysql_connect($this->utilMySqlServerIP, $this->utilUser, $this->utilPass);
            if (!is_resource($this->link)) { $this->isDBConnect=false; }
            else { $this->isDBConnect=true; }
            mysql_select_db($this->utilDB,$this->link);
        } catch(Exception $ee) { $this->link=false; }
    }
  
    public function showTableNames() {
        try    {
            $result = mysql_query("show tables", $this->link);
            if(mysql_num_rows($result)>0) { return $result; } else { return false; }  
        } catch(Exception $ee) { return (false); }
    }
  
    public function showTableColumn($tblName) {
        try    {
            $result = mysql_query("SHOW COLUMNS FROM ".$tblName, $this->link);
            if(mysql_num_rows($result)>0) { return $result; } else { return false; }  
        } catch(Exception $ee) { return (false); }
    }
    public function exeQuery($query, $isPrint=0) {
        try    {
            if($isPrint==1) { echo $query; }
            $result = mysql_query($query, $this->link);
            if(mysql_num_rows($result)>0) { return $result; } else { return false; }  
        } catch(Exception $ee){ return (false); }
    }
    public function __destruct() { }
}

////////////////////////////////////////////////////////
$db=new DBUtil();
$tbRs=$db->showTableNames();
$term="search term";
$number=0;
$tmpSql=" select [COLS] from [TABLENAME] where [WHERES]";
while($tbName = mysql_fetch_array($tbRs, MYSQL_BOTH)) {
    $colRs=$db->showTableColumn($tbName[0]);
    $startSql=$tmpSql; $fnd=0; $comma=",";     $cols=""; $where="";
    while($col = mysql_fetch_array($colRs,MYSQL_BOTH)) {
         $vFound=stripos($col[1], "varchar");
        $tFound=stripos($col[1], "text");
        $charFound=stripos($col[1], "char");
        $longFound=stripos($col[1], "longtext");
        $mediumFound=stripos($col[1], "mediumtext");
        $tinyFound=stripos($col[1], "tinytext");
      
        if($vFound!== false || $tFound!== false || $charFound!== false || $longFound!== false || $mediumFound!== false || $tinyFound!== false ) {
            if($fnd==0) {
                $fnd=1;      $cols.="`".$col[0]."`";  $where.= "`".$col[0]."`"." like '%".$term."%' ";
            } else {
                $cols.=","."`".$col[0]."`";      $where.=" or "."`". $col[0] ."`"." like '%".$term."%' ";
            }
        }
    }
    $startSql=str_replace("[TABLENAME]", $tbName[0], str_replace("[WHERES]", $where, str_replace("[COLS]", $cols, $startSql)));
    if(stripos($startSql,"like '%")!== false) {
        $rs=$db->exeQuery($startSql, 0);
        if($rs) { echo "<p></p>[".(++$number)."]== ".$startSql."<hr>";    }
    }
}
?>