Re: Complex search advice?

On Tue, 2007-02-20 at 11:26, Robert Fitzpatrick wrote:
> I want to be able to build complex search and reporting capabilities in
> to our PHP5 application. We want to be able to save searches for later
> use. We also want to build queries from virtually any field in certain
> tables from our PHP app. I hope to do as much within postgresql as
> possible. Then I start looking for how to enumerate field names, etc.
> Before I spend countless hours on seeing if some of my ideas will work
> and coding them, I hoped to receive some guidance here as to where I
> should start and possibly what are the elements of something like this?
> Will anything in contrib help?

Depending on the scale of your dataset, you might wanna look into
tsearch2, it's a full text search engine that lets you search very large
text data sets quickly.

If you're not going to have hundreds of thousands of records to search
through, then you might be able to just roll your own using standard
searches.

If by enumerate field names you mean how to ask postgresql what field
names it has on a table, the easy easy way in PHP is to run a query like

select * from table limit 1

then use pgsql_fetch_assoc() to grab the first row and iterate through
the keys you get back.

If you already know the column names, then you can use an array to store
the ones you want to search on and iterate over that.

There's lots of examples on the net for how to do this, and look at
www.phpbuilder.com for forums discussing just this.