Good time. There is a table with a large number of fields and columns. I need that when searching for the page, exactly that cell would be displayed where a match was found.
Suppose I enter “Minister” in the search and php writes “Minister of Foreign Affairs went there” to me, and the following cells with matches.

  • Please specify what you tried to do? How did you try to search and display data? - Alex
  • I first discovered the values ​​of all columns through $cols = mysqli_query($link, 'SHOW columns FROM progs'); Then, in the loop I searched for matches, sorting through all the fields while ($field = mysqli_fetch_assoc($cols)) { if ((isset($search))) { $search_name = mysqli_query($link, "SELECT * FROM progs WHERE " . $field["Field"] . " LIKE '%$search%'"); - Denis
  • But in this case, I can only display the column that I set myself, for example, $row['opisanie'] . And I just need a cell with a match. - Denis
  • I need the notes themselves. In a MySQL query, the $field["Field"] variable just iterates through all existing columns in a loop. I did it in order not to enter them manually, because there may be a lot of them in the future. I can not understand how to continue to implement it all. - Denis
  • Suppose I sent the query "SELECT * FROM progs WHERE 'opisanie' LIKE '%$search%' OR 'desc' LIKE '%$search%'" . Then I assign this array to a variable in PHP. Then I can not figure out how to conclude exactly the record where the coincidence was. - Denis

1 answer 1

Build a query like this with all columns at once:

 SELECT (opisanie LIKE '%$search%') as in_opisanie, (desc LIKE '%$search%') as in_desc, P.* FROM progs P WHERE opisanie LIKE '%$search%' OR desc LIKE '%$search%'

Upon receipt of the string, you run through all its foreach fields, if the in_X field is 1, then there was a match in the X field and it can be displayed.

In general, it is worth thinking about the structure of the database. This option to say the least brake. Perhaps it makes sense to use full-text search or, if for some reason it does not fit, make an index table in the form:

 id, id_записи_основной_таблицы, id_столбца, слово или id_слова

If the search for individual words is acceptable, make a dictionary in which each unique word will be given an id. Then, during the search, it will be possible to search not like, but with strict equality and then select the records of the main table by the id of the found words. At the same time you will immediately know which columns are found. And all this will be able to work on indices, which on a large amount of data will be many times faster than like '%X%' and even on all fields.