query "select for update" in codeigniter

Question

I have query = SELECT @condi:=id FROM table WHERE id>1 LIMIT 1 for UPDATE; UPDATE table set aa="ok" WHERE id=@condi

I used $this->db->query(query) but codeigniter info error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update table set aa="ok" where id=@condi' at line 1" Miss ";" I use phpmyadmin execuse this query, it's result row correct not error query

maybe core codeigniter error can you help me execuse my query in codeigniter,thanks

Answer 1

You could try to use query builder to prevent SQL error:

$query = $this->db->select()
            ->from('table')
            ->where('id >', 1)
            ->limit(1)
            ->get_compiled_select();
$data = $this->db->query("{$query} FOR UPDATE")->row_array();

$this->db->where('id', $condi)->update('table', ['aa'=>'ok']);

Because the Codeigniter 3 builder doesn't support FOR UPDATE, so I just use compiler to rebuild query.

Codeigniter get_compiled_select()

yidas/codeigniter-model - Pessimistic Locking