Jump to content


This topic is now archived and is closed to further replies.


Cleaning my code

Recommended Posts


I built this little code, but I am not happy with the number of queries and loops

$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"
."<a href='".$_SERVER['PHP_SELF']."?paid=".$row2["aff"]."'>Click here when you have Paid</a><br /><br />\n";


Now let me explain a little about the tables

table 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 table
The table goes something like this

id_num (int50) autoincrement Primary
aff (int50)
amount (double 5,2)
sold_on (DateTime)
paid(enum 'y','n'

Table affiliates:
This is the user information basicly
id_num (int50) auto increment, primary (This is the ID number used aff_sales.aff)

I am not going through info on each one, there all basic and not related.


The 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 form
Then 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

Share this post

Link to post
Share on other sites
Single query should do it
$sql = "SELECT a.name, a.email, SUM(s.amount) as total_sales
FROM affiliates a INNER JOIN aff_sales s
ON a.id_num = s.aff
WHERE s.paid = 'n'
GROUP BY a.name, a.email";

Then loop thriough the results of this query

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.