CODEWITHSUNDEEP
mysqlsqlsearch
VoltzRoad
VoltzRoad

Reputation: 495

Search a whole table in mySQL for a string

I'm trying to search a whole table in mySQL for a string.

I want to search all fields and all entrees of a table, returning each full entry that contains the specified text.

I can't figure out how to search multiple fields easily; here are the details:

The table is "clients". It has about 30 fields and 800 entries, too much to show all at once in a browser. I would like to search for a name (i.e. "Mary"), but it could be in the shipping_name field or the billing_name field, or the email field, etc.

I would like to search all fields for any entries that contain the string "Mary". This is what I think should work but doesn't:

SELECT * FROM `clients` IN 'Mary'

Upvotes: 32

Views: 119564

Answers (10)

Kyle Coots
Kyle Coots

Reputation: 2131

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!

Upvotes: 0

user10212105
user10212105

Reputation:

One can take an export of the table in an excel sheet & find the string in the excel file itself.

Upvotes: 0

user10899318
user10899318

Reputation:

Try this code,

SELECT 
       * 
FROM 
       `customers` 
WHERE 
       (
          CONVERT 
               (`customer_code` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`customer_name` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`email_id` USING utf8mb4) LIKE '%Mary%' 
          OR 
          CONVERT(`address1` USING utf8mb4) LIKE '%Mary%' 
          OR
          CONVERT(`report_sorting` USING utf8mb4) LIKE '%Mary%'
       )

This is help to solve your problem mysql version 5.7.21

Upvotes: 1

user7957410
user7957410

Reputation: 65

for specific requirement the following will work for search:

select * from table_name where (column_name1='%var1%' or column_name2='var2' or column_name='%var3%') and column_name='var';

if you want to query for searching data from the database this will work perfectly.

Upvotes: 0

Artem Pianykh
Artem Pianykh

Reputation: 1181

Try something like this:

SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"

You may want to see SQL docs for additional information on string operators and regular expressions.

Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '') instead of just field_i

Case sensitivity: You can use case insensitive collation or something like this:

... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")

Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECT operator syntax (WHERE section, to be precise).

Upvotes: 30

gonzo
gonzo

Reputation: 526

If you are just looking for some text and don't need a result set for programming purposes, you could install HeidiSQL for free (I'm using v9.2.0.4947).

Right click any database or table and select "Find text on server".

All the matches are shown in a separate tab for each table - very nice.

Frighteningly useful and saved me hours. Forget messing about with lengthy queries!!

Upvotes: 5

Soner
Soner

Reputation: 126

In addition to pattern matching with 'like' keyword. You can also perform search by using fulltext feature as below;

SELECT * FROM clients WHERE MATCH (shipping_name, billing_name, email) AGAINST ('mary')

Upvotes: 7

James Smith
James Smith

Reputation: 538

If you're using Sublime, you can easily generate hundreds or thousands of lines using Text Pastry in conjunction with multiple line selection and Emmet.

So in my case I set the document type to html, then typed div*249, hit tab and Emmet creates 249 empty divs. Then using multiple selection I typed col_id_ in each one and triggered Text Pastry to insert an incremental id number. Then with multiple selection again you can delete the div markup and replace it with the MySQL syntax.

Upvotes: 0

Arpan Dubey
Arpan Dubey

Reputation: 94

A PHP Based Solution for search entire table ! Search string is $string . This is generic and will work with all the tables with any number of fields

$sql="SELECT * from client_wireless";
$sql_query=mysql_query($sql);
$logicStr="WHERE ";
$count=mysql_num_fields($sql_query);
for($i=0 ; $i < mysql_num_fields($sql_query) ; $i++){
 if($i == ($count-1) )
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' ";
else
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' OR ";
}
// start the search in all the fields and when a match is found, go on printing it .
$sql="SELECT * from client_wireless ".$logicStr;
//echo $sql;
$query=mysql_query($sql);

Upvotes: 4

Francis P
Francis P

Reputation: 13665

Identify all the fields that could be related to your search and then use a query like:

SELECT * FROM clients
WHERE field1 LIKE '%Mary%'
   OR field2 LIKE '%Mary%'
   OR field3 LIKE '%Mary%'
   OR field4 LIKE '%Mary%'
   ....
   (do that for each field you want to check)

Using LIKE '%Mary%' instead of = 'Mary' will look for the fields that contains someCaracters + 'Mary' + someCaracters.

Upvotes: 15

Related Questions