티스토리 뷰

웹개발/Php

Kohana Query Builder

야쿠 yaku 2011.08.26 06:18


표현 예제

Sql 
UPDATE `pages` SET `views` = views + 1 WHERE `id` = 1

변환

DB::update('pages')
  	->set(array('views' => DB::expr('views + 1')))
  	->where('id', '=', 1)->execute(); 

Transactions

BEGIN WORK;

DB::query(NULL, "BEGIN WORK")->execute();
 
 

  DB::query(NULL, "BEGIN WORK")->execute();
  
  // Array of Page Data
  $page_data = array(
  	'id'	=> NULL,
  	'title'	=> 'My New Page',
  );
  
  $page = (bool) DB::insert('pages', array_keys($page_data))
  		->values($page_data)
  		->execute();
  
  $page_cat_data = array(
  	'page_count'	=> DB::expr('page_count + 1'),
  );
  
  $page_cat = (bool) DB::update('page_categories')
  		->set($page_cat_data)
  		->where('id', '=', 1)
  		->execute();
  
  if($page AND $page_cat)
  {
  	DB::query(NULL, "COMMIT")->execute();
  }
  else
  {
  	DB::query(NULL, "ROLLBACK")->execute();
  }


Joins

sql 문
SELECT `articles`.`title`, `users`.`username` FROM `articles` JOIN `users` ON (`users`.`id` = `articles`.`user_id`) WHERE `articles`.`id` = 1 LIMIT 1
 변환 표현

 $query = DB::select('articles.title', 'users.username')
		->from('articles')
		->where('articles.id', '=', 1)
			->join('users')
			->on('users.id', '=', 'articles.user_id')
		->limit(1)
		->execute();



복잡한 Select 표현 형식

select 컬럼 지정

 DB::select('column1','column2')->from('table_name');
==> SELECT `column1`, `column2` FROM `table_name`


컬럼에 as 사용하기

DB::select(array('column','my_column'))->from('table_name')->compile($db);
SELECT `column` AS `my_column` FROM `table_name`


조인 하기

 DB::select()->from('table_name')->join('table_2')->on('table_2.table_id', '=', 'table_name.id');

=>  SELECT * FROM `table_name` JOIN `table_2` ON `table_2`.`table_id` = `table_name`.`id`

group_by()

DB::select()->from('table_name')->group_by('column');

 => SELECT * FROM `table_name` GROUP BY `column`

DB::select()->from('table_name')->group_by(array('column1', 'mycol'));

=>  SELECT * FROM `table_name` GROUP BY `column1` AS `mycol`

having() 절 사용

  DB::select()->from('table_name')->having('column','=','value');

=>  SELECT * FROM `table_name` HAVING `column` = 'value'


order_by() 절 사용

DB::select()->from('table_name')->order_by('column', 'ASC');
=>  SELECT * FROM `table_name` ORDER BY `column` ASC


limit() 사용

 DB::select()->from('table_name')->limit(10);
=> SELECT * FROM `table_name` LIMIT 10

offset() 사용

  DB::select()->from('table_name')->limit(10)->offset(50);
=>  SELECT * FROM `table_name` WHERE `column` = 'value'

and_where() 사용

 DB::select()->from('table_name')->where('column','=','value')->and_where('column2','=','value');
=> SELECT * FROM `table_name` WHERE `column` = 'value' OR `column2` = 'value'


where_open() 사용

  DB::select()->from('table_name')->where_open()->where('column','=','value')
          ->or_where('column2','=','value')->where_close();
 => SELECT * FROM `table_name` WHERE (`column` = 'value' OR `column2` = 'value')

and_where_open() 사용

  DB::select()->from('table_name')->where('column','=','value')->and_where_open()->where('column2','=','value')
          ->or_where('column3','=','value')->and_where_close();

 => SELECT * FROM `table_name` WHERE `column` = 'value' AND (`column2` = 'value' OR `column3` = 'value')

or_where_open()

DB::select()->from('table_name')->where('column','=','value')->or_where_open()->where('column2','=','value')
          ->and_where('column3','=','value')->or_where_close();

=>  SELECT * FROM `table_name` WHERE `column` = 'value' OR (`column2` = 'value' AND `column3` = 'value')
 



 
 


 

 
 
저작자 표시
신고

'웹개발 > Php' 카테고리의 다른 글

PHP 정규 표현식 10가지 사용 예제  (2) 2011.09.06
Kohana Validation 체크  (0) 2011.08.26
Kohana Query Builder  (0) 2011.08.26
Kohana Request  (0) 2011.08.26
Kohana 3.2 Config 얻기..  (0) 2011.08.24
Kohana 3.2 Custom Error Page 만들기.  (1) 2011.08.24
댓글
댓글쓰기 폼