티스토리 뷰

웹개발/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
댓글
댓글쓰기 폼