keyword search/notification

Busy posted this at 23:29 — 26th November 2004.

Joined: May 2001

I have two database rows, one with keywords, the other with story title/description. What I want to do is when a new story is inserted it goes through the keywords and if the keywords match title or description sends an email to the person waiting for this story.

I'm a bit stumped on how to do this without using a million database queries (in a while loop).

something like:

select all keywords from keywords table
while loop
strstr(title-description,keyword)
if keywords match send email

that could be a lot of loops
I could put all the keywords into an array and query through that (using preg_split() and strstr()), but doesn't seem like the best solution.

Any ideas?

ROB posted this at 21:43 — 27th November 2004.

They have: 447 posts

Joined: Oct 1999

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?

Busy posted this at 02:12 — 28th November 2004.

He has: 6,151 posts

Joined: May 2001

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 posted this at 02:40 — 4th December 2004.

They have: 447 posts

Joined: Oct 1999

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'
;

ROB posted this at 02:44 — 4th December 2004.

They have: 447 posts

Joined: Oct 1999

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.