Need help witha query

[kjc]

Hello,

I am building an e-commerce site for first edition books.
I have a search field which will allow the customer to enter a title or author and then search for matching results.

My database has a field for titile, author firstname, and author surname.

The query at the moment is
"select * from books where Live = 'Y' AND (Title like '%".$searchterm."%' OR Author_Firstname like '%".$searchterm."%' OR Author_Surname like '%".$searchterm."%')"

This works fine when the customer enters a title or just a surname but if they enter both names then this causes a problem.

Is there any way of getting around this...

[mordred]

You could try

OR CONCAT(Author_Firstname, ' ', Author_Lastname) LIKE '%$searchterm%'
OR CONCAT( Author_Lastname, ' ', Author_Firstname) LIKE '%$searchterm%'

...though that's quick hack if not even worse. The best-suited approach would surely be fulltext indices and trying to use MATCH AGAINST, but my current expertise on these constructs is not very good, so I just point you to the manual:
http://www.mysql.com/doc/en/Fulltext_Search.html