[SOLVED] Help with query

[dreamwest]

I need to recalculate a whole column based on another columns row change

 

Database structure:

 

id | cost | total

 

1 | 20    | 20

2 | 40    | 60

3 | 10    | 70

4 | 70    | 140

5 | 60    | 200

 

etc...

 

Say i use a form to edit a value from cost like this (in bold)

 

id | cost | total

 

1 | 20    | 20

2 | 40    | 60

3 | 20   | 70  <-----New value from 10 to 20 in cost

4 | 70    | 140

5 | 60    | 200

 

etc...

 

How can i recalculate the rest of the total column so it ends up like this:

 

 

id | cost | total

 

1 | 20    | 20

2 | 40    | 60

3 | 20    | 80 

4 | 70    | 150

5 | 60    | 210

 

 

[Yesideez]

I don't feel there's any need to have that column to be honest.

 

Let's say you pull the data resulting in row 4 getting pulled.

 

We have ID of 4 and COST of 70.

 

We can run a query to total up everything previously.

 

SELECT SUM(cost) AS total FROM table WHERE id<=4

[dreamwest]

I don't feel there's any need to have that column to be honest.

 

Let's say you pull the data resulting in row 4 getting pulled.

 

We have ID of 4 and COST of 70.

 

We can run a query to total up everything previously.

 

SELECT SUM(cost) AS total FROM table WHERE id<=4

 

Your right, Thanks.

 

I didnt think of doing it this way, this will get rid of half my code

[FezEvils]

hello there...

what happen to data id 4 and 5?

another way is you just create sql update statement and add it with your form value

[dreamwest]

Ok i rewrote the query to get the total of cost, but this is my first time using sum() and it doesnt seem to be working:

 

$result = mysql_query("SELECT SUM(cost) AS total FROM table ") or die(mysql_error());  

$row = mysql_fetch_array( $result );
$total =   number_format($row['SUM(cost)']/100,2);

echo $total;

[FezEvils]

Ok i rewrote the query to get the total of cost, but this is my first time using sum() and it doesnt seem to be working:

 

$result = mysql_query("SELECT SUM(cost) AS total FROM table ") or die(mysql_error());  

$row = mysql_fetch_array( $result );
$total =   number_format($row['SUM(cost)']/100,2);

echo $total;

 

edit this:

$total = number_format($row['total']/100,2);

//because you have set sum(cost) as total..

[Yesideez]

I wouldn't just "copy & paste" the query I posted into your code and expect it to work straight away.

 

I didn't know what your tables were called so I used "table" - this needs to be replaced with the name of your table.

 

$total = number_format(($row['total']/100),2);

 

Have added brackets to the value to make sure that gets done first (the division) before it gets passed to number_format()

[FezEvils]

ya, Yesideez right..

it's called BODMAS

www.mathsisfun.com/operation-order-bodmas.html

[Yesideez]

ya, Yesideez right..

it's called BODMAS

www.mathsisfun.com/operation-order-bodmas.html

You got any idea how long it's been since I have seen or heard anyone say that word?

 

It's been many, many YEARS!

[FezEvils]

hehe..me too... ;D

[dreamwest]

I wouldn't just "copy & paste" the query I posted into your code and expect it to work straight away.

 

I didn't know what your tables were called so I used "table" - this needs to be replaced with the name of your table.

 

$total = number_format(($row['total']/100),2);

 

Have added brackets to the value to make sure that gets done first (the division) before it gets passed to number_format()

 

Woops - i didnt notice the AS total. I figured it out

 

Thanks

[Yesideez]

btw, AS is handy to rename a column or data returned. Particularly handy when you're performing a mathematical operation and want to give the value an index name to reference it by, just like we have with our SUM value - calling it "total"