I have a laravel project that for some reasons keeps on rounding numbers when saving them into the database.

Here is my model:

/**
 * Adds a pur order.
 *
 * @param      <array>   $data   The data
 *
 * @return     boolean , int id purchase order
 */
public function add_pur_order($data){

    $data['order_date'] = to_sql_date($data['order_date']);

    $data['delivery_date'] = to_sql_date($data['delivery_date']);

    $data['datecreated'] = date('Y-m-d H:i:s');

    $data['addedfrom'] = get_staff_user_id();

    if(isset($data['pur_order_detail'])){
        $pur_order_detail = explode (',', $data['pur_order_detail']);
        unset($data['pur_order_detail']);
        $es_detail = [];
        $row = [];
        $rq_val = [];
        $header = [];
        $header[] = 'item_code';
        $header[] = 'unit_id';
        $header[] = 'unit_price';
        $header[] = 'quantity';
        $header[] = 'into_money';
        $header[] = 'tax';
        $header[] = 'total';
        $header[] = 'discount_%';
        $header[] = 'discount_money';
        $header[] = 'total_money';

        for ($i=0; $i < count($pur_order_detail); $i  ) {
            
                $row[] = $pur_order_detail[$i];
            
            if((($i 1)) == 0){
                $rq_val[] = array_combine($header, $row);
                $row = [];
            }
        }
        foreach($rq_val as $key => $rq){
            if($rq['item_code'] != ''){
                array_push($es_detail, $rq_val[$key]);
            }
        }
    }
    
    if(isset($data['dc_total'])){
        $data['discount_total'] = reformat_currency_pur($data['dc_total']);
        unset($data['dc_total']);
    }

    if(isset($data['dc_percent'])){
        $data['discount_percent'] = $data['dc_percent'];
        unset($data['dc_percent']);
    }

    $this->db->insert(db_prefix() . 'pur_orders', $data);
    $insert_id = $this->db->insert_id();

    if ($insert_id) {
        // Update next estimate number in settings
        $total = [];
        $total['total'] = 0;
        $total['total_tax'] = 0;
        $total['subtotal'] = 0;
       
        foreach($es_detail as $key => $rqd){
            $es_detail[$key]['pur_order'] = $insert_id;
            $total['total']  = $rqd['total_money'];
            $total['total_tax']  = ($rqd['total']-$rqd['into_money']);
            $total['subtotal']  = $rqd['into_money'];
        }

        if($data['discount_total'] > 0){
            $total['total'] = $total['total'] - $data['discount_total'];
        }

        $this->db->insert_batch(db_prefix().'pur_order_detail',$es_detail);

        $this->db->where('id',$insert_id);
        $this->db->update(db_prefix().'pur_orders',$total);

        return $insert_id;
    }

    return false;
}

The problem I have is specifically related to quantity. If I am to enter 20.10 (just and example, but applies to any decimal number) I still end up having a saved value in the database of 20.

Furthermore I have also seen to change the table for quantity (which was previously set to varchar) to DECIMAL, however with the same result.

What is the best way to have this fixed? This should only apply for quantity. Some expert help would be greatly appreciated, thank you

CodePudding user response：

You should use DECIMAL(X,Y). X being the total length and Y being the number of decimals you would like to keep.

Using string might give you more problems in the future when you're trying to ensure integrity of the data type.

https://www.w3schools.com/sql/sql_datatypes.asp