MYSQL query for searching through ALL the fields?

Question

As dumb as it sounds, is there a way to do something like this:

select row_id from mytable where * like '%searched_text%';

By * here I mean "all the fields" in the table, instead of me specifying them one by one...

Are you trying to build a full text index? You might have better luck with apache lucene and apache solr. — Esben Skov Pedersen, Jan 15 '14 at 14:56

score 8 · Accepted Answer · edited May 23 '17 at 11:46

8

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.

edited May 23 '17 at 11:46

Community

1
1

answered Sep 26 '10 at 13:59

Wikeno

419
2
4

the linked question is exactly what my question is... thanks wikeno – siliconpi Sep 26 '10 at 14:19

score 4 · Answer 2 · answered Feb 20 '13 at 13:37

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;

Should be noted that in the very likely case that you change the search phrase to user input (for example $searchphrase = $_GET['query']) then you need to use mysql_real_escape_string to prevent SQL injection. — Aron, Dec 13 '14 at 10:41

score 2 · Answer 3 · answered Sep 26 '10 at 13:13

2

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).

answered Sep 26 '10 at 13:13

Pablo Santa Cruz

176,835
32
241
292

umm - hi Pablo - how do I use it? It sounds like I need to modify my columns? – siliconpi Sep 26 '10 at 13:42
Full Text doesn't help, you still have to enumerate all the fields you search in. – Wikeno Sep 26 '10 at 13:48
@matt_tm, you don't need to modify your columns, you just need to create a FULL TEXT index on all the columns you want to search. It's like creating an index. – Pablo Santa Cruz Sep 26 '10 at 14:57
@Wikeno: true. But I think he's trying to avoid a **HUGE** where clause here, concatenated by **OR** conditions... I might be wrong though... – Pablo Santa Cruz Sep 26 '10 at 14:59

score 0 · Answer 4 · answered Jan 09 '15 at 12:44

I was looking something like this to search in all the fields of a table. Though my table having less data so I opted 'Kilian Lindberg' answer and make the PDO function using his idea. In this function we can sent the 'search string' and 'Table name' in parameter and it will return the SQL string which we can use further as per our requirement. Thought in big tables it may slowdown the process.

function columnsTable($Search, $Table){
    include("PDO_conn_detail.php"); /* your PDO mysql connection file */
    $srchSQLstr = "SELECT * FROM $Table WHERE ";
    $tableFields = Array();

    $colSQL = $conn->prepare("SHOW COLUMNS FROM $Table");
    $colSQL->execute();
    while ($result_colSQL = $colSQL->fetch(PDO::FETCH_ASSOC)){
            $tableFields[] = $result_colSQL[Field]." LIKE ('%".$Search."%')";
        }

    $srchSQLstr .= implode(" OR ", $tableFields);
    return $srchSQLstr;
}

score -1 · Answer 5 · answered Jul 18 '18 at 17:28

-1

Use like this

select row_id from mytable where '%searched_text%' IN ('column1','column2','column3','column4','column5');

answered Jul 18 '18 at 17:28

Sahil Jangra

31
3

score -1 · Answer 6 · answered May 26 '20 at 07:15

This solution make it possible by Ms Sql Server via Linked server

DECLARE @searchTxt VARCHAR(100) = 'searching text'

DECLARE curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
select * from openquery (BITRIX, 'SELECT table_name, column_name FROM information_schema.columns where table_schema = ''bitrix_50'' and data_type = ''varchar'' and table_name not like ''b_mail%''')

DECLARE @tName VARCHAR(100), @cName VARCHAR(100), @sql NVARCHAR(4000), @cnt INT

OPEN curr

FETCH NEXT FROM curr INTO @tName, @cName

WHILE (@@FETCH_STATUS=0)
BEGIN

    SET @sql = 'select @cnt=cnt from openquery (BITRIX, ''SELECT count(*) cnt FROM ' + @tName + ' WHERE ' + @cName + ' LIKE ''''%' + @searchTxt + '%'''''')'

    BEGIN TRY

        EXECUTE sp_executesql @sql, N'@cnt int OUTPUT', @cnt=@cnt OUTPUT

        IF (@cnt>0)
            PRINT @sql

    END TRY
    BEGIN CATCH
        PRINT N'Error on: ' + @sql
    END CATCH

    FETCH NEXT FROM curr INTO @tName, @cName

END

CLOSE curr
DEALLOCATE curr

Access MySQL data from SQL Server via a Linked Server

Translate it to SQL/PSM :-) than it works on mysql, too. – Frank May 26 '20 at 07:46 — Frank, May 26 '20 at 07:46

score -2 · Answer 7 · answered Jan 15 '14 at 14:52

He is a complete solution,,is the modification of the above solution,It worked for me, Thank you.

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
        <?php
        include('config.php');
        $searchphrase = "adsa";
        $table = "tenders";
        $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."<br />";
        while ($results = mysql_fetch_array($rs2)){
            print $results[0]."<br />";
        }
        ?>
    </body>
</html>

MYSQL query for searching through ALL the fields?

7 Answers7

Linked

Related