Arithmatic

[bhavin_85]

hey guys

 

Ok i need to do the following in php, ive tried and got the wrong answer 

 

When editing a price the number of points change (points are calculated as 10% of price), you only edit if they were entered wrong

 

I have one table that keep a record of everytime the price changes and ive managed to change that and update the points perfectly, there is another page which just has a cumulative total of the points. How would I update this cumulative total?

 

heres an example:

old price is 2300, thats an error and new price is 2200, so the points change from 230 to 220...that makes a negative 10 point difference

then i take that 10 points and subtract it from the cumulative total

but waht happens if the points are increased ie

goes from 2300 to 2400, points = 230 to 240 thus 10 point diff so add 10 to the cumulative total

 

ok i know thats a really long winded explination but can some1 pleaseeeeeee help ive been at this 4 ages and dunno where im goin wrong

 

$invoice_item_id=$_POST['invoice_item_id']; 
$cust_id=$_POST['cust_id'];
include'../../config.php';
$price=$_POST['price'];
$pointchange = $price * 0.1;
$sql = "UPDATE invoice_items SET description = '".$_POST['description']."',price = '".$_POST['price']."',weight = '".$_POST['weight']."',points = '$pointchange' WHERE invoice_item_id = '$invoice_item_id'";
mysql_query($sql) or die ("Couldnt execute $sql: " . mysql_error()); 
$sql2="SELECT * FROM points_history WHERE invoice_item_id='$invoice_item_id'";
$query2=mysql_query($sql2);
$row2=mysql_fetch_assoc($query2);
$oldpoints=$row2['points'];
$sql3="UPDATE points_history SET points='$pointchange' WHERE invoice_item_id='$invoice_item_id'";
$query3=mysql_query($sql3);
$newpoints=$oldpoints - $pointchange;
$sql4="SELECT points FROM points WHERE cust_id='$cust_id'";
$query4=mysql_query($sql4);
$row4=mysql_fetch_assoc($query4);
$tobechangedpoints=$row4['points'];
$newestpoints = $tobechangedpoints + $newpoints;
$sql5="UPDATE points SET points='$newestpoints' WHERE cust_id='$cust_id'";
$query5=mysql_query($sql5);

[Barand]

What is your table structure?

What information is in the form?

Where do new price and old price come from?

In which table/field should the updated cumulative be stored?

Have you got some sample data with an example in the context of the form and your tables?

[bhavin_85]

Table Structure:

points: points_id, cust_id, points(int) - this table stores the cumulative total of points

points_history: history_id, cust_id, points(int), purchase_date, invoice_item_id - this stores the history of earning/spending points

invoice_items: stores details about the items

The form has:

Invoice_Item ID - used to find a specific item in an invoice

Description

weight

price - when this price is updated it should change the value stored in the points_history table (already done by $sql3) and take away or add the difference to cumulative balance of the points in the points table.

 

New price comes from that from above form.

The cumulative total should be stored in the points table in the points field

 

Example:

1. user selects invoice_item_id to be updated, form pulls up desc, wight and price of that invoice_item_id

2. user enters updated price and clicks save button

3. system updates invoice_items table with the data from the form

4. then selects the points value in points_history for that item, the calculates the points made when price is updated -

oldpoints - pointschange = newpoints....newpoitns holds the difference

5. inserts this updated value into the points_history table

***all the above works fine***

6. then selects the cumulative points in the poitns table

7. calculates the change in points by adding/subtracting the current points to the value of newpoints (line4)

8. inserts the new value into the database

 

thats how it works

[Barand]

The bit I find confusing is that you never pick up the price that is originally in the invoice item record before you update it. The points change would be (new price - original price) * 0.1

[bhavin_85]

its ok i fixed it there was an error with 1 of the symbols i was using

 

cheers