onlyican Posted August 9, 2006 Share Posted August 9, 2006 HeyI built this little code, but I am not happy with the number of queries and loops[code]<?php$query2 = "SELECT * FROM aff_sales WHERE paid = 'n' GROUP BY aff";$result2 = mysql_query($query2); while($row2 = mysql_fetch_assoc($result2)){ $payments = ""; $query5 = "SELECT id_num FROM aff_sales WHERE paid = 'n' and aff = '".$row2["aff"]."'"; $result5 = mysql_query($query5); while($row5 = mysql_fetch_assoc($result5)){ $payments .= $row5["id_num"]."_"; } $total_pay = mysql_result(mysql_query("SELECT SUM(amount) FROM aff_sales WHERE paid = 'n' AND aff = '".$row2["aff"]."'"),0); echo "For Aff id ".$row2["aff"]."<br />\n"; $query4 = "SELECT * FROM affiliates WHERE id_num = '".$row2["aff"]."'"; $result4 = mysql_query($query4); while($row4 = mysql_fetch_assoc($result4)){ $name = $row4["name"]; $email_to = $row4["email"]; } echo $name." is owed \$".$total_pay." this much money, Click pay to pay them, or update if you have paid them.<br />"; echo "<form method='post' action='confirm.php'>\n" ."<input type='hidden' name='email' value='".$email_to."' />\n" ."<input type='hidden' name='amount' value='".$total_pay."' />\n" ."<input type='hidden' name='payments' value='".$payments."' />\n" ."<input type='submit' value='Pay' />\n" ."</form>\n" ."<a href='".$_SERVER['PHP_SELF']."?paid=".$row2["aff"]."'>Click here when you have Paid</a><br /><br />\n"; }?>[/code]Now let me explain a little about the tablestable aff_sales:When ever someone buys something, and there is an affiliate behind this (using cookies OR IP_add)The info of there comission is added to the tableThe table goes something like thisid_num (int50) autoincrement Primaryaff (int50)amount (double 5,2)sold_on (DateTime)paid(enum 'y','n'Table affiliates:This is the user information basiclyid_num (int50) auto increment, primary (This is the ID number used aff_sales.aff)nameemailurlcountrypwdreg_onlevelI am not going through info on each one, there all basic and not related.SoThe goal of the page is to show the Admin when he logs in, who is owed money, and how much.He then clicks a button, and this will open a new page going to paypal Send money, completing the formThen a button will show saying click her when payment is successful.What I want to do is make the code cleaner, less mysql calls, and less loops.I am thinking if there are 20 people who is owed money, thats a lot of MySQL calls and Loops Quote Link to comment Share on other sites More sharing options...
Barand Posted August 9, 2006 Share Posted August 9, 2006 Single query should do it[code]<?php$sql = "SELECT a.name, a.email, SUM(s.amount) as total_salesFROM affiliates a INNER JOIN aff_sales s ON a.id_num = s.affWHERE s.paid = 'n'GROUP BY a.name, a.email";?>[/code]Then loop thriough the results of this query Quote Link to comment Share on other sites More sharing options...
onlyican Posted August 9, 2006 Author Share Posted August 9, 2006 can I do field and SUM in one query, wicked 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.