AdWarm
March 16, 2011, 6:55pm
1
Hi Guys,
I need some advice please. In my database table called ‘affleads’ i have a row called Payout. Now below is the following MySQL code i am using on my PHP script:
$query = "SELECT Payout FROM affleads WHERE AffID ='$affid'";
Now lets say there are 6 records matching the SQL query as the following:
2.50
2.34
1.85
2.74
1.00
2.07
What PHP code would i use in order to get the values of Payout and then Add them all together and save the value to a variable?
Any help would be great and thank you in advance.
Thanks!
Hey, welcome to SitePoint.
Something like the following should suffice:-
<?php
$res = mysql_query(sprintf(
"SELECT SUM(Payout) AS 'total' FROM affleads WHERE AffID = '%s';",
mysql_real_escape_string($affid)
));
$row = mysql_fetch_assoc($res);
echo $row['total'];
AdWarm
March 16, 2011, 7:31pm
3
Hi,
Thank you for your reply and help, although I’ve just been thinking and have another problem. Below is what table looks like:
What I’d like to do is do it so my PHP script will output the total number of records for each Program Name and output the Total Payout. So for example, going off the records above the output would be the following:
Nuffield Health - 3 - £7.50
I was thinking of using the DISTINCT method in SQL but then relised it wouldnt work to how i want it. Any help please guys would be great.
Thank you very much guys!
Ah, in that case, your query would be…
SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM
affleads
GROUP BY
ProgramName
AdWarm
March 16, 2011, 8:10pm
5
Hi Anthony,
Thank you again for your help, i have tested the SQL in PHPMYADMIN and it executes fine and i will now add it to my PHP script.
I’m guessing i no longer need the following now?
$total = 0;
while ($row = mysql_fetch_object($result)) {
$total += $row->Payout;
}
Also this is how i normally output data from my SQL:
EXAMPLE:
$i=0;
while ($i < $num) {
$bannerid=mysql_result($result,$i,"BannerID");
$type=mysql_result($result,$i,"Type");
$code=mysql_result($result,$i,"Code");
$bannerurl=mysql_result($result,$i,"BannerURL");
<? echo $code; ?>
<?
$i++;
}
mysql_close();
?>
Do i do the same using the SQL you have provided or will it be different now?
<?php
$sql = "
SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM
affleads
WHERE
AffID = '%s'
GROUP BY
ProgramName;
";
$res = mysql_query(sprintf(
$sql,
mysql_real_escape_string($affid)
));
while($row = mysql_fetch_assoc($res)){
printf(
'%s - %s - %s<br />',
$row['name'],
$row['count'],
$row['payout']
);
}
AdWarm
March 16, 2011, 9:00pm
7
Hi Anthony,
Thank you again for your help, but it doesnt seem to be working, nothing is being outputted when i use your code:
$username="REMOVED";
$password="REMOVED";
$database="REMOVED";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Oops theres an error, our highly trained monkeys have been notified.");
$query = "SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM affleads WHERE AffID='$s' GROUP BY ProgramName";
mysql_query($query);
$result = mysql_query(sprintf($query, mysql_real_escape_string($affid)));
while($row = mysql_fetch_assoc($result)){
printf(
'%s - %s - %s<br />',
$row['name'],
$row['count'],
$row['payout']
);
}
mysql_close();
?>
Check the query, yours is wrong.
AdWarm
March 16, 2011, 9:16pm
9
I’ve gone through it and ensured everything is the same (except the variable called $res which in mine is $result and $sql which in mine is $query)
$username="tmlmedia_test";
$password="mnsmns";
$database="tmlmedia_test";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Oops theres an error, our highly trained monkeys have been notified.");
$query = "SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM
affleads
WHERE
AffID = '%s'
GROUP BY
ProgramName;
";
//mysql_query($query);
$result = mysql_query(sprintf(
$query,
mysql_real_escape_string($affid)
));
while($row = mysql_fetch_assoc($result)){
printf(
'%s - %s - %s<br />',
$row['name'],
$row['count'],
$row['payout']
);
}
Still no output from the code
One thing I’m not sure about is the variable used in the SQL which you have put in:
WHERE
AffID = ‘%s’
Whats %s ?
Thanks again for your help.
OK, replace localhost, username, password and database with the required data.
<?php
error_reporting(-1);
ini_set('display_errors', true);
$con = mysql_connect(
'localhost',
'username',
'password'
);
if(false === is_resource($con)){
echo mysql_error();
exit;
}
if(false === mysql_select_db('database', $con)){
echo mysql_error();
exit;
}
$sql = "
SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM
affleads
WHERE
AffID = '%s'
GROUP BY
ProgramName;
";
$res = mysql_query(sprintf(
$sql,
mysql_real_escape_string($affid)
));
if(false === is_resource($res)){
echo mysql_error();
exit;
}
while($row = mysql_fetch_assoc($res)){
printf(
'%s - %s - %s<br />',
$row['name'],
$row['count'],
$row['payout']
);
}
%s is a placeholder, see [fphp]sprintf[/fphp] for more.
AdWarm
March 16, 2011, 9:33pm
11
Strict Standards: main() [function.main]: It is not safe to rely on the system's timezone settings. Please use the date.timezone setting, the TZ environment variable or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/Chicago' for 'CDT/-5.0/DST' instead in /home/REMOVED/public_html/REMOVED/REMOVED/stats.php on line 103
Notice: Undefined variable: affid in /home/REMOVED/public_html/REMOVED/REMOVED/stats.php on line 103
Line 103 is:
mysql_real_escape_string($affid)
The reason its line 103 is i have all my code still in the php file, iv just commented all my code out.
That’s OK.
Where are you getting $affid from? If at all…
Actually, we’ll skip that for now. Try this…
<?php
error_reporting(-1);
ini_set('display_errors', true);
$con = mysql_connect(
'localhost',
'username',
'password'
);
if(false === is_resource($con)){
echo mysql_error();
exit;
}
if(false === mysql_select_db('database', $con)){
echo mysql_error();
exit;
}
$sql = "
SELECT
ProgramName AS 'name'
, COUNT(*) AS 'count'
, SUM(Payout) AS 'payout'
FROM
affleads
GROUP BY
ProgramName;
";
$res = mysql_query($sql);
if(false === is_resource($res)){
echo mysql_error();
exit;
}
while($row = mysql_fetch_assoc($res)){
printf(
'%s - %s - %s<br />',
$row['name'],
$row['count'],
$row['payout']
);
}
AdWarm
March 16, 2011, 9:38pm
13
Oh god, sorry i know exactly why, i commented out the $affid variable.
Oooops sorry about that, i hate making stupid mistakes. Thank you for your help, you have been a big help!
Once again thanks!