Re: [PHP-DB] SELECT online store discount %
Ron Have u thought of CASE (in SELECT)http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html. Remebber their is some syntactical difference in CASE for SELECT and CASE in procedures regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] SELECT online store discount %
A variety of if’s and Greatest in conjunction to mySQL math works! SELECT ( ( GREATEST( IF( ( SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 10, 0 ) , IF( ( SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `buyer_email` = '$client_email' AND `paymentstatus` = 'Completed' LIMIT 1 ) , 5, 0 ) ) ) + ( IF( ( SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 ), 10, 0 ) ) ) AS discount_percentage The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Amit Tandon Sent: Monday, August 22, 2011 5:45 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] SELECT online store discount % Ron Have u thought of CASE (in SELECT). Remebber their is some syntactical difference in CASE for SELECT and CASE in procedures regds amit The difference between fiction and reality? Fiction has to make sense. On Sun, Aug 21, 2011 at 12:55 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] SELECT online store discount %
I am trying to write a database query that determine the customer loyalty discount for an online store. I am wondering if there is a way of doing this as 1 query, instead of multiple and using PHP to do the math? - I want to offer a 10% discount if the person is a subscriber SELECT 10 AS discount FROM `subscriber_details` WHERE `email` = '$client_email' LIMIT 1 - I also want to offer a customer loyalty discount: 10% if this is a purchase within 4 months of the previous purchase, SELECT 10 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` = DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 - OR 5% if the most recent previous purchase is between 4 months and 1 year ago. SELECT 5 AS discount FROM `paypal_payment_info_sample` WHERE `datecreation` BETWEEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) AND DATE_SUB( NOW( ) , INTERVAL 4 MONTH ) AND `paymentstatus` = 'Completed' LIMIT 1 The discounts possibilities would be: - 20% (a subscriber with a purchase within the past 4 months) - 15% (a subscriber with a purchase between 4 months and a year ago) - 10% (for being a subscriber) - 10% (for a purchase made within the past 4 months) - 5% (for a purchase made between 4 months and a year ago) Is there a way to do this all within the context of 1 query? Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info