Procedural File: PDO_MySQL_Manipulator.php

0.0.1

PHP versions 5

 usage:

 [common] ----------------------------------------------------------------------------------------
 $this->manipulator = new PDO_MySQL_Manipulator(DB_HOST, DB_NAME, DB_USER, DB_PASS);
 $this->manipulator->setTable('test_tbl');

 [select] ----------------------------------------------------------------------------------------
 $this->manipulator->select('SQL_CALC_FOUND_ROWS *');
 - or -
 $this->manipulator->select('a', 'b', 'CCC as count');

 $this->manipulator->where(array(
     '(',
     'test_id' => 123, // or 'test_id' = array('>', 123); or array('test_id', '>', 123);
     'and',
     'test_name' => 'hoge',
     'and',
     'test_num' => array('BETWEEN|NOT BETWEEN', 1, 9),
     'and',
     'test_col' => array('IS NULL|IS NOT NULL'),
     'and',
     'FUNC:FIND_IN_SET' => array(array('column' => 'area_div_id'), array('value' => '1,3')) // FIND_IN_SET(area_div_id, '1,3')
     'and',
     'FUNC:FIND_IN_SET' => array(array('value' => 1), array('column' => 'genre_div_id'))    // FIND_IN_SET(1, genre_div_id)
     ')',
 ));
 - or -
 $this->manipulator->where(array(
     $column => array('IN', $arr),  // or $column => array('NOT IN', $arr),
     'and',
     'test_id' => 123,
     ));
 - or -
 // [廃止] $this->manipulator->whereIn($column, $arr);

 $this->manipulator->groupBy('a', 'b');

 $this->manipulator->having(); // 未実装

 $this->manipulator->orderBy(array(
     'test_name' => 'DESC',
     'test_id'   => 'ASC',
 ));
 - or -
 $this->manipulator->orderBy(array('rand()'));

 $this->manipulator->limit(0, 10);

 $result = $this->manipulator->fetchAll(true/false);
 - or -
 $result = $this->manipulator->fetch(true/false);
 - or -
 $result = $this->manipulator->fetchColumn(true/false);

 $total_rows = $this->manipulator->foundRows();

 [join] ----------------------------------------------------------------------------------------
 $this->manipulator->setAlias('mc');

 $this->manipulator->select('
     SQL_CALC_FOUND_ROWS *, mc.name as mc_name, mk.name as mk_name, mc.active_flg as mc_active_flg
 ');

 $this->manipulator->join('LEFT', 'makers mk ON mc.maker_id = mk.maker_id');
 - or -
 $this->manipulator->join('LEFT', 'makers mk');
 $this->manipulator->using('maker_id');

 $this->manipulator->where(array(
     'mc.type_div_id' => $type_div_id,
 ));

 $this->manipulator->orderBy(array(
     'mc.name' => 'ASC',
 ));

 $result = $this->manipulator->fetchAll(true/false);

 [insert] ----------------------------------------------------------------------------------------
 $this->manipulator->columns('id', 'name', 'count');

 $this->manipulator->values(array(
     'id'    => 123,
     'name'  => $p['name'],
     'count' => 1,
 ));

 $this->manipulator->onDuplicateKeyUpdate(array(
     'name'  => $p['name'],
     'count' => array('=', 'count + 1'), // or 'count' => $count + 1,
 ));

 $result = $this->manipulator->insert(true/false);

 [update] ----------------------------------------------------------------------------------------
 $this->manipulator->set(array(
     'col_a' => 'hoge',
     'col_b' => 'fuga',
 ));

 $this->manipulator->where(array(
     'test_id' => 123, // or 'test_id' = array('>', 123);
     'and',
     'test_name' => 'hoge',
 ));
 - or -
 // [廃止] $this->manipulator->whereIn($column, $arr);

 $result = $this->manipulator->update(true/false);

 [delete] ----------------------------------------------------------------------------------------
 $this->manipulator->where(array(
     'test_id' => 123, // or 'test_id' = array('>', 123);
     'and',
     'test_name' => 'hoge',
 ));
 - or -
 // [廃止] $this->manipulator->whereIn($column, $arr);

 $result = $this->manipulator->delete(true/false);

 [raw query] -------------------------------------------------------------------------------------
 $sql = "select * from $this->table where user_id = $user_id and hoge like '$hoge%' ";
 $this->manipulator->rawQuery('fetchColmun|fetch|fetchAll|execute', $sql);



 change log:

 2009.09.24 setWhereメソッドを複数回コールできるように拡張。
 2009.10.06 joinメソッドを追加。
 2009.10.14 where句に BETWEEN|NOT BETWEEN を指定できるように拡張。
 2009.10.15 冗長だったメソッド名を修正。 ex) setWhere -> where
 2009.10.15 selectメソッドを追加。
 2009.10.16 setAliasメソッドを追加。
 2009.10.16 usingメソッドを追加。
 2010.01.06 setメソッドを複数回コールできるように拡張。
 2010.01.25 where句に IS NULL|IS NOT NULL を指定できるように拡張。
 2010.02.04 where句に IN|NOT IN を指定できるように拡張。IN + (and|or) のような復号条件を指定可能になった。
 2010.02.12 forUpdateメソッドを追加。(SELECT ... FOR UPDATE)
 2010.02.12 lock / unlockメソッドを追加。(LOCK TABLES / UNLOCK TABLES)
 2010.02.18 updateWithAffectedRowsメソッドを追加。
 2010.02.18 deleteWithAffectedRowsメソッドを追加。
 2010.02.23 where句において、同名カラムを指定できるように拡張。

    例）WHERE reg_date >= start_date AND reg_date <= end_date のような where句 を構築したい場合

        // 配列で設定する array(カラム名, 演算子, 値)
        $this->manipulator->where(array(
            array('reg_date', '>=', $start_date),
            'and',
            array('reg_date', '<=', $end_date),
            'or',
            array('reg_date', 'IS', null), // or array('reg_date', 'IS NOT', null),
        ));

 2010.03.09 hasWhereメソッドを追加。
 2010.08.16 ifDefineWhereメソッドを追加。usage) $this->manipulator->ifDefineWhere('and');
 2010.10.25 join と useing を複数回定義できるように拡張。
 2011.03.15 where句に FIND_IN_SET を指定できるように拡張。