PostgreSQL, PHP - How to design a database search?

Question

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

I'm not very familiar 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.

Can you be more specific, please? Which of the columns do you want users to search? — user554546
– user554546, Commented Apr 5, 2011 at 19:28

filiprem · Accepted Answer · 2012-01-11 23:35:05Z

1

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.

answered Jan 11, 2012 at 23:35

filiprem

7,2231 gold badge35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL, PHP - How to design a database search?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related