hello sir,
the code u sent works a bit but searches for the first keyword. i have
stored all keywords in another table seperated by commas. thp code
searches for first keyword and leaves the rest tell me how to enter
keywords to table. and format is there . pls make me clear.
thanx once again
Arun
On Wed, 6 Feb 2002, Josh Berkus wrote:
> Arun,
>
> > thnx for reply sir. actually we are maintaining a cdrom database in
> > postgresql wherein we have cd rom titlename,author,year etc and
> > keywords
> > as fields. we will store a set of keywords for each record. i want to
> > know
> > how to enter those keywords in to database (whether to have comma
> > between
> > keywords or plain) and code to search for keywords. i.e. if i enter a
> > keyword then it should search for that keyword in keyword field and
> > display result. keywords are strings an there will also be some
> > combinational search. i believe u will see to this and do the
> > needful.
>
> You should use a subtable. Example:
>
> CREATE TABLE cd_inventory (
> cd_id SERIAL NOT NULL PRIMARY KEY,
> cd_title VARCHAR (150) NOT NULL,
> cd_publisher VARCHAR (150) NULL,
> cd_year INT2 NULL DEFAULT (EXTRACT (year FROM CURRENT_TIMESTAMP)),
> etc.
>
> CREATE TABLE cd_keywords (
> cd_key_id SERIAL NOT NULL PRIMARY KEY,
> cd_id INT4 NOT NULL REFERENCES cd_inventory(cd_id),
> --this is a Foriegn Key to the cd_inventory table
> keyword VARCHAR(50) NOT NULL,
> CONSTRAINT cs_cd_key_unq UNIQUE (cd_id, keyword)
> );
>
> CREATE INDEX idx_cd_key ON cd_keywords (keyword)
>
>
> Thus each CD in the inventory will have a list of zero to many keywords
> in cd_keywords. Once the data is populated, you search like this:
>
> SELECT * FROM cd_inventory
> WHERE EXISTS (SELECT cd_id FROM cd_keywords
> WHERE keyword = '$keyword_searched'
> AND cd_id = cd_inventory.cd_id)
> --for multiple keywords
> AND EXISTS (SELECT cd_id FROM cd_keywords
> WHERE keyword = '$keyword_searched2'
> AND cd_id = cd_inventory.cd_id)
> etc.
>
> Get the general idea? This is the relational way to approach a simple
> keyword list.
>
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
