why my transaction doesn't work

[eluser]Mario "St.Peter" Valentino[/eluser]
this is my code

Code:

class Pembelian_model extends CI_Model
{
public function pembelian_save($katalog_id,$nama,$email,$telepon,$handphone,$alamat,$pesan)
{
  $this->db->trans_start();
    
  $user_id  = date("Ymd").'-';
  $max_user = $this->check_max_user($user_id);
  if($max_user=='')
  {
   $urutan   = 1;
   $user_id .= $urutan;
    
  }
  else
  {
   $urutan   = $max_user+1;
   $user_id    .= $urutan;
  }
  
  $data = array('id_client' => $user_id,
      'nama'  => $nama,
      'email'  => $email,
      'telp'  => $telepon,
      'handphone' => $handphone,
      'alamat' => $alamat,
      'remark' => $pesan,
      'urutan' => $urutan
    );
  
  if($this->db->insert('tbl_client',$data))
  {
   $order_id  = $katalog_id.'-'.date("Ymd").'-';
   $max_order = $this->check_max_order($order_id);
   if($max_order=='')
   {
    $urutan_order = 1;
    $order_id    .= $urutan_order;
   }
   else
   {
    $urutan_order = $max_order+1;
    $order_id    .= $urutan_order;  
   }
  
   $data_order = array('id_order'   => $order_id,
        'id_client'  => $user_id,
        'id_katalog' => $katalog_id,
        'tgl_order'  => date("Y-m-d h:m:s"),
        'status'  => 0,
        'urutan'  => $urutan_order
        );
  
   if($this->db->insert('tbl_order',$data_order))
   {
    $no = 1;
    foreach($this->cart->contents() as $items)
    {
     $id_produk = $items['id'];
     $qty  = $items['qty'];
     $price  = $items['price'];
    
    
     $data  = array('id_order'  => $order_id,
          'id_produk' => $id_produk,
          'qty'  => $qty,
          'harga'  => $price,
          'urutan' => $no
        );
    
     if($this->db->insert('tbl_cart',$data))
     {
      if($this->db->query("UPDATE tbl_produk SET produk_stok = produk_stok-".$qty." WHERE produk_id='".$id."'"))
       $result = TRUE;
      else
       $result = FALSE;
     }
    
     $no++;
    }
   }
  }
  
  $this->db->trans_complete();
  
  if ($this->db->trans_status()===FALSE)
  {
      $this->db->trans_rollback();
  }
  else
  {
      $this->db->trans_commit();
  }
  
  return $result;
}

public function check_max_user($user_id)
{
  $sql = "SELECT max(urutan) as max_sort FROM tbl_client WHERE id_client LIKE '".mysql_real_escape_string($user_id)."%'";
  $query = $this->db->query($sql);
  
  $data = $query->row();
  
  //echo $sql;exit;
  
  return $data->max_sort;
  
}

public function check_max_order($order_id)
{
  $sql = "SELECT max(urutan) as max_sort FROM tbl_order WHERE id_order LIKE '".mysql_real_escape_string($order_id)."%'";
  $query = $this->db->query($sql);

  $data = $query->row();
  
  //echo $sql;exit;
  
  return $data->max_sort;

}
}

please see $id on

Code:

if($this->db->query("UPDATE tbl_produk SET produk_stok = produk_stok-".$qty." WHERE produk_id='".$id."'"))

this is not exists so transaction will fail too but why transaction still saving data before the code running.


Please help me