MySQL Search Entire Database For Value

Abstract

The need to search an entire database for a value comes up suprisingly often as a question here at PHPRO. Folks give various reasons from "I am new to the database" to "this database design is insane". Whatever the reason is you need to do this, the script below will be able to get the job done for you. However, there are a few caveats that need to be understood.

Speed

Searching the whole database for a value is a slow process. The larger the database, the longer the search will take, and the more resources will be consumed. To alleviate some of this pain, the search can be refined in such a way that not ALL the fields need to be searched.

By specifying which data type fields need to be search, the job of searching the entire database is reduced to searching only those fields which are likely to contain the search value. Eg: A field of type INT or TIMESTAMP is unlikely to contain the text 'big kev'. So only the fields which are capable of holding the search value need to be searched. An example might be fields with a datatype of CHAR, VARCHAR or TEXT. There are many other string data types and a file list can be seen in Mysql Data Types section of the MySQL Manual.




<?php
// name of the database to connect to
$db_name = 'my_database';

// name of the host to connect to
$db_host = '127.0.0.1';

// username to log into MySQL
$db_user = 'root';

// password for MySQL
$db_pass = 'SuperSecret';

// data source name
$dsn = "mysql:host=$db_host;dbname=$db_name";

// connect to database
$db = new PDO( $dsn, $db_user, $db_pass );

// use exceptions to trap errors
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// a new search instance
$search = new dbSearch( $db );

// name of database to search
$search->db_name    = 'my_database';

// search for exact string or if search is anywhere
$search->operator       = 'like';

// the data types to search
$search->data_types    = ['int', 'text', 'char', 'blob', 'date', 'time', 'enum' ];

// the value of the search string
$search->search_value    = 'Big Kev';

// add errors to search results
$search->display_errors    = false;

// run the search
$search->doSearch();

// show the results
echo $search;

class dbSearch {

    /**
     * @var    PDO object    $db
     */
    public $db;

    /**
     * @var    string    $db_name
     */
    public $db_name;

    /**
     * @var    string    $operator
     */
    public $operator = 'equals';

    /**
     * @var    array    $data_types
     */
    public $data_types = [];

    /**
     * @var    string    $delimiter
     */
    public $delimiter = ',';

    /**
     * @var    string    $enclosure
     */
    public $enclosure = "'";

    /**
     * @var    array    $results
     */
    public $results = [];

    /**
     * @var    string    $display_errors
     */
    public $display_errors = false;

    /**
     * 
     * Constructor, duh!
     *
     * @access    public
     *
     */
    public function __construct( PDO $db )
    {
        $this->db = $db;
    }

    public function doSearch( $search_value = '' )
    {
        $search_value = isset( $this->search_value ) ? $this->search_value : $search_value;
        $likes = $this->likes();
        $ret = [];

        $sql = "SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.columns 
            WHERE table_schema ='$this->db_name' AND DATA_TYPE $likes 
            ORDER BY table_name, ordinal_position";
        $res = $this->db->query( $sql );

        foreach( $res as $r )
        {
            $table_name = $r['TABLE_NAME'];
            $column_name = $r['COLUMN_NAME'];

            // set the type of search operator
            if( $this->operator == 'equals' )
            {
                $operator = "= '$search_value'";
            }

            if( $this->operator == 'like' )
            {
                $operator = "LIKE '%$search_value%'";
            }

            $sql = "SELECT `$column_name` FROM `$table_name` WHERE `$column_name` " . $operator;
            try
            {
                $stmt = $this->db->prepare( $sql );
                // $stmt->bindParam( ':search_value', $search_value, PDO::PARAM_STR);
                $stmt->execute();
            }
            catch( Exception $e )
            {
                if( $this->display_errors == true )
                {
                    $this->results[] = $e->getMessage();
                }
                continue;
            }

            $res = $stmt->fetchAll( PDO::FETCH_ASSOC );
            if( $res )
            {
                $this->results[] = "Found $search_value IN Table $table_name ($column_name)";
            }
            else
            {
                if( $this->display_errors == true )
                {
                    $this->results[] = "Did not find $search_value IN Table $table_name ($column_name)";
                }
            }
        }
    }


    /**
     *
     * Return a string representation of the results
     *
     * @access    public
     *
     */
    public function __toString()
    {
        $ret = '';
        foreach( $this->results as $r )
        {
            $ret .= "$r\n";
        }
        return $ret;
    }

    /**
     *
     * Create the LIKE statements for the query
     * @access    public
     * @return    string
     *
     */
    public function likes()
    {
        $i = 0;
        $ret = '';
        foreach( $this->data_types as $type )
        {
            if( $i == 0 )
            {
                $ret .= " LIKE '%$type%'";
            }
            else
            {
                $ret .= " OR DATA_TYPE LIKE '%$type%'";
            }

            $i++;
        }
        return $ret;
    }

} // end of class

?>