...

# help with calculating figures

09-01-2006, 07:54 AM
Hi Guys,

Can anyone possibly help with with this?

I have designed a basic affiliate program script. Affiliate Commissions are a flat fee of \$50. I am trying to code the page that spits out the affiliate earnings for each month.

When we have verified the order we use a script to set a column (status) in the order table to 'approved', I am not currently putting in the commission amount (\$50) into the Db as I was hoping to just count the rows where status = 'approved' and do some math.

Only prob is, I dont know how to do it !!

I have been trying to use this code below as a starter to build my query, at the moment its just counting the rows:

\$month = 1;
while(\$month < 13){

while(\$month < 13){
\$mon = \$month;
if (\$month < 10)
\$mon = "0".\$month;

// make the query to get the postings
\$query1 = "SELECT Affiliate_ID, Status FROM orders WHERE Affiliate_ID=\$_SESSION[affiliate_id] AND Status=approved";

// run the query
\$result1 = @mysql_query (\$query1) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . \$query1 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

// get the number of rows
\$num1 = mysql_num_rows(\$result1);

\$month++;
}
}

For example... If there were 2 entries in the orders table for affiliate_id 1 that had the value of 'approved' in the status column. I would like to calculate and echo 2x\$50

Any help would be greatly appreciated.

09-01-2006, 08:12 AM
Whoo Hoo, I worked it out!

\$month = 1;
while(\$month < 13){

while(\$month < 13){
\$mon = \$month;
if (\$month < 10)
\$mon = "0".\$month;

// make the query to get the postings
\$query1 = "SELECT Affiliate_ID, Status FROM orders WHERE Affiliate_ID='web\$_SESSION[affiliate_id]' AND Order_Result='approved' AND Inquiry_Date LIKE '2006-\$mon%'";

// run the query
\$result1 = @mysql_query (\$query1) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . \$query1 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

// get the number of rows
\$num1 = mysql_num_rows(\$result1);

\$amount= \$num1 * 50;

\$month++;
}
}

Is my query correct? I mean can I use 2 'AND' Statements?

marek_mar
09-01-2006, 09:50 AM
You could do the math in the query

SELECT COUNT(Affiliate_ID) * 50 AS amount FROM orders WHERE Affiliate_ID='web\$_SESSION[affiliate_id]' AND Order_Result='approved' AND Inquiry_Date LIKE '2006-\$mon%'