MYSQL query for searching through ALL the fields?

It is not possible with one query.

However when you do:

DESCRIBE table_name;

you get the field names which you can generate the query from.

Search in all fields from every table of a MySQL database May be useful.


Here's a solution combined with some PHP to search all fields in a specific table.

include("db_con.php");
//search all fields
$searchphrase = "banan";
$table = "apa303";
$sql_search = "select * from ".$table." where ";
$sql_search_fields = Array();
$sql = "SHOW COLUMNS FROM ".$table;
$rs = mysql_query($sql);
    while($r = mysql_fetch_array($rs)){
        $colum = $r[0];
        $sql_search_fields[] = $colum." like('%".$searchphrase."%')";
    }

$sql_search .= implode(" OR ", $sql_search_fields);
$rs2 = mysql_query($sql_search);
$out = mysql_num_rows($rs2)."\n";
echo "Number of search hits in $table " . $out;

Yes, it's called Full Text Search.

You can use MySQL's built-in Full Text Search, or use a separate product to do you text indexing such as Apache's Lucene (my personal favorite).

Tags:

Mysql