Computing Markup Formula and updating all rows

[IbnSaeed]

Hi

I am new to Forum and this is my first post.


I am trying to edit my shopping cart.

I have three tables which I am working on

**************
Table = products
**************
Fields:
1). products_price
2). products_cost
3). products_markup


First, i want to enter One Value in products_markup(it should work globally). I have done that with the following command

------------------
[code]$db->Execute("update " . TABLE_PRODUCTS . " set
            products_markup='" . db_input($_POST['products_markup']) . "' ");[/code]
------------------

So for e.g. i want 60% markup to be applied on all the products, i run the above code.

Now this is where i am getting stuck. Now i want the same global markup which i applied above, to calculate the markup based on the values stored in my products_cost table (which is filled in with values beforehand).

So products_markup should calculate itself with products_cost and automatically update the all products_price rows. I tried with the following code but it does seem to work, the calculation is done fine but it takes the first value from products_markup and products_cost rows and calculates the markup and updates all the products_price rows from the calculation from the first row of products_markup and products_cost.

So for e.g. in the first row
products_cost = 150
products_markup = 90
it changes the products_price to 285.
And the same value 285 gets updated in all the rows of products_price.

Whereas I need the values to change according to the different values in products_cost row.



-----------------------------------------
[code]$products_cost_query = $db->Execute("select products_id, products_cost from " . TABLE_PRODUCTS . "");
    $products_id= $products_cost_query->fields['products_id'];    
    $products_cost= $products_cost_query->fields['products_cost'];    
products_cost_q = $db->Execute("select products_cost from " . TABLE_PRODUCTS . "");
                
    $products_price = ((($products_cost * $products_markup)/100) + $products_cost);

    
    $db->Execute("update " . TABLE_PRODUCTS . " set
            products_price='" . $products_price . "'  ");[/code]

-----------------------------------------


Even though the SQL statement works in MySQL Query software.


-----
[code]
update products
set products_markup=100,
products_price = (((products_cost * all_products_markup)/100) + products_cost)
;
[/code]
----


This mysql command works and it updates accordingly but when i try to implement it in the php file, it does not work as intended

[Crimpage]

I think you have the entire design wrong. I can't see why you would store the price, cost, and markup in seperate tables...

I'm not sure if you are modifying an existing eCommerce site or what, but I think your 1 table should look like:

products
-> id
-> cost
-> markup
-> price

Now with the price field, isn't always necessary as the price really is just the cost + the markup.

To help in your question:
try the sql command
"UPDATE product_price SET <price field> = product_cost.<field name> * ((product_markup.<markup field> / 100) + 1)
WHERE product_price.id = product_cost.id
AND product_cost.id = product_markup.id"

This should make sure that it updates the price with the right percentage, base on where the price, cost and markup ID's are all the same

[IbnSaeed]

Thanks for your reply

Sorry for the mistype,there are not tables . Those are fields under the table "products"

As i said, that when I run the update sql command

[code]
update products
set products_markup=100,
products_price = (((products_cost * all_products_markup)/100) + products_cost)
;
[/code]

It works perfectly but only when i run this command from phpmyadmin.

But what i need is to run it through the ecommerce site. That is where is the problem is arising.

It is computing the markup correctly but it is taking the values from the first fields of products_cost and products_markup and applying the same value from first field to all the rows in products_price


SCreenShot: This is what happens when i run the command through PHP.

[img src=\"http://www.islamicilm.com/uploads/mysql.gif\" border=\"0\" alt=\"IPB Image\" /]