ROB — Sat, 04 Dec 2004 02:44:07 +0000

Disclaimer: it's Friday night and my buzz is on, remember that when the above examples dont workl, or if they have nothing to do with the question.

ROB — Sat, 04 Dec 2004 02:40:16 +0000

What you need is another table associating email addresses with keywords, or more likely associating members with keywords.

For example, from what I understand of what you're trying to do, I'd take an approach like this:

TABLE members (
	memberid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	membername VARCHAR(100),
	memberemail VARCHAR(100),
	etc...
);

TABLE stories (
	storyid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	storytitle VARCHAR(255) NOT NULL,
	storydescription TEXT NOT NULL,
	storytext TEXT NOT NULL,
	etc...
);

TABLE keywords (
	keywordid INT UNSIGNED NOT NULL PRIMARY KEY
	keyword VARCHAR(100) UNIQUE NOT NULL
);

TABLE story_keywords (
	storyid INT UNSIGNED NOT NULL,
	keywordid INT UNSIGNED NOT NULL,
	PRIMARY_KEY(storyid, keywordid)
);

TABLE member_keywords (
	memberid INT UNSIGNED NOT NULL,
	keywordid INT UNSIGNED NOT NULL,
	PRIMARY_KEY(storyid, keywordid)
);

Now, assuming your program populated these tables accurately, let's say you have a specific keyword you want to notify members about, members who want to know about that keyword, such as 'fishing':

SELECT DISTINCT m.memberemail
FROM members AS m
INNER JOIN member_keywords AS mk ON m.memberid=mk.memberid
INNER JOIN keywords AS k ON mk.keywordid=k.keywordid
WHERE  k.keyword='fishing'
;

The above query should give you the email of every member associated with the keyword 'fishing'.

Similarly, if you want to notify everyone associated with every keyward associated with a story, say storyid 834:

SELECT DISTINCT m.memberemail
FROM stories AS s
INNER JOIN story_keywords AS sk ON s.storyid=sk.storyid
INNER JOIN keywords AS k ON sk.keywordid=k.keywordid
INNER JOIN member_keywords AS mk ON sk.keywordid=mk.keywordid
INNER JOIN members AS m ON mk.memberid=m.memberid
WHERE s.storyid='834'
;

Busy — Sun, 28 Nov 2004 02:12:28 +0000

rows - columns ...
the email is something I thought about after I mentioned the arrary method as it wont work as I don't have the email in that column.
At the moment I am playing with something like:

$themboth = strtolower($title, $description);
select * from keyword_table; (* = id, user and keyword)
while(result = query)
{
$match = strstr($themboth, strtolower($result['keyword']));
if($match){ get email from another column from result['user'] and send it; }
}

of course with the right syntax, this was just off the top of my head as the code I have on the page is a little bit of this and a little bit of that (trying different things).
The above would work but if it can be a lot of database queries if people used simple words like 'the', 'and', 'php', 'html', 'code' ... I can only really restrict it to a minimum of 3 characters as things like php, css etc are 3.
If I restrict the simple words (the, and etc) things like "show the code" would become "show code" and not match "show the code".

Cheers

ROB — Sat, 27 Nov 2004 21:43:44 +0000

I think you mean you have two database columns, one with keywords and the other with story title/desc. The question is, where are the emails and how are they related to your keyword table?