I have an Access DB which I will turn into a MYSQL db. It has a key id field which is an auto number.
The other fields are p1, p2, p3. They are currency fields. I want to add a colum called total_p. And I would like that filed to give me the total of p1,p2,p3. Is this possible?

Perhaps easier to use SUM in your query for displaying the totals dynamically - rather than having to run some UPDATE everytime you want the latest total - assuming you keep adding new records.

www.mysql.com/doc/en/Group_by_functions.html#IDX1370

Regards,
Brent

That's the problem. I don't know how to say take the sum of p1, p2, p3 and then put it in this file.

I'm saying there is no reason to put a sum field in your db - use the SUM function in your MySQL query to get the SUM of the selected fields.

Regards,
Brent

I got ya there. It's just I dont understand how to wite it.

SELECT id, p1+p2+p3 AS total_p FROM myTable ORDER BY total_p ASC

-or-

SELECT id, SUM(p1+p2+p3) AS total_p FROM myTable GROUP BY id ORDER BY total_p ASC

Regards,
Brent

i to have a this issue (not knowing the right syntax to use) i have a table that has the fields nname, authid, email, amount, and date... i would like to have a query on a php page that displays total amounts per authid which is then associated with the nname so if i have 6 rows in the table consisting of this info ....
+-------------------------------------------------------------------+
|nname | authid | email | amount | date |
| |
|Mike 3343 20.00 11/27/2006 |
|Mark 3342 10.00 11/26/2006 |
|Mike 3343 20.00 11/12/2006 |
|Mark 3342 20.00 11/22/2006 |
|Mike 3343 20.00 11/18/2006 |
|Mike 3343 20.00 11/09/2006 |
+-------------------------------------------------------------------+

it would display something like this ...

Mike (SteamID 3343) Has donated $80.00 since 11/09/2006 {this date being the oldest date of entry}
Mark (SteamID 3342) Has donated $30.00 since 11/22/2006

i would also like to have it display the total donations in a line kinda like this ...

Total donations this month $110.00 { where the sql query displays the sum of all donations for the current month}




Edited by - archangel2006 on 27 Nov 2006 18:24:57

you need to use the group sql command:

select authid, sum(amount) as sum_amount
from yourtable
group by authid

this will give you the sum of the amounts for each authid. You must query the other info seperately.

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

ok im still not quite sure what you mean .. here is what i have so far ...

this is the form that a user will use to donate
donate.php
<pre id=code><font face=courier size=2 id=code>
&lt;form action="donate_add.php" method="post"&gt;
&lt;table width="343" height="348" border="1" align="center" cellpadding="0" cellspacing="0"&gt;
&lt;tr&gt;
&lt;td width="339" height="348"&gt; &lt;p align="center"&gt;&lt;font size="4"&gt;Please Fill
in the Fields to Make a Donation.&lt;/font&gt;&lt;/p&gt;
&lt;table width="258" border="0" align="center" cellpadding="0" cellspacing="0"&gt;
&lt;tr&gt;
&lt;td width="114"&gt;&lt;div align="left"&gt;&lt;strong&gt;SteamID: &lt;/strong&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td width="144"&gt;&lt;div align="center"&gt;
&lt;input name="authid" type="text" id="authid"&gt;
&lt;/div&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;div align="left"&gt;&lt;strong&gt;Nick Name: &lt;/strong&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td&gt;&lt;div align="center"&gt;
&lt;input name="nname" type="text" id="nname"&gt;
&lt;/div&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;div align="left"&gt;&lt;strong&gt;Email Address: &lt;/strong&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td&gt;&lt;div align="center"&gt;
&lt;input name="email" type="text" id="email"&gt;
&lt;/div&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&nbsp;&lt;/td&gt;
&lt;td&gt;&nbsp;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Amount:&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;input name="amount" type="text" id="amount"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;p&gt;
&lt;label&gt; &lt;/label&gt;
&lt;center&gt;&lt;input type="hidden" value="&lt;?php
$date = mktime(0,0,0,date("m",date("d",date("Y");
echo "".date("m/d/Y", $date);
?&gt;" name="date" id="date"&gt;&lt;/center&gt;&lt;br&gt;
&lt;div align="center"&gt;&lt;input type="submit" name="Submit" value="Submit"&gt;&lt;/div&gt;
&lt;/p&gt;
&lt;br&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/form&gt;
</font id=code></pre id=code>

This is the page that adds the info to the database and allows for edit (on another page)
donate_add.php
<pre id=code><font face=courier size=2 id=code>
&lt;?php
include 'config.php';

$db = mysql_connect ($hostname, $username, $password) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db($database);

$query = "INSERT INTO $donate_table (authid,nname,email,amount,date) VALUES ('" . mysql_real_escape_string($_POST['authid']) . "','" . mysql_real_escape_string($_POST['nname']) . "','" . mysql_real_escape_string($_POST['email']) . "','" . mysql_real_escape_string($_POST['amount']) . "','" . mysql_real_escape_string($_POST['date']) . "')";
mysql_query($query) or die("Query failed " . mysql_error());

mysql_close($db);
$authid = $_REQUEST['authid'];
$nname = $_REQUEST['nname'];
$email = $_REQUEST['email'];
$amount = $_REQUEST['amount'];
$date = $_REQUEST['date'];
echo "&lt;center&gt;";
echo "&lt;p align=\"center\"&gt;&lt;font size=\"5\"&gt;Is this information Correct?&lt;/font&gt;&lt;/p&gt;";
echo "&lt;table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"&gt;";
echo "&lt;tr&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"3\"&gt;&lt;strong&gt;SteamID&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"3\"&gt;&lt;strong&gt;Nick Name&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"3\"&gt;&lt;strong&gt;Email&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"3\"&gt;&lt;strong&gt;Amount&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"3\"&gt;&lt;strong&gt;Date&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;/tr&gt;";
echo "&lt;tr&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"2\"&gt;&lt;strong&gt;$authid&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"2\"&gt;&lt;strong&gt;$nname&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"2\"&gt;&lt;strong&gt;$email&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"2\"&gt;&lt;strong&gt;$amount&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;td&gt;&lt;div align=\"center\"&gt;&lt;font size=\"2\"&gt;&lt;strong&gt;$date&lt;/strong&gt;&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;";
echo "&lt;/tr&gt;";
echo "&lt;/table&gt;";
echo "&lt;/center&gt;";
echo "&lt;form action=\"www.paypal.com/cgi-bin/webscr\" method=\"post\"&gt;";
echo "&lt;input type=\"hidden\" name=\"cmd\" value=\"_xclick\"&gt;";
echo "&lt;input type=\"hidden\" name=\"business\" value=\" \"&gt;";
echo "&lt;input type=\"hidden\" name=\"item_name\" value=\"Test Name\"&gt;";
echo "&lt;input type=\"hidden\" name=\"item_number\" value=\"Test ID\"&gt;";
echo "&lt;input type=\"hidden\" name=\"amount\" value=\"$amount\"&gt;";
echo "&lt;input type=\"hidden\" name=\"page_style\" value=\"Primary\"&gt;";
echo "&lt;input type=\"hidden\" name=\"no_shipping\" value=\"1\"&gt;";
echo "&lt;input type=\"hidden\" name=\"return\" value=\"successpay.com\"&gt;";
echo "&lt;input type=\"hidden\" name=\"cancel_return\" value=\"cancelpay.com\"&gt;";
echo "&lt;input type=\"hidden\" name=\"no_note\" value=\"1\"&gt;";
echo "&lt;input type=\"hidden\" name=\"currency_code\" value=\"USD\"&gt;";
echo "&lt;input type=\"hidden\" name=\"tax\" value=\"0\"&gt;";
echo "&lt;input type=\"hidden\" name=\"lc\" value=\"US\"&gt;";
echo "&lt;input type=\"hidden\" name=\"bn\" value=\"PP-DonationsBF\"&gt;";
echo "&lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;center&gt;";
echo "&lt;input type=\"submit\" name=\"submit\" value=\"Yes-Continue Donation\"&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
echo "&lt;/form&gt;";
echo "&lt;td&gt;&lt;form action=\"donate_edit.php?authid=$authid\" method=\"post\"&gt;&lt;input type=\"submit\" value=\"No-Go Back\"&gt;&lt;/form&gt;&lt;/td&gt;";
echo "&lt;/center&gt;";
?&gt;
</font id=code></pre id=code>

and this is page that i want the totals in another column (Total Donations)
donate_view.php
<pre id=code><font face=courier size=2 id=code>
&lt;?php
include 'config.php'; //is where my connection info is stored
include 'access.php';// is where my access levels are stored for other information i plan to use here

$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = "SELECT * FROM $donate_table";
$result = mysql_query($query) or die ("Cannot query table " . mysql_error());

?&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td align="left"&gt; &lt;table width="100%" border="1" bordercolor="#000000"&gt;
&lt;tr bgcolor="#000000"&gt;
&lt;td&gt;&lt;div align="center"&gt;&lt;font size="4"&gt;Name&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td&gt;&lt;div align="center"&gt;&lt;font size="4"&gt;Amount&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td&gt;&lt;div align="center"&gt;&lt;font size="4"&gt;Date&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;
&lt;td&gt;&lt;div align="center"&gt;&lt;font size="4"&gt;Total Donations&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr bgcolor="#666666"&gt;
&lt;td&gt;
&lt;?php
while ($row = mysql_fetch_assoc($result))
{

$nname = $row['nname'];
echo "&lt;strong&gt;$nname&lt;/strong&gt;&lt;br&gt;";
echo "&lt;/td&gt;&lt;td&gt;";
$amount = $row['amount'];
echo "&lt;strong&gt;$amount&lt;/strong&gt;&lt;br&gt;";
echo "&lt;/td&gt;&lt;td&gt;";
$date = $row['date'];
echo "&lt;strong&gt;$date&lt;/strong&gt;&lt;br&gt;";
$total = $row['total'];
echo "&lt;strong&gt;$total&lt;/strong&gt;&lt;br&gt;";
}
mysql_free_result($result);
?&gt;
</font id=code></pre id=code>

any ideas on how to fix this in my code ?