Where Method

The where method purpose is for filtering data from query result by executing WHERE SQL command. This method will put the result SQL command on Query Builder can combined with another Query Builder methods.

Basic Usage

Here is the basic usage where method from $db property on SENE_Model class.

x
–
+
$this->db->where(string $column_name, mixed $filter_value [, string $operator = 'AND' [, string $relation_operator = '=' [, int $open_bracket = 0 [, int $open_bracket = 0 ]]]]): $this->db

Parameters

This method has required 2 parameters and 4 optional parameters.

$column_name

Can contain the name of the column to be filtered. The value of this parameter is auto in escape.

$filter_value

Filter value against the selected column.

$operator

The operator value performed for the next WHERE condition, the following values match the value of this parameter.

  • AND
  • OR

$operator_relational

The value of the relational operator assigned to the filter condition with the column name.

  • =
  • != or <>
  • >
  • <
  • >=
  • <=
  • like
  • like%
  • %like
  • %like% or like%%
  • notlike
  • notlike%
  • %notlike
  • %notlike% or notlike%%

$open_bracket

Add an opening parenthesis at the beginning of the SQL command before the contents of the next WHERE command.

$close_bracket

Add a closing parenthesis at the end of the SQL command before the contents of the next WHERE command.

Example

The following is an example of using the where method in the d_order_model.php file.

x
–
+
<?php
class D_Order_Model extends SENE_Model{
  var $tbl = 'd_order';
  var $tbl_as = 'dor';

  public function __construct(){
    parent::__construct();
    $this->db->from($this->tbl,$this->tbl_as);
  }

  public function getById($id){
    $this->db->where("id",$id,"AND","=",0,0);
    return $this->db->get_first();
  }
  public function getNotCancelled(){
    $this->db->where("order_status","order_cancel","AND","<>",0,0);
    return $this->db->get();
  }
  public function getCancelByUser($b_user_id){
    $this->db->where("order_status","order_cancel","AND","like",0,0);
    $this->db->where("b_user_id",$b_user_id,"AND","=",0,0);
    return $this->db->get();
  }
  public function getAllCancelRefund(){
    $this->db->where("order_status","order_cancel","AND","=",0,0);
    $this->db->where("is_refund", 1,"OR","=",1,0);
    $this->db->where("is_refund_closed", 1,"OR","=",0,1);
    return $this->db->get();
  }
}

Generated SQL Command

The following is the SQL command generated by the method in the D_Order_Model class example.

x
–
+
-- result from executing D_Order_Model::getById(112) --
SELECT * FROM `d_order` WHERE `id` = 112;

-- result from executing D_Order_Model::getNotCancelled() --
SELECT * FROM `d_order` WHERE `order_status` != 'cancel';

-- result from executing D_Order_Model::getCancelByUser(2125) --
SELECT * FROM `d_order` WHERE `order_status` LIKE 'cancel' AND `b_user_id` = 2125;

-- result from executing D_Order_Model::getAllCancelRefund() --
SELECT * FROM `d_order` WHERE `order_status` = "order_cancel" AND ( `is_refund` = 1 OR `is_refund_closed` = 1);