How to organize a search for values ​​in all columns of a table?

    4 answers 4

    select * from `table` where `col1` like "%$query_string%" or `col2` like "%$query_string%" or `col3` like "%$query_string%"...

    So looking for PHPMyAdmin.

     SHOW COLUMNS FROM `table`;

    For the definition of columns. =)

       select * from table where concat(col1,col2,col3) like '%search_string%'

      Without explicitly specifying columns, dynamic SQL will be required.

        Search script in all fields of the table for the entry of at least one word from the query

         $search = trim(substr($search, 0, 64)); // Это можно убрать, это ограничение на 64 символа в поиске $search = preg_replace("#\#s=#msi", "", $search); $search = preg_replace("/[^\w\x7F-\xFF\s]/", " ", $search); $search = preg_replace("# +#msi", " ", $search); if(empty($search)) return false; $result = mysql_query("SELECT * FROM `table` WHERE `field1` LIKE '%".preg_replace("# #msi", "%' OR `field1` LIKE '%", $search)."%' OR `field2` LIKE '%".preg_replace("# #msi", "%' OR `field2` LIKE '%", $search)."%'");
        • God, what is it? ... O_O No, I like it, but what is it? ... My eyes have already fled - knes
        • This is the php code that receives the $search query string. Then removes spaces at the end and beginning of the line. Then it removes the special characters, then removes the extra spaces (the result is a string, which you can safely stick into the database), and then searches the database. As a result, there are all results that contain an entry of at least one word upon request) - Daniel Vendolin
        • Thanks for the answer, but you need to search through all columns, explicitly specifying column names. To apply to any table - thedarkelf
        • Not sure if this is possible only with MySQL. You can write a function in php, which by first request will receive a list of all fields. And the second is to conduct a search. - Daniel Vendolin September
        • I did it .. but I wanted to do everything with one request .. - thedarkelf

        The procedure for finding values ​​in all columns of a table.

        Verified on Postgres.

        The meaning is as follows: we select all the columns of the table from the schema. Then we go around the selected values ​​in the 'loop' and collect the query, which selects the number of matches with the desired value greater than zero.

         CREATE PROCEDURE `findValue`( IN `_value` VARCHAR(50), IN `_table` VARCHAR(50), IN `_database` VARCHAR(50) ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'Search the value in any columns of table.' BEGIN DECLARE done BOOL; DECLARE t, c varchar(250); DECLARE _query LONGTEXT DEFAULT ''; DECLARE cur CURSOR FOR SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = _database AND table_name = _table; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; Open cur; read_loop: LOOP FETCH cur INTO t, c; IF done THEN LEAVE read_loop; END IF; IF _query <> '' THEN SET _query = concat(_query, ' UNION '); END IF; SET _query = concat(_query, 'SELECT "', t, '" as tbl, "', c, '" as col, count(', c, ') as have from ', concat(_database, '.', t), ' WHERE ', c, ' like "%', _value, '%" HAVING have > 0'); END LOOP; Close cur; SET @q = _query; prepare qqq from @q; execute qqq; END

        call: call findValue ('value', 'table_namer', 'database');