PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
$result = mysql_query ("
SELECT * FROM matterusersrights
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode
WHERE matterusersrights.userid = '" .$cookieid."' GROUP BY persondealing");
while ($row = mysql_fetch_object($result)) {
echo "<br/>
<b> $row->persondealing</b>
$row->estimate<br/>
$row->total<br/>
";
}
mysql_free_result($result);
mysql_close($sqlconn);
Hi all - I really seem to come up with problems that seem impossible!
The above code is trying to extract various bits of data, but mainly the name from the field "persondealing" of which there could be several of the same person, and a total of the "total" field.
For instance:
Joe Bloggs, £2,500
Billy Bob, £1000
Joe Bloggs, £1,500
I want to see as a result:
Joe Bloggs, £4,000
Billy Bob, £1,000.
I can do a groupby, but that doesn't total up the amount.
Can some genius help?
Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis wrote:The above code is trying to extract various bits of data, but mainly the name from the field "persondealing" of which there could be several of the same person, and a total of the "total" field.
You must GROUP BY something which is really unique - e.g. ID field in your persons table. There is a huge chance for having two persons with the same names
simonmlewis wrote:For instance:
Joe Bloggs, £2,500
Billy Bob, £1000
Joe Bloggs, £1,500
I want to see as a result:
Joe Bloggs, £4,000
Billy Bob, £1,000.
I can do a groupby, but that doesn't total up the amount.
Can some genius help?
Simon
[sql]SELECT *, sum(money)[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
I can't tell you that - I don't know what's your DB design.
You just need to add SUM(field_to sum) in you SELECT clause.
To make it easier to reference it use alias:
[sql]SUM(field_to sum) AS amount_per_person[/sql]
and you'll be able to echo it by using:
I see you work with real persons and real money data - so it's very important that your code contains no errors. So, I would advice you to read some SQL manuals - http://www.w3schools.com/sql/default.asp
There are 10 types of people in this world, those who understand binary and those who don't
$result = mysql_query ("
SELECT * FROM matterusersrights
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode
WHERE matterusersrights.userid = '" .$cookieid."' GROUP BY persondealing, SUM(total) AS amount_per_person");
while ($row = mysql_fetch_object($result)) {
echo "<br/>
<b> $row->persondealing</b>
$row->estimate<br/>
$row->total<br/>";
echo $row['amount_per_person'];
}
mysql_free_result($result);
mysql_close($sqlconn);
This fails with a 'fetch_object' error, and I think it's because of the "SUM" section as it's on that line.
I have to select various columns, not just SUM.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
SELECT persondealing,
SUM(total) AS amount_per_person
FROM matterusersrights
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode
WHERE matterusersrights.userid = '" .$cookieid."'
GROUP BY persondealing
And you'll need to include every selected column (other than your sum) in the GROUP BY expression