How to add columns and insert a total....

Clark

Simplified example of my problem.....

Have (as part of a row, there are varchar columns too) 3 columns, C1, C2, and C3.

C1 and C2 are integers.

Want C3 to contain the sum of C1 and C2 so that displayed queries can be ordered by the total (C3).

Is there some way to insert a query or formula into C3, similar to a spreadsheet, to calculate this value?

Or should I run a query selecting C1 and C2 from the table and then (possibly?) save the sum as a variable and have php loop through each row and insert this value into C3 in a seperate query?

I'm sure I'm not the first person who has wanted to do this, but haven't had a great deal of luck searching newsgroup archives at Google to find a solution.

Clark

Am doing this and getting a total for each row with php. But I need to actually insert the total into the table.

// Query table
$query = "SELECT * FROM $tablename";
$result = mysql_db_query ($db_name, $query, $link);

// Get total
$i = 0;

while ($Row = mysql_fetch_array ($result)) {
     $PIDresult = mysql_query("SELECT C1+C2 as sum FROM $tablename");
     $PIDtotal = mysql_result($PIDresult,$i,"sum"); 

// Faulty code here (listed seperately below)
     $i++;
}

This calculates the correct total for each row. Omitted code to display in an html table for brevity, but so far this works fine.

Here is the faulty code I have in my while statement, just after $PIDtotal is calculated for each row....

		$InsertTotal = "UPDATE $tablename SET C3='$PIDtotal'";
		if (!mysql_query ($InsertTotal, $link) ) {	
		die (mysql_error());
		}

Instead of inserting a different total for each row it seems to be inserting the total for the last row into each column.

xblue

you can do it in one query:
UPDATE table SET field3=field1+field2

but this does not really seem a good idea to me. I don't understand why you cannot add these values together on select, then you wouldn't have to worry about inconsistencies either.