This is not the best method and should be used with care as it can crash db with large amounts of tables and data. Somethings may need to be modified to use with your specific setup however should get you close.
<?php
class DBSearch{
// DB Connection
protected $db;
// Name of the DB to search in
protected $db_name = 'my_db_name';
// Tables to exclude from search
protected $excluded_tables = array(
'TABLE_I_DONT_WANT_INCLUDED',
);
// Search String
protected $search_string = '';
// Table has column
protected $has_column = '';
// Set the result limit per query
protected $limit = 5;
public function __construct($db_conn) {
parent::__construct();
$this->db = $db_conn;
}
public function search(string $search_str, string $has_column, array $exclude_table){
$this->search_string = $search_str;
$this->has_column = $has_column;
$this->excluded_tables = $exclude_table;
if(!empty($this->has_column)){
$table_names = $this->get_table_with_column($this->has_column,$this->excluded_tables);
}else{
$table_names = $this->get_all_tables($this->excluded_tables);
}
$query_string = $this->generate_query_string($table_names, $this->search_string);
$results = array();
foreach($query_string as $k=>$v){
$query = $v.' LIMIT '.$this->limit;
$results[] = $this->db->query($query)->result();
}
return $results;
}
/**
* Returns the column names associated with the table
* provided by the $table param
*
* @param string $table
* @return array
*/
private function get_table_column_names($table){
$response = array();
$sql = 'SELECT COLUMN_NAME, TABLE_NAME
FROM information_schema.columns
WHERE table_schema = ?
AND table_name = ?
ORDER BY table_name, ordinal_position';
$param = array($this->db_name, $table);
$result = $this->db->query($sql, $param);
if($result->num_rows() >= 1){
foreach ($result->result() as $v){
$response[$table][] = $v->COLUMN_NAME;
}
}
return $response;
}
/**
* Returns a object contaning the table names that
* have columns that have the name provided in $column
*
* You can also pass in a string or an array of tables not to in clude in
* the result set using the $exclude_table param
*
* @param string $column
* @param array|string $exclude_table
* @return object|boolean
*/
private function get_table_with_column($column, $exclude_table=NULL){
$sql = 'SELECT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (COLUMN_NAME = ?
OR COLUMN_NAME LIKE ?)
AND table_schema = ? ';
if(NULL !== $exclude_table){
if(is_array($exclude_table)){
foreach($exclude_table as $v){
$sql .= ' AND TABLE_NAME != "'.strip_quotes($v).'"';
}
}
if(is_string($exclude_table)){
$sql .= ' AND TABLE_NAME != "'.strip_quotes($exclude_table).'"';
}
}
$sql .= ' GROUP BY TABLE_NAME ORDER BY TABLE_NAME ';
$query_param = array($column, '%'.$column.'%', $this->db_name);
$result = $this->db->query($sql, $query_param);
if($result->num_rows() >= 1){
return $result->result();
}
return false;
}
/**
* Returns an object contaning the table names.
*
* You can also pass in a string or an array of tables not to in clude in
* the result set using the $exclude_table param
*
* @param array|string $exclude_table
* @return object|boolean
*/
private function get_all_tables($exclude_table=NULL){
$sql = 'SELECT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = ? ';
if(NULL !== $exclude_table){
if(is_array($exclude_table)){
foreach($exclude_table as $v){
$sql .= ' AND TABLE_NAME != "'.strip_quotes($v).'"';
}
}
if(is_string($exclude_table)){
$sql .= ' AND TABLE_NAME != "'.strip_quotes($exclude_table).'"';
}
}
$sql .= ' ORDER BY TABLE_NAME';
$query_param = array($this->db_name);
$result = $this->db->query($sql, $query_param);
if($result->num_rows() >= 1){
return $result->result();
}
return false;
}
/**
* Generates a search string for each table
* provided $table_names array
*
* @param array $table_names
* @param string $search_string
* @return array[]
*/
private function generate_query_string($table_names, $search_string){
$search_split = explode(' ', $search_string);
$search_a = isset($search_split[0]) ? $search_split[0]:'';
$search_b = isset($search_split[1]) ? $search_split[1]:'';
$queries = array();
if(is_array($table_names)){
foreach ($table_names as $v){
$query_string = 'SELECT * FROM '.$v->TABLE_NAME.' WHERE (';
foreach ($this->get_table_column_names($v->TABLE_NAME)[$v->TABLE_NAME] as $c){
$query_string .= '`'.$c.'` LIKE "%'.$search_string.'%" OR';
if(!empty($search_a)){
$query_string .= '`'.$c.'` LIKE "%'.$search_a.'%" OR';
}
if(!empty($search_b)){
$query_string .= '`'.$c.'` LIKE "%'.$search_b.'%" OR';
}
}
// Remoe Last OR
$query_string = substr($query_string, 0, strlen($query_string)-3). ')';
$queries[$v->TABLE_NAME] = $query_string;
}
}
return $queries;
}
}
// USEAGE
$search = new DBSearch($db_conn);
$exclude_table = array(
'tables',
'i_dont',
'want_searched'
);
$search->search('Something to search for', 'has_this_column', $exclude_table);
This essentials is a query builder for database tables and then runs the query on each table/column found in the DB. Maybe it will be helpful. Enjoy!
