CODEWITHSUNDEEP
phpmysql
user1404623
user1404623

Reputation:

Update multiple rows in Mysql

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 ?

Upvotes: 1

Views: 471

Answers (2)

Zeke Nierenberg
Zeke Nierenberg

Reputation: 2206

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] . " ;";

Upvotes: 0

jeroen
jeroen

Reputation: 91782

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.

Upvotes: 0

Related Questions