Search in multiple columns

Question

Having the following table schema:

id | english_name | russian_name | year
---------------------------------------
 5 |   The Book   |    Kniga     | 2008

and the given input: Kniga 2008;

Is it possible to reproduce the following steps?

• Search russian_name for EXACT MATCH. If matched, return the row;
• If no matches, search english_name for EXACT MATCHES.
• If still no matches, like in the following case (input is Kniga 2008), then try to look among columns and see if there is ANY COMBINATION of columns matched. In this particular case russian_name and year will match.
• Ignore characters like -, /, : so if the given input is: The Book / Kniga 2008 or is The Book : 2008, the results should still contain the row above.

Answer 1

It's not pretty, but:

SELECT * FROM books WHERE russian_name = ? OR english_name = ? OR CONCAT(russian_name, ' ', year) LIKE ? OR CONCAT(english_name, ' ', year) LIKE ?

where the query string is set to the first two question marks, and the second and third as:

'%' . str_replace ( ' ', '%', $query ) . '%'