PHP mysql Query Builder 数据库类

2014-01-06 16:07:18 0  category: PHP记事本

php mysql 数据库类


<?php

class query_builder
{
protected $tableName = '';
protected $queries = array();
public $join = array();
protected $text = 0;
protected $pdo = array();
public $host = '';
protected $sql = array(
'select' => ''
, 'where' => ''
, 'order' => ''
, 'limit' => ''
, 'group' => ''
, 'having' => ''
);

private function join( $join, $condition, $params = array() )
{
$str = $join . ' ON ' . $condition;
$this->join['str'][] = $str;
$this->join['par'][] = $params;
return $this;
}

public function leftjoin( $join, $condition, $params = array() )
{
return $this->join( ' LEFT JOIN ' . $join, $condition, $params );
}

public function rightjoin( $join, $condition, $params = array() )
{
return $this->join( ' RIGHT JOIN ' . $join, $condition, $params );
}

public function innerjoin( $join, $condition , $params = array() )
{
return $this->join( ' INNER JOIN ' . $join , $condition, $params );
}

protected function setNull()
{
$this->sql = array(
'select' => ''
, 'where' => ''
, 'order' => ''
, 'limit' => ''
, 'group' => ''
, 'having' => ''
);
$this->tableName = NULL;
$this->join = array();
$this->text = 0;
$this->host = '';
}
public function __get( $name )
{
if ( $name == 'text' )
$this->text = 1;
return $this;
}
public function __call( $methodName, $args )
{
$methodName = strtolower( $methodName );

if ( array_key_exists( $methodName , $this->sql ) )
{
if ( empty( $args[0] ) || ( is_string( $args[0] ) AND trim( $args[0] === '') ))
$this->sql[$methodName] = '';
else
$this->sql[$methodName] = $args;

if ( $methodName == 'limit' )
$this->sql[$methodName] = $args;
} else if ( $methodName == 'from' )
{
$this->tableName = $args[0];
} else if ( $methodName == 'host' )
{
$this->host = $args;
} else
{
echo 'class ' . get_class( $this )
. " function $methodName not exists";
}
return $this;
}

function queryAll()
{
$select = $this->sql['select'] != ''
? $this->sql['select'][0]
: ' * ';
$where = '';
$data = array();

$join = '';
if ( isset( $this->join['str'] ) )
{
foreach ( $this->join['str'] as $t_join )
$join .= $t_join;

foreach ( $this->join['par'] as $params )
foreach ( $params as $param)
$data[] = $param;
}


if ( $this->sql['where'] !== '' )
{
$where = $this->comWhere( $this->sql['where'] );
foreach ( $where['data'] as $v )
$data[] = $v;
$where = ' WHERE ' . $where['where'];
}

$group = '';
if ( $this->sql['group'] != '' )
{
$group = " GROUP BY " . implode( ',', array_fill( 0, count( $this->sql['group'] ) , '?' ));
foreach ( $this->sql['group'] as $val )
$data[] = $val;
}
$having = '';
if ( $this->sql['having'] != '')
{
$having = $this->comWhere( $this->sql['having'] );
foreach ( $having['data'] as $val )
$data[] = $val;
$having = ' HAVING ' . $having['where'];
}
$order = '';
if ( $this->sql['order'] != '')
{
$order = ' ORDER BY ' . $this->sql['order'][0];
}
$limit = '';
if ( $this->sql['limit'] != '' )
{
$limit = $this->comLimit( $this->sql['limit'] );
}
$sql = "SELECT $select FROM " . $this->tableName
. " $join $where $group $having $order $limit";
return $this->pdo_query( $sql, $data );
}

function queryRow()
{
$data = $this->queryAll();
$data = empty( $data[0] ) ? null : $data[0];
return $data;
}

function insert( $array = null )
{
$sql = "INSERT INTO {$this->tableName}("
. implode(',', array_keys($array)).") VALUES ("
. implode(',', array_fill(0, count($array), '?')) . ")";
return $this->pdo_query( $sql, array_values( $array ) );
}

function update( $array = null )
{
$data = array();
if ( is_array( $array ))
{
$s = '';
foreach ( $array as $k => $v )
{
$s .= " $k = ? ,";
$data[] = $v;
}
$s = rtrim( $s, ',' );
} else
{
return;
}

$limit = '';

if ( $this->sql['where'] != '' )
{
$where = $this->comWhere( $this->sql['where'] );
$sql = "UPDATE " . $this->tableName
. " SET $s WHERE " . $where['where'];
if ( ! empty( $where['data'] ))
{
foreach ( $where['data'] as $v )
$data[] = $v;
}
$order = '';
if ( $this->sql['order'] != '')
{
$order = ' ORDER BY ' . $this->sql['order'][0];
}
if ( $this->sql['limit'] != '' )
{
$limit = $this->comLimit( $this->sql['limit'] );
}
$sql .= $order . $limit;
} else
{
$sql = "UPDATE " . $this->tableName . " SET $s LIMIT 1";
}
return $this->pdo_query( $sql, $data );
}

function delete()
{
$where = '';
$data = array();

if ( $this->sql['where'] != '')
{
$where = $this->comWhere( $this->sql['where'] );
$data = $where['data'];
$where = ' WHERE ' . $where['where'];
}
$select = $this->sql['select'] != ''
? $this->sql['select'][0]
: '';

$order = '';
if ( $this->sql['order'] != '')
{
$order = ' ORDER BY ' . $this->sql['order'][0];
}

$limit = '';
if ( $this->sql['limit'] != '' )
{
$limit = $this->comLimit( $this->sql['limit'] );
}
if ( $where == '' )
$limit = ' LIMIT 1 ';

$sql = 'DELETE ' . $select. ' FROM ' . $this->tableName . " $where $order $limit ";

return $this->pdo_query( $sql, $data );
}

private function comLimit( $args )
{
if ( isset( $args[1] ))
$limit = ' LIMIT ' . (int)$args[0] . ' , ' . (int)$args[1];
else
$limit = ' LIMIT ' . (int)$args[0];
return $limit;
}

private function comWhere( $args )
{
$where = ' ';
$data = array();

if ( empty( $args ))
return array( 'where' => '' , 'data' => $data );
foreach ( $args as $option )
{
if ( empty( $option ))
{
$where .= '';
continue;
} else if ( is_array( $option ))
{
foreach ( $option as $k => $v )
{
if ( is_array( $v ))
{
if ( strpos( $k, '?' ))
$where .= $k;
else
$where .= $k . " IN (" . implode( ',', array_fill( 0, count($v), '?' )). ")";
foreach ( $v as $val )
$data[] = $val;
} else if ( strpos( $k, ' ' ))
{
$where .= $k . '?';
$data[] = $v;
} else
{
$where .= "$k = ?";
$data[] = $v;
}
$where .= ' AND ';
}
$where = rtrim( $where, 'AND ' );
$where .= ' OR ';
continue;
}
}
$where = rtrim( $where, 'OR ' );
return array( 'where' => $where, 'data' => $data );
}

public function sql( $sql, $params )
{
if ( false === strpos( $sql, '?' ))
return $sql;

$sql = str_replace( '?', "%s", $sql );
array_unshift( $params, $sql );
return call_user_func_array( 'sprintf', $params );
}

}








<?php
class pdo_db extends query_builder
{

function pdo_connect()
{
if ( $this->host == '')
$host = 'default';
else
$host = $this->host[0];

if ( empty( $this->pdo[$host] ) )
{
$config[$host]['dsn'] = 'mysql:host=mysql.win;dbname=test;charset=utf8';
$config[$host]['user'] = 'root';
$config[$host]['pass'] = '123456';
$config[$host]['prefix'] = '';
$option[$host] = array(
PDO::ATTR_DEFAULT_FETCH_MODE =>PDO::FETCH_ASSOC
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$this->pdo[$host] = new PDO( $config[$host]['dsn'], $config[$host]['user'], $config[$host]['pass'], $option[$host] );
$this->pdo[$host]->table_prefix = $config[$host]['prefix'];
}
return $this->pdo[$host];
}

function pdo_query( $sql, $data = array() )
{
try
{
$start_time = microtime();
$return = '';
$pdo = $this->pdo_connect();
$sql = str_replace( '<<_', $pdo->table_prefix, $sql );

if ( $this->text == 1 )
{
$text[] = $sql;
$text[] = $this->sql( $sql, $data );
$this->setNull();
return $text;
}

if ( strpos( $sql, '?' ) === FALSE )
{
$return = $pdo->query( $sql )->fetchAll();
if ( isset( $this->host[1] ))
unset( $this->pdo[$this->host[0]] );

$this->setNull();
return $return;
}

$prepare = $pdo->prepare( $sql );
$result = $prepare->execute( $data );

switch ( strtolower( $sql{0} ) )
{
case 's' :
$return = $prepare->fetchAll();
break;
case 'i' :
$return = $pdo->lastInsertid();
break;
case 'd' :
case 'u' :
$return = $prepare->rowCount();
break;
default :
$return = $prepare->fetchAll();
}

if ( isset( $this->host[1] ))
unset( $this->pdo[$this->host[0]] );

$end_time = microtime();
$end_time = explode( ' ', $end_time );
$start_time = explode( ' ', $start_time );
$end_time = number_format( ( $end_time[0] + $end_time[1] ) - ( $start_time[0] + $start_time[1] ), 8);
$this->setNull();
$this->queries[] = array( $end_time, $this->sql( $sql, $data ) . "\n" . $sql );
return $return;

}catch( PDOException $e )
{
echo $e->getMessage();
}

}

public function begintransaction()
{
$pdo = $this->pdo_connect();
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);
$pdo->beginTransaction();
}

public function commit()
{
$pdo = $this->pdo_connect();
$pdo->commit();
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
}

public function rollback()
{
$pdo = $this->pdo_connect();
$pdo->rollBack();
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);
}
public function dbVersion()
{
return $this->pdo_connect()
->getAttribute( PDO::ATTR_SERVER_VERSION );
}

}






<?php

include 'sql_build_query.php';
include 'pdo_query.php';
$db = new pdo_db;


$sql = $db->pdo_query('show databases ');


var_dump( $sql, $db->dbVersion() );


exit;

$sql = $db->select( 'i.kd k, io.id dk, iil.ii ds' )->from( 'test t' )
->leftjoin( 'post p', 't.id = ?', array( 2 ) )
->order( 'name DESC, id ASC' )
->where( array( 'xin = ? AND do > ?' => array( 1,3 )))
->having( array('xin' => 'dkls' ))
->group( 'id' )
->limit( 3,4 )
->queryAll();
exit;
//var_dump( $sql );

$sql = $db->from( 'test')
->insert( array( 'kd' => '233', 'dkwe' => 323 ));


$sql = $db->from( 'test' )
->where( array( '( dk = ? and kd > ?) or ( dk = ? and ddd = ?)' => array( 2,3,5,4 )))
->limit( 2 , 4 )
->update( array( 'dkddl' => 'd', 'kdls' => 'dkdl' ));


$sql = $db->from( 'test,test' )
->select( 'test,test' )
->where( array( 'id' => array( 3,3,4,2,3) ))
->limit( '3', 3)
->delete();