Searching in many fields

intenz

How do I search in a MySQL table which has 30+ fields?

WHERE foo LIKE '%foo%' AND foo2 LIKE '%foo2%' AND ...

This will be so long and would be a great TheDailyWTF input. Any suggestions? :bemused:

jara06

you probably have to go the hard way with this. I dont know enough about the subject for helping out. 🙁

bradgrafelman

Well, you can always use a for() loop and increment numbers sequentially to automatically build a query.

What are the values going to be like? Are the fields actually enumerated as such, i.e. a constant keyword appended with a number? If so, a simple PHP for() loop would do the trick:

$query = 'SELECT * FROM `table` WHERE ';

for($i = 1; $i < 30; $i++)

 $query .= '`foo' . $i . '` LIKE \'foo' . $i . '\' AND ';

$query = rtrim($query, ' AND');

If you exaggerated the simplicity of the structure/query in order to explain it, then more complicated loops or alternate methods might still be able to help you. If nothing else, better start a-typing! 😉

Sxooter

Note that you may want to substitute OR for AND in this query, unless you really want the search team in every single field of a row.

It's also a simple step to put this loop in another loop and have more than one search term, and AND the outside, so you have queries like:

where (field1 like '%term1%' OR field2 like '%term1%') AND (field1 like '%term2%' OR field2 like '%term2%')