Jump to content


Photo

Cleaning my code


  • Please log in to reply
2 replies to this topic

#1 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 09 August 2006 - 09:37 PM

Hey

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

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

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)
name
email
url
country
pwd
reg_on
level

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

So


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


Tell me the problem, I will try tell you the solution

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 August 2006 - 11:09 PM

Single query should do it
<?php
$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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 09 August 2006 - 11:47 PM

can I do field and SUM in one query, wicked
Tell me the problem, I will try tell you the solution




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users