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?
<?bhb if(broken){ echo("It wasn't me 
"); } ?>
Learn HTML the ez way - EzHTML.net
Some people are like slinkies, they dont really serve any purpose but they still bring a smile to your face when you push them down the stairs ...
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,157 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
<?bhb if(broken){ echo("It wasn't me
"); } ?>
Learn HTML the ez way - EzHTML.net
Some people are like slinkies, they dont really serve any purpose but they still bring a smile to your face when you push them down the stairs ...
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.memberemailFROM 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.memberemailFROM 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.