Re: SQL question

At 9:54 PM -0600 1/25/10, Skip Evans wrote:

Hey all, I have an SQL query that's stumping me. I have two date variables, $start and $end that are in mm/dd/yyyy format and two database fields, start_date and no_donations. The start date is mm/dd/yyyy format and no_donations is an integer that represents the number of months from start_date that donations will be made. So if start date is say 02/01/2010 and no_dations is 4 then donations will be made four times from the start date for four months. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. But I'm pretty stumped. How can I convert start_date + no_donations in the database to the date when the last donation will take place so I'll now if the donations fall between $start and $end? Any suggestions would be very help and appreciated, Skip -- ==================================== Skip Evans

Skip: Here's a snip-it of code from one of my projects: $qry = "SELECT SUM(amount) AS subtotal, COUNT(*) AS num FROM transaction WHERE product_type = 'video' AND UNIX_TIMESTAMP(transtime) > " . strtotime($startd) . " AND UNIX_TIMESTAMP(transtime) < " . strtotime($endd) . " AND is_charged = 1 AND notes = 'Approved' AND is_refunded = 0 AND transnum NOT LIKE 'TEST-PNREF' AND product_id LIKE '$key' "; $db2->select($qry); while ($db2->readrow()) { $rev = $db2->data["subtotal"]; // this is the total amount collected for the sale $num = $db2->data["num"]; // this is the number of this type of sale } Clearly, you don't need everything there, but the timestamp notation will give you better insight into how to use dates in your query. Cheers, tedd -- ------- http://sperling.com http://ancientstones.com http://earthstones.com