[RESOLVED] search engine with mysql and php

private_click

hi.
i am currently working on a website and i need to build an internal search engine for it.
what i want to do is to search through multilple tables with different layouts at the same time.

how the db looks:i have 3 tables, one for news, one for smth like a dictionary and another table for various content (includes html code)

news:id, title, body, image, date, visible, rss
dictionary:id, title, body
content: id, parent, parent_by, content

i need to search for a given keyword through title and body of news, through title and body of dictionary and through content of content.
is there any way to do this?
any help will be appreciated.thx

sneakyimp

Yes. Sounds like you need 3 queries. If you want to search news, the simplest query might be this:

SELECT * FROM news WHERE title LIKE '%keyword%' OR body LIKE '%keyword%'

You can also use full text search functionality. Try reading the MySQL documentation on full-text search.

private_click

thx for your reply.
yea...the thing is that i want to use only one query.is it possible? 🙂

sneakyimp

Yes it is. Try reading about UNION syntax. It might look something like this:

SELECT n.id AS id, n.title as title, n.body as body, 'news' as table_name FROM news n WHERE n.title LIKE '&#37;keyword%' OR n.body LIKE '%keyword%' 

UNION

SELECT d.id AS id, d.title as title, d.body as body, 'dictionary' as table_name FROM dictionary d WHERE d.title LIKE '%keyword%' OR d.body LIKE '%keyword%'

I don't really understand your content table so I didn't include that one.

private_click

works great with UNION, thx a lot.