最新ソースはコチラ
下記のソースは初期版です。
// PDOを利用したデータベースクラス
class pdbo{
private static $pdo;
private static $conf;
private function __construct(){
}
public function Factory( $table ){
if( is_null( pdbo::$conf ) ){
$tmp = new Config( CONF_PATH.'db.ini' );
$tmp = $tmp->Get();
pdbo::$conf = $tmp['DataBase'];
}
if( is_null( pdbo::$pdo ) ){
try{
pdbo::$pdo = new PDO( pdbo::$conf['dns'],
pdbo::$conf['user'], pdbo::$conf['password'] );
} catch( PDOException $e ){
echo $e->GetMessage();
throw new Exception( $e->getMessage() );
}
}
return new dbo( pdbo::$pdo, '', $table );
}
}
class dbo{
private $dbo;
private $db_name;
private $table_name;
private $fileds;
private $error_message;
private $query_log = array();
public function __construct( $pdbo, $db, $table ){
$this->dbo = $pdbo;
$this->db_name = $db;
$this->table_name = $table;
//テーブル情報取得
try{
$ret = $pdbo->query( 'DESCRIBE '.$table );
} catch( PDOException $e ){
$this->error_message = $e->getMessage();
throw new Exception( $this->error_message );
}
$tmp = $ret->fetchAll( PDO::FETCH_ASSOC );
foreach( $tmp as $node ){
$type = $this->getType( $node['Type'] );
$this->fileds[$node['Field']]['type']= $type['type'];
$this->fileds[$node['Field']]['size']= $type['size'];
$this->fileds[$node['Field']]['option']= $type['option'];
$this->fileds[$node['Field']]['null']= $node['Null'];
$this->fileds[$node['Field']]['key'] = $node['Key'];
}
}
public function DumpQuerys(){
print_r( $this->query_log );
}
/**
* 一行だけレコードを取得する
* $flagがtrueの時、条件配列にプライマリキーが存在しない場合エラーに
* なります
* @param array $condition 条件配列
* @param bool $flag プライマリキー存在チェック
*/
public function getOnce( $condition, $flag = true ){
$table = $this->table_name;
$dbo =& $this->dbo;
if( $flag ){
//プライマリーキーの存在チェック
$primary = $this->getPrimary();
$keys = array_keys( $condition );
foreach( $primary as $name ){
if( !in_array( $name, $keys ) ){
throw new Exception('プライマリーキーが設定されていません');
}
}
}
//検索条件生成
$ret = null;
try{
$where = $this->makeWhere( $condition );
$sql = "SELECT * FROM {$table}";
if( strlen( $where ) > 0 ) $sql .= $where;
$this->query_log[] = $sql;
$ret = $dbo->query( $sql );
} catch( PDOException $e ){
$this->error_message = $e->getMessage();
throw new Exception( $this->error_message );
}
//エラー処理
if( $ret === false ) throw new Exception( "query error: {$sql}");
//複数行検出
if( $ret->rowCount() != 1 ) return false;
return $ret->fetch(PDO::FETCH_ASSOC);
}
public function getAll($condition = array(), $order = array()){
$table = $this->table_name;
$dbo =& $this->dbo;
//検索条件生成
$ret = null;
try{
$where = $this->makeWhere( $condition );
$_order = $this->makeOrder( $order );
$sql = "SELECT * FROM {$table}";
$sql .= $where;
$sql .= $_order;
$this->query_log[] = $sql;
$ret = $dbo->query( $sql );
} catch( PDOException $e ){
$this->error_message = $e->getMessage();
throw new Exception( $this->error_message );
}
//エラー処理
if( $ret === false ) throw new Exception( "query error: {$sql}");
return $ret->fetchAll(PDO::FETCH_ASSOC);
}
public function getPaging( $condition = array(), $order, &$page ){
$table = $this->table_name;
$dbo =& $this->dbo;
//検索条件生成
$res = null;
try{
$where = $this->makeWhere( $condition );
$_order = $this->makeOrder( $order );
$sql = "SELECT * FROM {$table}";
$sql .= $where;
$sql .= $_order;
$this->query_log[] = $sql;
$res = $dbo->query( $sql );
} catch( PDOException $e ){
$this->error_message = $e->getMessage();
throw new Exception( $this->error_message );
}
//エラー処理
if( $res === false ) throw new Exception( "query error: {$sql}");
$page['rowCount'] = $res->rowCount();
$page['pageMax'] = ceil( $page['rowCount'] / $page['limit'] );
$page['start'] = ($page['now'] - 1 )* $page['limit'] + 1;
$page['end'] = $page['now'] * $page['limit'];
if( $page['end'] > $page['rowCount'] ) $page['end'] = $page['rowCount'];
$ret = array();
for( $n = $page['start']; $n fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $n );
}
return $ret;
}
/**
* 唯一のレコード更新
*/
public function updateOnce( $data ){
$table = $this->table_name;
$dbo =& $this->dbo;
$fileds =& $this->fileds;
//更新データ
$up_data = array();
//条件データ
$condition = array();
foreach( $data as $name => $val ){
list( $nm ) = $this->getFiledName( $name );
if( $fileds[$nm]['key'] == 'PRI' ){
$condition[$name] = $val;
} else {
$up_data[$name] = $val;
}
}
return $this->update( $up_data, $condition );
}
/**
* 複数更新
*/
public function update( $data, $condition ){
$table = $this->table_name;
$dbo =& $this->dbo;
$fileds =& $this->fileds;
$node = array();
try{
$where = $this->makeWhere( $condition );
foreach( $data as $name => $val){
$val = addslashes($val);
//日付時にクォートをつける
if( $this->isDate($fileds[$name]['type']) ) {
if( $val == $val * 1 ){
$val = "'".date( 'Y-m-d H:i:s', $val )."'";
}
}
//文字型にシングルクォートをつける
if( $this->isString($fileds[$name]['type']) ) {
$val = "'{$val}'";
}
$node[] = "{$name} = {$val}";
}
$sql = "UPDATE {$table} SET ".implode( ' , ', $node )." {$where}";
$this->query_log[] = $sql;
$dbo->query( $sql );
} catch( Exception $e){
echo $e->getMessage();
return false;
}
return true;
}
/**
* 一行だけレコードを取得する
* $flagがtrueの時、条件配列にプライマリキーが存在しない場合エラーに
* なります
* @param array $data 登録情報
* @param bool $flag プライマリキー存在チェック
*/
public function insert( $data, $flag = true ){
if( count( $data ) == 0 ) return false;
//インサートチェック
if( ($name = dbo::existsFiled($data,$this->getNotNull())) !== true ){
throw new Exception(
"{$name}はnot nullのフィールドですが、存在していませんでした。"
);
}
$fileds =& $this->fileds;
//文字列次処理
foreach( $data as $name => $val ){
//文字列時にクォートをつける
if( $this->isString($fileds[$name]['type']) ){
$val = "'".addslashes($val)."'";
}
//日付時にクォートをつける
if( $this->isDate($fileds[$name]['type']) ) {
if( is_int( $val ) ){
$val = date( 'Y-m-d H:i:s', $val );
}
$val = "'".addslashes($val)."'";
}
$data[$name] = $val;
}
$tmp = array();
foreach( $data as $name => $val ) $tmp[] = "{$val}";
$sql = "INSERT INTO {$this->table_name} ( ".
implode( ' , ', array_keys($data) )." ) VALUES ( ";
$sql .= implode( ', ', $tmp )." ); ";
try{
$this->query_log[] = $sql;
$ret = $this->dbo->query( $sql );
} catch( PDOException $e ) {
throw new Exception( 'Insert Error:'.$e->getMessage() );
}
if( $ret === false ){
throw new Exception( 'Insert Error: '.$sql );
}
return true;
}
public function delete( $condition = array() ){
}
/**
* 条件削除
*/
public function deleteSearch( $condition ){
if( count( $condition ) == 0 ) return false;
$sql = "DELETE FROM {$this->table_name} ".$this->makeWhere($condition);
try{
$this->query_log[] = $sql;
$ret = $this->dbo->query( $sql );
} catch( PDOException $e ) {
throw new Exception( 'Insert Error:'.$e->getMessage() );
}
return true;
}
/**
* Where句の生成
*/
private function makeWhere( $condition ){
if( count( $condition ) == 0 ) return '';
$fileds = $this->fileds;
$where = array();
foreach( $condition as $key => $val ){
$val = addslashes( $val );
$option = '';
//オプション検出
if( strpos( $key, ':' ) !== false ){
//オプション取得
$tmp = $key;
list( $key, $option ) = explode( ':', $tmp );
//テーブルにカラムが存在するか
if( !isset( $fileds[$key] ) ){
throw new Exception(
"{$key}は{$this->table_name}に存在しないカラムです"
);
}
//文字列 日付型 の場合の処理
if( $this->isString( $fileds[$key]['type'] ) ||
$this->isDate( $fileds[$key]['type'] )
) {
$val = "'{$val}'";
}
switch( $option ){
case 'not':
$where[] = "{$key} <> {$val}";
break;
case 'begin':
$where[] = "{$key} >= {$val}";
break;
case 'quit':
$where[] = "{$key} {$val}";
break;
case 'end':
$where[] = "{$key} < {$val}"; break; case '--': $where[] = "{$key} {$val}"; break; case 'like': $where[] = "{$key} LIKE {$val}"; break; case 'eq': $where[] = "{$key} = {$val}"; break; case 'neq': $where[] = "{$key} = {$val}"; break; default: throw new Exception( 'makeWhere error: '. '指定されないオプションを検出しました' ); } } else { //オプションが無いとき //テーブルにカラムが存在するか if( !isset( $this->fileds[$key] ) ){
throw new Exception(
"{$key}は{$this->table_name}に存在しないカラムです"
);
}
if( $this->isString($fileds[$key]['type'] ) ) {
$where[] = "{$key} LIKE '%{$val}%'";
} else {
$where[] = "{$key} = {$val}";
}
}
}
return " WHERE ".implode( ' AND ', $where );
}
/**
*
*/
protected function makeOrder( $order ){
if( count( $order ) == 0 ) return '';
$ret = array();
$fileds = $this->fileds;
$kyes = array_keys( $order );
//テーブルにカラムが存在するか
foreach( $order as $name => $val ){
if( !isset( $fileds[$name] ) ){
throw new Exception(
"{$key}は{$this->table_name}に存在しないカラムです"
);
}
$ret[] = "{$name} {$val}";
}
return " ORDER BY ".implode( " , ", $ret );
}
/**
* 文字型かを判別する
*/
protected function isString( $type ){
switch( strtolower($type) ){
case 'char':
case 'varchar':
case 'text':
return true;
}
return false;
}
/**
* 日付型かを判別する
*/
protected function isDate( $type ){
switch( strtolower($type) ){
case 'date':
case 'datetime':
case 'timestamp':
return true;
}
return false;
}
/**
* Mysqlのカラムタイプから型とサイズ、オプション属性を切り離す
*/
private function getType( $type ){
$ret = array(
'size' => '',
'type' => '',
'option'=> '',
);
$st = 0;
$end= 0;
$st = strpos( $type, '(' );
if( $st === false ){
$ret['type'] = $type;
return $ret;
}
$ret['type'] = substr( $type, 0, $st );
$end = strpos( $type, ')' );
$ret['size'] = substr( $type, $st+1, $end-$st-1 );
$tmp = explode( ' ', $type );
if( count( $tmp ) > 1 ) $ret['option'] = $tmp[1];
return $ret;
}
/**
* プライマリーキー配列の取得
*/
private function getPrimary(){
$ret = array();
foreach( $this->fileds as $name => $node ){
if( $node['key'] == 'PRI' ){
$ret[] = $name;
}
}
return $ret;
}
private function getNotNull(){
$ret = array();
foreach( $this->fileds as $name => $node ){
if( $node['null'] == 'No' ){
$ret[] = $name;
}
}
return $ret;
}
/**
* $list配列内のフィールドが$filedsに含まれているかチェックする
*/
private function existsFiled( $fileds, $list ){
$keys = array_keys( $fileds );
foreach( $list as $name ){
if( !in_array( $name, $keys ) ) return $name;
}
return true;
}
private function getFiledName( $name ){
if( strpos( $name, ':' ) === false ) return array( $name, '' );
return explode( ':', $name );
}
}