|
|
Posted by kenoli on 10/21/06 11:10
As usual, a thoughtful reply. What I think is being done here, however
is that you are getting a list of keywords associated with a person.
What I want to do, however, is rhe opposite. People will select a
group of keywords using, say, checkboxes identified with keywords from
a lookup table and on submit I will want to return data for all the
persons associates with any selected keyword. Something that does
something like:
SELECT * FROM persons WHERE keyword=keyword1 OR keyword=keyword 2 OR
keyword=keyword3 etc.
I know this isn't a correct query, but it illustrates conceptually what
I will want to do.
--Kenoli
Koncept wrote:
> In article <1161320236.854242.143860@i3g2000cwc.googlegroups.com>,
> kenoli <kenoli@igc.org> wrote:
>
> > I'm interested in people's thinking about key word fileds. I will want
> > to associate a number of key words with each person in a contact list
> > using a mysql database. The key words will be selected from values
> > derived from a lookup table and displayed in a form as check boxes or
> > pulldown menus.
> >
> > I could either have a number of key word fields inserting one key word
> > in each field or concatenate the words in one field. If I add key
> > words I can append them to the one field. Any opinions about which way
> > to do?
> >
> > I will of course want to search on these key words.
> >
> > Thanks,
> >
> > --Kenoli
>
> I would keep your keywords in separate table (easier to query directly
> ) and then relate the id's of people in your database to these keywords
> using a table which bridges people and keywords. (aka keep the keywords
> in their own exclusive fields). Something like structurally:
>
> CREATE TABLE `keywords` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `keyword` varchar(50) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM;
>
> CREATE TABLE `lookups` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `keyword_id` int(11) unsigned default NULL,
> `person_id` int(11) unsigned default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM;
>
> CREATE TABLE `persons` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `first_name` varchar(30) NOT NULL default '',
> `last_name` varchar(30) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM;
>
>
> Then to get keywords that relate to a particular member, you can do
> this:
>
> SELECT keywords.keyword
> FROM persons
> INNER JOIN lookups ON persons.id = lookups.person_id
> INNER JOIN keywords ON lookups.keyword_id = keywords.id
> WHERE persons.id = [the person's id]
> ORDER BY keywords.keyword ASC
>
> Would be interested how others would handle this...
>
> --
> Koncept <<
> "The snake that cannot shed its skin perishes. So do the spirits who are
> prevented from changing their opinions; they cease to be a spirit." -Nietzsche
Navigation:
[Reply to this message]
|