Search page engine PHP with MYSQL database?

I'm going to generalize this question so that other people can use the answers.

Let's 开发者_如何学JAVAsay I have a website driven by a MYSQL database.

Database contains 5 tables:events,news,books,articles,tips.

Every table has among others 2 fields Title and Details in which I want to search

On every page of the site I have a search form (text field and button).

After I type a word or phrase I want to be redirected to a page called search where I should see the results as a list with links from the entire database.

e.g.

Book X (link on it to the book found in the database)

Event Y

Article Z

HELP: The tables are INNODB ENGINE so full text search didn't work also I'm having trouble in building a SELECT statement for searching multiple fields from multiple tables with LIKE. I've succeded with one table but with multiple tables and multiple fields I'm getting error or no data or duplicated data in some cases. Some help with this Select statement please.

Question: How do I build a search engine for all the tables in my MYSQL DB? Some SQL injection or other hacking prevention advice would be appreciated also.

---

My Approach to the situation is create a view based on all the tables with similar columns ( columns which we need to search only) and one more alias column with their table names/ entity name (Books, event etc)

It should look like this

EntityName      Title     Details
Books            xxxx      xxxxx
...............................

I am not explaining how to create views with union (dont use Union All if not expecting duplicates).

The next stop would be search using like statements

select * from vwSearchData where Title like '%keyword' or details like '%keyword'

Next step is to display the data along with their entity names.

Ofcourse, you need to get the keyword by filtering with html entities from the search form.

---

You can use UNION:

(SELECT * FROM events WHERE title LIKE '$key' OR details LIKE '$key')  
UNION  
(SELECT * FROM news WHERE title LIKE '$key' OR details LIKE '$key')

and so on.