Trying to find a keyword in all columns of a MySQL table-CodePudding

I have been having issues with a query that I am creating to search for books in a system, I am searching for a keyword in all columns in a table. It could be anything and I need it to search the booktitle, author, publisher and language. I need to be able to select the category then also search for they keyword. I am looking for all values as I want to display this to the user.

Here is the query I am trying but I keep getting error after trying a few different things. Can anyone suggest a better approach?

SELECT * FROM book WHERE category='Fiction' AND booktitle, author, publisher, language LIKE '%King%';

I have tried different combinations using WHERE and LIKE but keep getting errors or 0 results for my query.

CodePudding user response：

try this :

SELECT * FROM book 
WHERE category='Fiction' AND (
booktitle LIKE '%King%' 
OR author LIKE '%King%' 
OR  publisher LIKE '%King%' 
OR  language LIKE '%King%');

CodePudding user response：

If it's sufficient the word/key occurs in at least one of all affected columns, just concat them and check whether the entire string contains it:

SELECT * FROM book 
WHERE category='Fiction' 
AND CONCAT(booktitle, author, publisher, language) LIKE '%King%';