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!
Moderator: General Moderators
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Thu Apr 18, 2013 4:53 am
I need to get the total of a column in my PHP MySQL based on the recordID
i can get a total of the full column but that's not what i need
this is my query
Code: Select all
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
mysql_select_db($database_hostprop, $hostprop);
$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));
$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());
$row_rsProperty = mysql_fetch_assoc($rsProperty);
$totalRows_rsProperty = mysql_num_rows($rsProperty);
mysql_select_db($database_hostprop, $hostprop);
$query_rsSum = sprintf("SELECT SUM(utilityAmount) FROM host_editpropUtil", GetSQLValueString($colname_rsProperty, "text"));
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
this is what i am echoing out
Code: Select all
<?php
echo $row_rsSum['SUM(utilityAmount)'];
?>
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Thu Apr 18, 2013 12:28 pm
So add a WHERE condition.
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Thu Apr 18, 2013 2:12 pm
So add a WHERE condition.
yes i know i need to add a WHERE host_editpropUtil.prop_id = host_editprop.prop_id
but dont know where it should go
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Thu Apr 18, 2013 3:08 pm
Uh, not quite? The only table in the query is host_editpropUtil. There's no host_editprop. The condition should be--
You know what? Let me backtrack. What is this total that you're getting? How is it used?
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Fri Apr 19, 2013 4:26 am
Uh, not quite? The only table in the query is host_editpropUtil. There's no host_editprop.
yes i know i need to add host_editprop to the statement aswell.
What is this total that you're getting? How is it used?
i have modified the statements as suggested
Code: Select all
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
mysql_select_db($database_hostprop, $hostprop);
$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));
$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());
$row_rsProperty = mysql_fetch_assoc($rsProperty);
$totalRows_rsProperty = mysql_num_rows($rsProperty);
mysql_select_db($database_hostprop, $hostprop);//this is the working showing all totals
$query_rsSum = sprintf("SELECT SUM(utilityAmount) AS utilityTotal FROM host_editpropUtil", GetSQLValueString($colname_rsProperty, "text"));
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
and echo out
<?php
echo $row_rsSum['utilityTotal'];
?>
and am getting the TOTAL of all the values of the column
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Fri Apr 19, 2013 5:14 am
i have tried this
Code: Select all
mysql_select_db($database_hostprop, $hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount
$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id ", GetSQLValueString($colname_rsProperty, "text"));
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
but am getting the following errors
Code: Select all
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
so i take it i have done this wrong
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Fri Apr 19, 2013 5:43 am
i have added the GROUP BY BUT AM GETTING UNUSUAL RESULTS..
Code: Select all
mysql_select_db($database_hostprop, $hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount
$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id GROUP BY host_editprop.prop_id", GetSQLValueString($colname_rsProperty, "text"));
GROUP BY host_editprop.prop_id show the total for the first property no matter what property is selected
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Mon Apr 22, 2013 12:23 pm
And where in that query is the part that you restrict the results to only "what property is selected"? You're running it against everything.
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Mon Apr 22, 2013 1:09 pm
And where in that query is the part that you restrict the results to only "what property is selected"? You're running it against everything.
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Mon Apr 22, 2013 1:52 pm
But you're not actually using it.
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Mon Apr 22, 2013 2:00 pm
But you're not actually using it.
im using it where i have commented are i?
Code: Select all
mysql_select_db($database_hostprop, $hostprop);
$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id ", GetSQLValueString($colname_rsProperty, "text"));///using it here arent I ?
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Mon Apr 22, 2013 2:26 pm
You're trying to, but your query
Code: Select all
SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id
doesn't reference it.
Remember how your other query earlier has
You need something like that, with the %s, in this query too.
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Tue Apr 23, 2013 3:37 am
using
Code: Select all
$colname_rsProperty = "-1";
if (isset($_GET['recordID'])) {
$colname_rsProperty = $_GET['recordID'];
}
mysql_select_db($database_hostprop, $hostprop);
$query_rsProperty = sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID", GetSQLValueString($colname_rsProperty, "text"));
$rsProperty = mysql_query($query_rsProperty, $hostprop) or die(mysql_error());
$row_rsProperty = mysql_fetch_assoc($rsProperty);
$totalRows_rsProperty = mysql_num_rows($rsProperty);
mysql_select_db($database_hostprop, $hostprop);
$query_rsSum = sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id AND host_editprop.prop_id = %s", GetSQLValueString($colname_rsProperty, "text"));///using it here arent I ?
$rsSum = mysql_query($query_rsSum, $hostprop) or die(mysql_error());
$row_rsSum = mysql_fetch_assoc($rsSum);
$totalRows_rsSum = mysql_num_rows($rsSum);
i am still getting error
Code: Select all
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Tue Apr 23, 2013 3:51 am
Like the message says, you can't do a SUM() on one field and then regularly select other fields unless you do a GROUP BY in the query.
I would say "so add one" but I don't think the SUM is entirely appropriate anyways. You'd have to group by host_editprop and host_editpropUtil, but that's all the tables in the query and your SUM() will only sum one row at a time. So either what you're trying to do in the query is wrong or the SUM doesn't belong.
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Tue Apr 23, 2013 4:31 am
Like the message says, you can't do a SUM() on one field and then regularly select other fields unless you do a GROUP BY in the query.
I would say "so add one" but I don't think the SUM is entirely appropriate anyways. You'd have to group by host_editprop and host_editpropUtil, but that's all the tables in the query and your SUM() will only sum one row at a time. So either what you're trying to do in the query is wrong or the SUM doesn't belong.
so would you suggest removing the sum?