Update multiple rows in Mysql

Question

I have a sql table in my database "accounts" whose structure looks like this.

 ItemNo  CostPrice  SellingPrice  Discount(10%)  Price(INR)  
  100       $100      $150            $135         Rs.7425   
  101       $200      $250            $225         Rs.10395  
  102       $150      $200            $180         Rs.7920   
  103       $500      $550            $495         Rs.25245

Here each column is dependent on the previous one.

SellingPrice = CostPrice + 50

Discount = SellingPrice + (0.1 * SellingPrice)

Price(INR) = SellingPrice * ($_Conversion_rate_to_Rs)

I need to calculate the SellingPrice and update it in the table. Then I calculate the Discount and update the table Then I calculate the Price(INR) and update the table.

I am trying the following code but it doesnt seem to be working.

$i = 0;
foreach($item_no as $item ){
mysql_query("UPDATE accounts SET SellingPrice = CostPrice + 50 WHERE item_no = '$item[$i]'") or die(mysql_error());
++$i;
}

Now after updating the SellingPrice I need to calculate the discount and update the database.

$i = 0;
foreach($item_no as $item ){
mysql_query("UPDATE accounts SET Discount = SellingPrice + (0.1 * SellingPrice) WHERE item_no = '$item[$i]'") or die(mysql_error());
++$i;
}

And so on..

My doubt may be quite simple but I tried a lot and couldn find a correct solution.

Can I know my mistakes and the correct solution ?

Zeke Nierenberg · Accepted Answer · 2012-08-19 16:34:44Z

EDIT!

The way I edit multiple fields is with a custom update function. The function takes an associative array of "field"=>"new value". Here's the function:

function update($table,$values,$conditions){

    $query = "UPDATE `" . $table . "` SET ";
    foreach($values as $key => $value){
        if($key == "password")
            $query .= "`password` = PASSWORD('" . $mysqli->real_escape_string($value) . "'),";
        else if(is_numeric($value))
            $query .= "`" . $mysqli->real_escape_string($key) . "` = " . $mysqli->real_escape_string($value) . ",";
        else
            $query .= "`" . $mysqli->real_escape_string($key) . "` = '" . sanitize($value) . "',";
    }
    $query = substr($query,0,-1);

    if(!is_string($conditions)){
        $conditionStr = "";
        foreach($conditions as $key => $value){

            if($key == "password")
                $conditionStr .= "`password` = PASSWORD('" . $mysqli->real_escape_string($value) . "') AND ";
            else if(is_numeric($value))
                $conditionStr .= "`" . $mysqli->real_escape_string($key) . "` = " . $mysqli->real_escape_string($value) . " AND ";
            else
                $conditionStr .= "`" . $mysqli->real_escape_string($key) . "` = '" . sanitize($value) . "' AND ";
        }
        $conditionStr .= substr($conditionStr,0,-4);
        $conditions = $conditionStr;
    }

    $query .= " WHERE " . $conditions . " ;";
    $mysqli->query($query);
}

You use it like this: update("theTable",array("username"=>"whateverTheNewNameIs"),array("userID"=>55));

That updates userID=55 and makes the username something new. You can just expand the second array to update more things.

I think you a problem in your updates and your where clause

I'd make a variable for your new cost price,

$newSellingPrice = $item[$i]['sellingPrice'] + 50;

In your update you can use $newSellingPrice

Then in your where, you have a typo, you didn't use the $ to get the item array.

You'll need to leave the query string if you're using square bracket notation

"... WHERE item_no = " . $item[$i] . " ;";

I did this but my table is not just the three columns as I stated above.. It consists of 30 columns.. So instead of using one SQL query for one field, is there any way to use ONE SQL STATEMENT to update all the values stated above?

jeroen · Accepted Answer · 2012-08-19 16:26:09Z

0

To answer your question, if you want to update all fields in the table, there is no need to add a WHERE condition, you can just use:

UPDATE accounts SET SellingPrice = CostPrice + 50

and run only one query in total instead of one query per item.

However, all columns that depend on other columns really don't need to be separate columns, you would be a lot better of with a table with just 2 columns and calculate the prices as you need them. Now you have a lot of duplicate information as the cost price and the multipliers are the only values you really need to store.

edited Aug 19, 2012 at 16:26

answered Aug 19, 2012 at 16:19

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

2 Comments

user1404623 Over a year ago

Thanku Sir ! That was wonderful ! I worked!! I didnt understand your last statement.. Did u mean I can update my table in one UPDATE statement only using the multipliers ?? How ?

jeroen Over a year ago

@NealCaffrey No, I mean that you should not store the last 3 columns in your database but just calculate them when you need them. You would only need to store the numbers (50, 0.1, conversion rate) somewhere else or in a separate table.

Collectives™ on Stack Overflow

Update multiple rows in Mysql

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related