PostgreSQL, PHP - How to design a database search?

I need to design a search form and the code behind it.

I'm not very familia开发者_运维问答r with searches.

My table have the following aspect:

- Table_ads
site_name
ad_type
uri
pagetitle
name_ad
general_description_ad
age
country_ad
location_ad
zone_ad

Initially my idea was to do a search like google, we have a single text box and the button search, but I think this will be difficult. The other option is to build a search by fields(traditional search)

What do you think about this subject. What type of search should I do?

Best Regards,

PS: Sorry my English.

---

For "google-like" search it's best to use Full-Text Search (FTS) solution.

PostgreSQL 8.3 and newer has a built-in FTS engine, and it will let you do all querying in SQL. Example:

SELECT uri FROM ads WHERE fts @@ plainto_tsquery('cereal');

See documentation -> http://www.postgresql.org/docs/current/static/textsearch.html and come back if you have more questions :-)

However, in-database FTS is several times slower than dedicated FTS.

So if you need better performance, you will have to build an index outside of database,

Here I would recommend Sphinx -> http://sphinxsearch.com/docs/current.html, Sphinx integrates smoothly with PHP. You feed it with documents (preferably, in form of special XML docset) and update the index on demand or with some scheduler. Then you do searching directly from PHP (not touching the database).

HTH.