Update array query in Codeigniter

Question

I am making pharmacy system using Codeigniter. I want to update stock of several items in table after purchase, below is the code what i have tried so far.

Below is the my Controller

function add_invoice(){

        $customer = $this->input->post('customer');
        $date = date("Y-m-d",strtotime($this->input->post('date')));
        $grandtotal = $this->input->post('grandtotal');
        $ref = rand(1111111111,9999999999);
        $medicine = $this->input->post('medicine');
        $quantity = $this->input->post('quantity');
        $subtotal = $this->input->post('subtotal');

    foreach($medicine as $key=>$val){

    $data[] = array(
            'customer' => $customer,
            'date' => $date,
            'grandtotal' => $grandtotal,
            'ref' => $ref,
            'medicine' => $val,
            'quantity' => $quantity[$key],
            'subtotal' => $subtotal[$key],

            );
    }
    $this->my_model->decrement_item($medicine, $quantity);
    $this->db->insert_batch('table_invoice', $data);

}

And this is my Model below :

function decrement_item($medicine, $quantity)
{   
    $q = "UPDATE table_med SET stock = stock - ? WHERE medicine = ?";

    $this->db->query($q, [$quantity, $medicine]);

    if($this->db->affected_rows() > 0){
        return TRUE;
    }
    else{
        return FALSE;
    }
}

But when I execute the code, there is a message like this

I know I should turn parameter to array. But I don't know how? Thanks for your help

can you elaborate this **stock - ?** it should be **stock = ?** — munsifali, Aug 06 '18 at 09:33
Stock is in table_med (medicine) . So the idea is to substract the stock after purchase. Stock - quantity (purchase) — Ria W, Aug 06 '18 at 09:37
can you use another variable like this **$stock_quantity = $stock - $quantity** and then execute query — munsifali, Aug 06 '18 at 09:41
can you show your **Update** query after adding $stock_quantity variable. — munsifali, Aug 06 '18 at 09:51

Pradeep · Accepted Answer · 2018-08-06T10:21:29.967

1

Hope this will help you :

Your update query should be in foreach loop since quantity and medicine both are an array. Should be like this :

foreach($medicine as $key=>$val)
{

    $data[] = array(
        'customer' => $customer,
        'date' => $date,
        'grandtotal' => $grandtotal,
        'ref' => $ref,
        'medicine' => $val,
        'quantity' => $quantity[$key],
        'subtotal' => $subtotal[$key],
    );
    $this->db->set('stock', 'stock-'.$quantity[$key], FALSE);
    $this->db->where('medicine', $val);
    /* if not works use this 
     $this->db->where('medicine', $medicine[$key]);
    */
    $updated = $this->db->update('table_med');
}

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#updating-data

edited Aug 06 '18 at 10:21

answered Aug 06 '18 at 09:51

Pradeep

9,667
13
27
34

I try put it in Controller, but there's an error too. Saying "Duplicate entry 'Salbutamol' for key 'medicine' UPDATE `table_med` SET stock = stock-1, `medicine` = 'Salbutamol' – Ria W Aug 06 '18 at 10:00
change `$this->db->set('medicine', $val);` to this `$this->db->where('medicine', $val);` see my answer for that and try again – Pradeep Aug 06 '18 at 10:05
I've tried but, stock of medicine that changes is only the last one. The others didn't change. – Ria W Aug 06 '18 at 10:12
debug what `$val` contains in where clause in all iteration , i suggest use `medicine id` instead of `medicine name` to `update` the data , this is working for u , what u have to do only pass the correct medicine name or id in your `where` clause – Pradeep Aug 06 '18 at 10:19
ok try with this code : replace the previous `where clause` with `$this->db->where('medicine', $medicine[$key]);` see my answer in comment section – Pradeep Aug 06 '18 at 10:22
Hi, my mistake. I put the code outside {}. After I put it inside, it works. Thanks for your help. Really appreciate it :D – Ria W Aug 06 '18 at 10:27

Update array query in Codeigniter

1 Answers1