0

I have a mysql table called main and I would like to select all the rows that contain a string in any field.

Not all the fields are varchar.

I do this for the field hardware, but I would like to be able to search in all the fields and if possible not to specify their names, because there are 12 fields now and they might grow in number.

SELECT * FROM main WHERE hardware LIKE "%string%"

EDIT :

I will do this in PHP and basicly I would like to do sometring like this

SELECT * FROM main WHERE at_least_one_field LIKE "%string%"
Ionut
  • 1,729
  • 4
  • 23
  • 50
  • 1
    How and why are you doing this? I would guess the best answer to this question depends on what kind of tools you use to execute the query. – Samuel Åslund May 15 '14 at 18:56
  • I will do this in PHP but I would like, if possible, to receive the the data already filtered. – Ionut May 15 '14 at 19:10
  • http://stackoverflow.com/questions/6226417/compare-value-with-all-the-fields-of-a-table-in-mysql – Ejaz May 15 '14 at 19:28

2 Answers2

1

As mentioned in the comment from Ejay, full text search would be the best way. I didn't realize that since 5.6 full text worked on InnoDB tables.

If you can't use full text search, the following would work on smaller data sets, but it won't scale very well at all.

Probably not the most efficient way, but what about creating a view that unions all the different fields together.

CREATE view searchTerms ...
SELECT id, field1 as SearchValue FROM main 
UNION 
SELECT id, field2 from main
... etc

Then link the view to the main table 

SELECT m.* 
FROM main m 
    LEFT JOIN searchTerms s ON m.id = s.id
WHERE s.SearchValue like '%stringToSearch%';

Here's a link to a sql fiddle that demonstrates what I'm showing.

xecaps12
  • 5,316
  • 3
  • 27
  • 42
0

I thought that it can be done in a simpler way. I think I will use this solution:

SELECT * FROM clients
WHERE field1 LIKE '%search%'
   OR field2 LIKE '%search%'
   OR field3 LIKE '%search%'
   OR field4 LIKE '%search%'
   ....
   OR fieldX LIKE '%search%'
Community
  • 1
  • 1
Ionut
  • 1,729
  • 4
  • 23
  • 50