How to search record "without knowing tables name" in database using php

Question

I'm trying to make a PHP search form that lets you search for something without knowing No tables name, after search, it compares to the record with "search text" in all over database ?

Answer 1

You want to search in every fields of every tables ?

What you can try is to get all tables :

SHOW TABLES FROM your-database;

Then for each table you can get fields :

SHOW COLUMNS FROM your-table

---

Example :

$array = array();
$getTables = mysqli($link, "SHOW TABLES FROM your-database");

while ($t = mysqli_fetch_row($getTables))
    $array[$t[0]] = array();

foreach( $array as $tableName => $fields )
{
    $getColumns = mysqli($link, "SHOW COLUMNS FROM ".$tableName);

    while ($c = mysqli_fetch_assoc($getColumns))
        $array[$tableName][] = $c['Field'];
}

var_dump($array); // will output tables and each fields;

Now you have tables/fields so you can build your query to make your search.

Answer 2

Created Search Function For All tables and all columns

function searchAll($searchText = "", $printLogs = false)
{
    //if you are executing this function in your terminal
    if($printLogs)
    {
        echo "\033[34m  \n \n process started ". date("Y-M-D h:s:i"). "\033[0m \n ";
    }

    // $this->db get you database instance
    $tables = $this->db->list_tables();

    // Loop through each table
    foreach ($tables as $table)
    {

        if($printLogs)
        {
             echo "\033[92m \n \n process table  ". $table . " \033[0m";
        }

        $fields = $this->db->list_fields($table);

        // $searchText this is your search text ;

        foreach ($fields as $field)
        {

            $this->db->select('*');
            $this->db->from($table);
            $this->db->like($table.'.'.$field,$searchText);
    
            $query = $this->db->get();
            // echo $this->db->last_query(); die;
            $tmpResult = $query->result_array();
        
            if(!empty($tmpResult)){
                foreach($tmpResult as $values){
                    print_r($values);
                }
            }
            
            
        }
        
    }

}