laravel SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: 'buy_amt * 0.00097812555575316' for column 'price' at row 1 error

0
votes

I am trying to update the entire data, but I need to divide the exchange rate by the margin rate on the purchase price, and data format error occurs.

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: 'buy_amt * 0.00097812555575316' for column 'prod' at row 1 (SQL: update prod set value = 10, price = buy_amt * 0.00097812555575316 )

my code:

DB::update("update prod set value = ?, price = ?,  ",
            [
                $datas["value"],
                "buy_amt * ".(1+($datas["value"]/100))/$datas["price"],
            ]);

prod table

price value datatype DECIMAL buy_amt datatype INT

price | value | buy_amt
11    | 10    | 10
22    | 10    | 20
2
phplaravel
Its working for me. Did you try running this sql vai phpmyadmin "update prod set value = 10, price = buy_amt * 0.00097812555575316" - Rinsad Ahmed
I didn't install phpmyadmin. I want run laravel update query... - eun

2 Answers

1
votes

This piece of code

"buy_amt * ".(1+($datas["value"]/100))/$datas["price"]

will yield a string which wont fit into the price field in database which is a number

You meant this

$buy_amt * (1+($datas["value"]/100))/$datas["price"]

?

0
votes

I solved this problem...

    DB::table("prod")
        ->update([
            "value" => $datas["value"],
            "price" => DB::raw("buy_amt * ".(1+($datas["value"]/100))/$datas["price"]),
        ]),

Need to use DB :: raw to have the query calculate.