...

View Full Version : help with calculating figures



adammc
09-01-2006, 06: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);

echo "<td align=\"center\" class=\"row1\"><span class=\"link\">$num1</span></td>";

$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.

adammc
09-01-2006, 07: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;

echo "<td align=\"center\" class=\"row1\"><span class=\"link\">$amount</span></td>";

$month++;
}
}



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

marek_mar
09-01-2006, 08: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%'



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum