Unique Rows Help

[jimmyp3016]

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

[Glyde]

Do a SORT BY id DESC instead of GROUP BY id, unless you absolutely need a GROUP.  Otherwise, it should work.

[jimmyp3016]

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?

[per1os]

Glyde was confused. Replace SORY BY with ORDER BY

[jimmyp3016]

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" );

?>

 

[jimmyp3016]

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";