Re: How do I make such a complicated demand (for me)on mysql and php

On Fri, 2 May 2003 00:24:34 +0100, you wrote: >Okay... I am out of my depth and confused and would appreciate some help to push me in the right >direction. > >I have 3 tables in MYSQL: >"users" - contains users email address and the press release categories that they wish >to hear about (in usr_4). >"releases" - contains press releases with a datstamp in (Ymd) format and presscatid >categories - which has all the press release categories and presscatid > >What I want to do is send all users that have asked to receive daily updates of chosen >categories the title, first 150 characters and the link back to the release, an email of the >preferences that they have chosen (eg Food, Transport etc), if there is a new release in that >category. > >I have so far managed to get the releases with a datestamp of $today and send it to my email >address (hardcoded in the script) > >I can not work out how to get the script to step through all the press release categories on by >one and which logic is needed. Do I select the cateorgies and start that end or do I select the >users and the work through the categories that they have requested to hear about? What's "usr_4"? Ok, if it's not too late I suggest normalising your database so the join between (user and category) is in a separate table, because it's a many-to-many relationship. Something like this: user userid // primary key email // email address category categoryid // primary key description // "food", "transport", etc usercategory userid // primary key = (userid | categoryid) categoryid release releaseid // primary key categoryid // foreign key to category releasedate // date of release releasetext // body of release [If you decide that a single press release can be in multiple categories, you should create a similar joining table between category and release]. Now, the SELECT is pretty simple: SELECT email, description, releasetext FROM user, release, category, usercategory WHERE usercategory.userid = user.userid AND category.categoryid = usercategory.categoryid AND release.categoryid = category.categoryid AND releasedate = $today ORDER BY emailaddress, description Untested, but should be ok - I'm not about to build a db just to test it :) This should returns rows like this : emailaddress1, food, releasetextA emailaddress1, food, releasetextB emailaddress1, transport, releasetextD emailaddress2, food, releasetextB emailaddress2, transport, releasetextC emailaddress2, transport, releasetextD emailaddress3, food, releasetextA emailaddress3, transport, releasetextC emailaddress3, transport, releasetextD At which point, it's simple to bundle the releases up into single emails for each user. If things get really high-volume, you might want to group emails with identical category choices together, and bcc: the same email to groups of people. But that is a little more difficult. Hmm... oh yeah. That was a MySQL question, not PHP.