[RESOLVED] Simple SQL query

rvpals

I have a table book contains fields author, title,publisher,note etc.

I want to contruct a SQL statement that can return results where any one of the above fields matches a certain search term. For example, if I enter "book", I want all the rows that matchs book in any one of the fields in the book table (be it in author, title, publisher or note field)

I know I can do a "SELECT * from book where title LIKE '%book%' OR author LIKE '%book%' OR publisher LIKE '%book%' OR note LIKE '%book%'.

My question is is there a simpler query than the one above. One that does not have to specify all the field names.

I know this doesn't work:
select from book where LIKE '%book%'

TIA

devinemke

you need to query each field but you could make your code a bit more concise by getting all of the field names (use a DESCRIBE query) and then looping thru the list to constuct your search query. this would also be easier to maintain since you would not need to update the code if you later add new fields.

pohopo

You have to indicate all the field names, but you can make it shorter then what you have by concatening the fields names like so...

SELECT *
FROM book
WHERE title + author + publisher + note LIKE '%book%'

Drakla

If it is MySQL you concat like so

SELECT *
FROM animalhouse
WHERE CONCAT(animal, house, shed, kennel) LIKE '%dog%';

Constructing big queries on the fly as Devinemke said, rather than writing them out is also very good practise as it makes your code much cleaner and easier to maintain and the overhead is nothing.

$fields = array('author', 'book', 'title', 'publisher', 'note');

foreach ($fields as $field)
	$qf[] = $field . " LIKE '%$searchTerm%'";

// implode the fragments to OR them
$qf = implode(' OR ', $qf);

$query = "SELECT * FROM books WHERE $qf";

rvpals

Drakla wrote:
If it is MySQL you concat like so

SELECT *
FROM animalhouse
WHERE CONCAT(animal, house, shed, kennel) LIKE '%dog%';

Constructing big queries on the fly as Devinemke said, rather than writing them out is also very good practise as it makes your code much cleaner and easier to maintain and the overhead is nothing.
$fields = array('author', 'book', 'title', 'publisher', 'note');

foreach ($fields as $field)
	$qf[] = $field . " LIKE '%$searchTerm%'";

// implode the fragments to OR them
$qf = implode(' OR ', $qf);

$query = "SELECT * FROM books WHERE $qf";

Thanks a lot, you guys are great. I thought for sure SQL has something like * like. But concatenate the fields are great too.

rvpals

pohopo wrote:
You have to indicate all the field names, but you can make it shorter then what you have by concatening the fields names like so...

SELECT *
FROM book
WHERE title + author + publisher + note LIKE '%book%'

pohopo, is this query with + to concatenate the fields means AND?

How do you do an OR? is that a short hand for OR too?

Thanks a lot.