jimmyp3016 Posted April 12, 2007 Share Posted April 12, 2007 Hey Guys, I have this statement that shows who made affiliate commisions during the month I choose. $sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN ("; $sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'"; $sql .= ") GROUP BY id"; I run it and it runs correctly except for the fact that if an affiliate makes a couple sales from the same person, its not showing. Its only showing one sale. Is this because this statment is only pulling out unique id's? If so, how can i fix it? Thank you in advance. -Jimmy Quote Link to comment Share on other sites More sharing options...
Glyde Posted April 12, 2007 Share Posted April 12, 2007 Do a SORT BY id DESC instead of GROUP BY id, unless you absolutely need a GROUP. Otherwise, it should work. Quote Link to comment Share on other sites More sharing options...
jimmyp3016 Posted April 12, 2007 Author Share Posted April 12, 2007 Hey Glyde, I just tried that and i got a nasty... Query failed to execute correctly: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SORT BY id DESC' at line 1 It didnt like it. Any suggestions? Quote Link to comment Share on other sites More sharing options...
per1os Posted April 12, 2007 Share Posted April 12, 2007 Glyde was confused. Replace SORY BY with ORDER BY Quote Link to comment Share on other sites More sharing options...
jimmyp3016 Posted April 12, 2007 Author Share Posted April 12, 2007 I changed it to Order by and got this "Query failed to execute correctly: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause" Here is my whole script... <?php require_once( "../database.cls.php" ); require_once( "../functions.inc.php" ); adminPage(); if ( $_REQUEST['masspay'] ) { $startDate = $_REQUEST['year']."-".$_REQUEST['month']."-01 00:00:00"; $endDate = $_REQUEST['year']."-".$_REQUEST['month']."-".date( "t", mktime( 0, 0, 0, $_REQUEST['month'], 1, 2000 ) )." 23:59:59"; $db = new MySQL( DB_HOST, DB_USER, DB_PASS, DB_NAME ); $sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN ("; $sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'"; $sql .= ") ORDER BY id DESC"; $q = $db->query( $sql ); if ( $q->numRows() > 0 ) { $row = $q->getNext( "object" ); if ( $row->payPal ) $mp .= $row->payPal."\t".($row->referrals*AFFILIATE_COMMISSION_2)."\t".PRODUCT_CURRENCY."\t\tReferral commissions payment."; } header( "Content-Disposition: attachment; filename=".$_REQUEST['month']."-".$_REQUEST['year']."-commissions.xls" ); header( "Content-Type: application/vnd.ms-excel" ); echo $mp; exit(); } echo file_get_contents( "head.txt" ); echo doAdminNavBar(); ?> <h3>Web 2.0 Affiliate commissions</h3> <p>Show how much your users have earned in affiliate commission for the Web 2.0 Service.</p> <form method="get" action="service.php"> <p>Show monthly statement: <select name="month"> <?php for ($i = 1; $i < 13; $i++) echo "<option value=\"$i\">$i</option>\n"; ?></select> <select name="year"> <?php for ($i = 2006; $i < 2051; $i++) echo "<option value=\"$i\">$i</option>\n"; ?></select> <input type="submit" value="Go" /></p></form> <?php if ( $_REQUEST['month'] && $_REQUEST['year'] ) { $startDate = $_REQUEST['year']."-".$_REQUEST['month']."-01 00:00:00"; $endDate = $_REQUEST['year']."-".$_REQUEST['month']."-".date( "t", mktime( 0, 0, 0, $_REQUEST['month'], 1, 2000 ) )." 23:59:59"; ?> <p>Showing statement for <?php echo $startDate." - ".$endDate; ?>. <a href="service.php?masspay=1&month=<?php echo $_REQUEST['month']; ?>&year=<?php echo $_REQUEST['year']; ?>">Download PayPal 'mass-pay' file</a>.</p> <table cellpadding="5px" style="background:#eee;width:100%;"> <tr style="font-weight:bold;"><td>Name</td><td>Referrals</td><td>Total earned</td><td>Payment details</td></tr> <?php $db = new MySQL( DB_HOST, DB_USER, DB_PASS, DB_NAME ); $sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN ("; $sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'"; $sql .= ") ORDER BY id DESC"; $q = $db->query( $sql ); if ( $q->numRows() > 0 ) { $row = $q->getNext( "object" ); if ( $row->payPal ) $details = "PayPal: ".$row->payPal; else { $details = "Cheque: ".$row->cheques."<br />"; if ( $row->address1 ) $details .= $row->address1."<br />"; if ( $row->address2 ) $details .= $row->address2."<br />"; if ( $row->city ) $details .= $row->city."<br />"; if ( $row->state ) $details .= $row->state."<br />"; if ( $row->zip ) $details .= $row->zip."<br />"; if ( $row->country ) $details .= $row->country."<br />"; } $userLink = "<a href=\"users.php?email=".$row->email."\">".$row->firstName." ".$row->lastName."</a>"; echo "<tr><td>".$userLink."</td><td>".$row->referrals."</td><td>\$".($row->referrals*AFFILIATE_COMMISSION_2)."</td><td>".$details."</td></tr>\n"; } ?> </table> <?php } echo file_get_contents( "foot.txt" ); ?> Quote Link to comment Share on other sites More sharing options...
jimmyp3016 Posted April 13, 2007 Author Share Posted April 13, 2007 The script is only showing the last affiliate who made a sale and thats it, even though there are 2 different affiliates who made a sale. It should be showing the 2 rows but it doesnt. Does it have anything to do with this line? Is this line set to only echo one row? echo "<tr><td>".$userLink."</td><td>".$row->referrals."</td><td>\$".($row->referrals*AFFILIATE_COMMISSION_2)."</td><td>".$details."</td></tr>\n"; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.