Add All Together - PHP & MySQL

AdWarm · March 16, 2011, 6:55pm

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!

AnthonySterling · March 16, 2011, 7:08pm

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

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!

AnthonySterling · March 16, 2011, 7:53pm

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

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?

AnthonySterling · March 16, 2011, 8:45pm


<?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

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();
?>

AnthonySterling · March 16, 2011, 9:03pm

Check the query, yours is wrong.

AdWarm · March 16, 2011, 9:16pm

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.

AnthonySterling · March 16, 2011, 9:24pm

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


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.

AnthonySterling · March 16, 2011, 9:37pm

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

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!

AnthonySterling · March 16, 2011, 9:42pm

Not worries.

Don’t be a stranger.

Chin chin.

Anthony.