Search form for keywords

[ccladder]

Good afternoon everybody,

The last 3 days I am creating a database for myself, but for 2 days I am stuck on a search form. And unfortunately other topics here didn't help me out.

I am a songwriter and I want to store lines that I think would be handy to use one day. Also I want to give those lines keywords to search on themes for a song I want to write, for instance 'love', 'grieve' or 'Happy'.

So I have a table with the following fields
Line ID (INT)
Date (Date)
Line (Varchar)
Keywords (VARCHAR_IGNORECASE)
Attachement location (Varchar)
Language (INT) <-- This comes from another relational table

Now in the Keywords field I have multiple keywords devided by a ';'
For instance Love; Grieve; Happy; Fear

When I have 2000 lines or more I would like to have a search funtion in a form to search on keywords. But this should be with a contains or an anywhere function since it should also find the 3rd word when I search for the keyword Happy. (Refering to the example above 3rd keyword is happy)

The result I would like is to see a table with all the lines that contain the keyword Happy and others should be filtered away.

Can somebody give me some tips or is there another way you advise me to achieve this?

Thanks in advance!

[UnklDonald418]

You didn't reveal the name of your table so you will need to edit the following query, but try

Code: Select all

SELECT * FROM "YourTableName" WHERE INSTR( "Keywords", :Enter_Key_Word ) > '0';

If the keywords actually appear in "Line" you could dispense with "Keywords" and use

Code: Select all

SELECT * FROM "YourTableName" WHERE INSTR( "Line", :Enter_Key_Word ) > '0';

Edit: Sorry, it appears that the SQL function INSTR isn't available in HSQLDB 1.8.0 that is used in the Embedded version of Base. To use the above queries you will need HSQLDB version 2.3.2 or later, which requires a split database. viewtopic.php?f=83&t=61183

To normalize the Keywords in your database you would have no "Keywords" field in your main table instead you would need a table "keywords" with each being a separate record, something like
"Keyword ID" INTEGER which could serve as the Primary Key
"Keyword" VARCHAR_IGNORECASE
Because each line could refer to multiple keywords, and each keyword could refer to multiple lines you would have a many to many relationship which requires an intersection/intermediate table that would contain
"ID" INTEGER Primary Key,
a foreign key "Line ID" INTEGER
a foreign key "Keyword ID" INTEGER

[UnklDonald418]

These queries should work with an Embedded database

Code: Select all

SELECT * FROM "YourTableName"  WHERE  LOCATE(:Enter_Keyword , "Keywords", 0 ) > 0;
SELECT * FROM "YourTableName"  WHERE  LOCATE(:Enter_Keyword , "Line", 0 ) > 0;

[ccladder]

Hi Donald that looks promising:). I will try that tomorrow thanks a lot for the reply . I will refer back if it Works

[ccladder]

I have now created the keyword table and the linking table between Keyword and line. That was very good advise .
Now I want to create a form to fill the keywords, but can I create a check to make sure I don't fill doubles?

And for the SQL lines you have provided I have to create a query for it and link it to a form right?

[Villeroy]

http://forum.openoffice.org/en/forum/do ... hp?id=6442 contains a search form for a string starting with entered string. A similar solution would work with keywords in a single field.

[Example] Relations reflected by list boxes in forms is a small tutorial about one-to-many (items belonging to one distinct item) and many-to-many (items belonging to other items e.g. items with keywords) relations.

download/file.php?id=25922 movies with genres (aka "keywords")

download/file.php?id=32017 (various filters for many-to-many relations)

[UnklDonald418]

I would recommend creating a form using the Form Design Wizard with the Lines table on the mainform and the linking table on the subform. They would be linked with the key “Line ID”.
Use table controls for both the mainform and subform, the wizard refers to them as Data Sheets.
Edit the subform table control and add a listbox column for the Keyword. The listbox would use a query set up by the Listbox Wizard to display a list of keywords but store the “Keyword ID” in the linking table.

Then on your form you could select a Line on the mainform table and the subform table would display any keywords already associated with that line, and the listbox control would allow you to add/edit keyword associations.

The queries I provided earlier wouldn't be needed on this form, but a modified version of them might be handy for creating another form where you can select a keyword and display all the matching Lines.

Villeroy has provided links to good examples.