ทบทวนการใช้งาน query builder เพิ่มเติม สำหรับการ select ข้อมูล

CREATE TABLE IF NOT EXISTS `tbl_customer` (
  `cus_id` int(11) NOT NULL AUTO_INCREMENT,
  `cus_name` varchar(100) COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cus_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`cus_id`, `cus_name`) VALUES
(1, 'Cus A'),(2, 'Cus B'),(3, 'Cus C'),(4, 'Cus D'),(5, 'Cus E');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_order`
--

CREATE TABLE IF NOT EXISTS `tbl_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `cus_id` int(11) NOT NULL,
  `pro_id` int(11) NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `cus_id` (`cus_id`,`pro_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=11 ;

--
-- Dumping data for table `tbl_order`
--

INSERT INTO `tbl_order` (`order_id`, `cus_id`, `pro_id`) VALUES
(1, 1, 3),(2, 2, 3),(3, 1, 4),(4, 3, 5),(5, 1, 5),(6, 3, 5),(7, 5, 2),(8, 5, 4),(9, 3, 2),(10, 5, 5);

-- --------------------------------------------------------

--
-- Table structure for table `tbl_product`
--

CREATE TABLE IF NOT EXISTS `tbl_product` (
  `pro_id` int(11) NOT NULL AUTO_INCREMENT,
  `pro_name` varchar(100) COLLATE utf8_general_ci NOT NULL,
  `pro_price` int(11) NOT NULL,
  PRIMARY KEY (`pro_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=6 ;

--
-- Dumping data for table `tbl_product`
--

INSERT INTO `tbl_product` (`pro_id`, `pro_name`, `pro_price`) VALUES
(1, 'Pro 111', 100),(2, 'Pro 222', 200),(3, 'Pro 333', 100),(4, 'Pro 444', 250),
(5, 'Pro 555', 400);

$this->db->get("tbl_customer");

// คำสั่ง sql : 
// SELECT * FROM tbl_customer

$this->db->get("tbl_customer",3,2);

// คำสั่ง sql : 
// SELECT * FROM tbl_customer LIMIT 2,3

<?php
$query = $this->db->get("tbl_customer");

$total = $query->num_rows();  // จำนวนข้อมูลจากการคิวรี่ทั้งหมด
if($total>0){ // เช็คว่ามีค่ามากว่า 0 หรือไม่
    foreach ($query->result_array() as $row)  // วนลูปแสดงข้อมูล
    {
            echo $row['cus_id'];
            echo $row['cus_name'];
            echo "<br>";
    }
}
?>

$this->db->get_where("tbl_customer",array('cus_id'=>3));

// คำสั่ง sql : 
// SELECT * FROM tbl_customer WHERE cus_id=3

$this->db->get_where("tbl_customer",array('cus_id'=>3),1,0);

// คำสั่ง sql : 
// SELECT * FROM tbl_customer WHERE cus_id=3 LIMIT 1 
// ถ้าค่า offset เป็น 0 คำสั่ง sql จะแสดงเพียงค่า limit เท่านั้น

<?php
$query = $this->db->get_where("tbl_customer",array('cus_id'=>3));

$row = $query->row_array();  //คิวรี่ข้อมูลมาแสดงแค่รายการเดียว
if(isset($row))  // ถ้ามีข้อมูล 
{
    echo $row['cus_id'];
    echo $row['cus_name'];
    echo "<br>";
}
?>

$this->db->select("pro_name,pro_price");    
$query = $this->db->get("tbl_product");

// คำสั่ง sql : 
// SELECT pro_name,pro_price FROM tbl_product

$this->db->select_sum('pro_price');
$query = $this->db->get("tbl_product");

// คำสั่ง sql : 
// SELECT SUM(pro_price) AS pro_price FROM tbl_product

$this->db->select_sum('pro_price','total_price');
$query = $this->db->get("tbl_product");

// คำสั่ง sql : 
// SELECT SUM(pro_price) AS total_price FROM tbl_product

$this->db->from('tbl_order');
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM tbl_order

$this->db->from('tbl_order a');
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_order` `a`

$this->db->from('tbl_order');
echo $this->db->get_compiled_select(null,FALSE); // สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

$this->db->from('tbl_order');
$this->db->join('tbl_product','tbl_order.pro_id=tbl_product.pro_id');
echo $this->db->get_compiled_select(null,FALSE); // สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_order` JOIN `tbl_product` ON `tbl_order`.`pro_id`=`tbl_product`.`pro_id`

$this->db->from('tbl_order a');
$this->db->join('tbl_product b','a.pro_id=b.pro_id');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_order` `a` JOIN `tbl_product` `b` ON `a`.`pro_id`=`b`.`pro_id`

$this->db->from('tbl_order a');
$this->db->join('tbl_product b','a.pro_id=b.pro_id','left');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_order` `a` LEFT JOIN `tbl_product` `b` ON `a`.`pro_id`=`b`.`pro_id`

$this->db->from('tbl_order a');
$this->db->join('tbl_product b','a.pro_id=b.pro_id','left');
$this->db->join('tbl_customer c','a.cus_id=c.cus_id','left');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_order` `a` 
// LEFT JOIN `tbl_product` `b` ON `a`.`pro_id`=`b`.`pro_id` 
// LEFT JOIN `tbl_customer` `c` ON `a`.`cus_id`=`c`.`cus_id`

$this->db->from('tbl_product');
$this->db->where('pro_price',100);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` = 100

$this->db->from('tbl_product');
$this->db->where('pro_price',100);
$this->db->where('pro_name','Pro 333');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` = 100 AND `pro_name` = 'Pro 333'

$this->db->from('tbl_product');
$this->db->where('pro_price >',200);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` > 200

$this->db->from('tbl_product');
$this->db->where('pro_price >=',200);
$this->db->where('pro_price <=',300);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` >= 200 AND `pro_price` <= 300

$arr_condition=array(
    'pro_price >=' =>200,
    'pro_price <=' =>300
);
$this->db->from('tbl_product');
$this->db->where($arr_condition);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` >= 200 AND `pro_price` <= 300

$this->db->from('tbl_product');
$this->db->where('pro_price >=200 AND pro_price<=300');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` >= 200 AND `pro_price` <= 300

$this->db->from('tbl_product');
$this->db->where('pro_price <=',100);
$this->db->or_where('pro_price >=',300);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` <= 100 OR `pro_price` >= 300

$setData = array(100,200,300);
$this->db->from('tbl_product');
$this->db->where_in('pro_price',$setData);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` IN(100, 200, 300)

$this->db->from('tbl_product');
$this->db->like('pro_name',22);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_name` LIKE '%22%' ESCAPE '!'

$this->db->from('tbl_product');
$this->db->like('pro_name',22,'before');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_name` LIKE '%22' ESCAPE '!'

$this->db->select('COUNT(*) as num_pro');    
$this->db->from('tbl_order');
$this->db->group_by('pro_id');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT COUNT(*) as num_pro FROM `tbl_order` GROUP BY `pro_id`

$this->db->select('COUNT(*) as num_pro');        
$this->db->from('tbl_order');
$this->db->group_by('pro_id');
$this->db->having('num_pro >',3);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT COUNT(*) as num_pro FROM `tbl_order` GROUP BY `pro_id` HAVING `num_pro` > 3

$this->db->from('tbl_product');
$this->db->order_by('pro_price','DESC');
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` ORDER BY `pro_price` DESC

$this->db->order_by('pro_price DESC');

$this->db->order_by('pro_price DESC,pro_name ASC');

$this->db->order_by('pro_price','DESC');
$this->db->order_by('pro_name','ASC');

$this->db->order_by('pro_nam', 'RANDOM');

$this->db->from('tbl_product');
$this->db->limit(3,2);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` LIMIT 2, 3

$total = $this->db->count_all('tbl_order');    
echo $total;

$this->db->from('tbl_product');
$this->db->where('pro_price >200');
$total_result = $this->db->count_all_results(null,FALSE); 
echo $total_result;
$this->db->limit(3,2);
echo $this->db->get_compiled_select(null,FALSE);// สำหรับโชว์คำสั่ง sql ลบออกถ้าไม่ใช้
$query = $this->db->get();

// คำสั่ง sql : 
// SELECT * FROM `tbl_product` WHERE `pro_price` > 200 LIMIT 2, 3

$this->db->from('tbl_order a');
$this->db->join('tbl_product b','a.pro_id=b.pro_id','left');
$this->db->join('tbl_customer c','a.cus_id=c.cus_id','left');
$this->db->order_by('b.pro_price','desc');
$this->db->limit(5,10);
$query = $this->db->get();

$query = $this->db->from('tbl_order a')
->join('tbl_product b','a.pro_id=b.pro_id','left')
->join('tbl_customer c','a.cus_id=c.cus_id','left')
->order_by('b.pro_price','desc')
->limit(5,10)
->get();