Re: [PHP-DB] Subscription Suspensions
On Tue, Jan 31, 2012 at 6:35 PM, Ron Piggott wrote: Hi Everyone. I manage an e-mail list. I am trying to add a new function into it: suspensions. The idea is that someone wouldn’t have to unsubscribe. Instead there would be a record of their suspension in the table member_subscription_suspensions and their account wouldn’t be included as a database query result when sending out the daily e-mail. There are three tables: Profile of the subscriber, subscriptions and subscription suspensions: How do I change this query to exclude any one who has a suspension record for “today”. IE CURDATE() Thanks for your help. Ron Ron Piggott Hi Ron, I'm not sure why you posted your question here, it has no relation to PHP whatsoever. And while you're not specifically asking, you cannot assume people will start to write SQL for you. It would've made sense if you mentioned what database you're using. Some databases support the EXCEPT operator in SQL, though it seems that the most popular one, MySQL, does not support it. You can achieve the same with LEFT JOIN. Wikipedia has an example[1] on how to replace the EXCEPT operator on databases that don't support it. Hope this will help you get started. - Matijn [1] http://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator Thank you for showing me this web page. It was what I was trying to find. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Subscription Suspensions
On Tue, Jan 31, 2012 at 6:35 PM, Ron Piggott wrote: > > Hi Everyone. > > I manage an e-mail list. I am trying to add a new function into it: > suspensions. The idea is that someone wouldn’t have to unsubscribe. Instead > there would be a record of their suspension in the table > member_subscription_suspensions and their account wouldn’t be included as a > database query result when sending out the daily e-mail. > > There are three tables: Profile of the subscriber, subscriptions and > subscription suspensions: > > > How do I change this query to exclude any one who has a suspension record for > “today”. IE CURDATE() > > Thanks for your help. Ron > > Ron Piggott > Hi Ron, I'm not sure why you posted your question here, it has no relation to PHP whatsoever. And while you're not specifically asking, you cannot assume people will start to write SQL for you. It would've made sense if you mentioned what database you're using. Some databases support the EXCEPT operator in SQL, though it seems that the most popular one, MySQL, does not support it. You can achieve the same with LEFT JOIN. Wikipedia has an example[1] on how to replace the EXCEPT operator on databases that don't support it. Hope this will help you get started. - Matijn [1] http://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subscription Suspensions
Hi Everyone. I manage an e-mail list. I am trying to add a new function into it: suspensions. The idea is that someone wouldn’t have to unsubscribe. Instead there would be a record of their suspension in the table member_subscription_suspensions and their account wouldn’t be included as a database query result when sending out the daily e-mail. There are three tables: Profile of the subscriber, subscriptions and subscription suspensions: Membership Profiles are in this table: CREATE TABLE IF NOT EXISTS `member` ( `record` int(10) NOT NULL AUTO_INCREMENT, `first_name` varchar(40) NOT NULL DEFAULT '', `last_name` varchar(40) NOT NULL DEFAULT '', `email` varchar(40) NOT NULL DEFAULT '', PRIMARY KEY (`record`), UNIQUE KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3861 ; Subscriptions are stored in this table: CREATE TABLE IF NOT EXISTS `member_subscriptions` ( `subscription_reference` int(30) NOT NULL AUTO_INCREMENT, `member_reference` int(10) NOT NULL DEFAULT '0', `list` int(2) NOT NULL DEFAULT '0', PRIMARY KEY (`subscription_reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2130 ; I have designed this table to store subscription suspensions: CREATE TABLE IF NOT EXISTS `member_subscription_suspensions` ( `reference` int(30) NOT NULL AUTO_INCREMENT, `member_reference` int(11) NOT NULL DEFAULT '0', `list` int(2) NOT NULL DEFAULT '0', `subscription_begins` date NOT NULL DEFAULT '-00-00', `subscription_expires` date NOT NULL DEFAULT '-00-00', PRIMARY KEY (`reference`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; The common column between each table is: member.record member_subscriptions.member_reference member_subscription_suspensions.member_reference The only way I know how to write the query (which is wrong) is: SELECT * FROM ( `member` INNER JOIN `member_subscriptions` ON `member`.`record` = `member_subscriptions`.`member_reference` ) LEFT OUTER JOIN `member_subscription_suspensions` ON `member`.`record` = `member_subscription_suspensions`.`member_reference` WHERE CURDATE() BETWEEN `member_subscription_suspensions`.`subscription_begins` AND `member_subscription_suspensions`.`subscription_expires` AND `member_subscriptions`.`list` = 1 How do I change this query to exclude any one who has a suspension record for “today”. IE CURDATE() Thanks for your help. Ron Ron Piggott www.TheVerseOfTheDay.info
