当前位置：首页 → 问答吧 → 贴一段哥写的代码．．一个简单的封装select查询

贴一段哥写的代码．．一个简单的封装select查询

时间：2010-07-15

来源：互联网

<?php
/************************************************************
* 封装数据库查询语句
*
* @filename: Select.Class.php 2010-03-23 13:22
* @copyright: Copyright by 2007 - 2010
* @author: <[email protected]> yjj
* @link:
***********************************************************/
class Select {
/**
* 当前数据库连接实例
* @var db_resurce
*/
protected $_db = null;
/**
* SQL 语句
* @var string
*/
protected $_sql = 'SELECT';
/**
* 所要查询的字段
* @var string
*/
protected $_filed = '*';
/**
* 所要查询的表
* @var string
*/
protected $_from = '';
/**
* 查询条件 WHERE 子句
* @var string
*/
protected $_where = '';
/**
* limit 子句
* @var string
*/
protected $_limit = '';
/**
* ORDER 子句
* @var string
*/
protected $_order = '';
/**
* 构造函数
* @param array $filed //需要查询的字段
* @return void
*/
public function __construct($db_object = null) {
if (!empty($db_object)) {
$this->_db = $db_object;
}
}
/**
* SQL FROM 语句支持多表查询
*
* ＜CODE＞
* $db->select()->from(
* array('alias' => table1, //数组的KEY为表的别名
* 'alias1' => table2,
* ... ...
* ),
* array('alias' => fileds,
* 'alias1' => fileds1
* ... ...
* )
* )->query();
* ＜/code＞
* ＜CODE1＞
* $db->select()->from(
* array(table1,
* table2,
* ... ...
* ),
* array('feild',
* 'feild1',
* ... ...
* )
* )->query();
* ＜/CODE＞
* ＜CODE1＞
* $db->select()->from(
* 'table1,
* table2,
* ... ...
* ',
* 'feild,
* feild1,
* ... ...'
* )
* )->query();
* ＜/CODE＞
*
* @param string|array $tablename //表名
* @param array|string $fileds //字段
* @return Object_Select
*/
public function from($tablename = null, $fileds = null) {
if ($tablename === null) throw new Db_Select_Exception('This DB tablename is null');
//支持多表联合查询
if (is_array($tablename)) {
$ar_table_name = array();
foreach ($tablename as $k => $v) {
if (is_int($k)) {
$ar_table_name[] = sprintf('`%s`', $v);
} else {
$ar_table_name[] = sprintf('`%s` AS %s', $v, $k);
}
}
$this->_from = sprintf('FROM %s', implode(',', $ar_table_name));
} else {
$this->_from = sprintf('FROM `%s`', $tablename);
}
//支持自定义多字段查询
if (!empty($fileds)) {
if (is_array($fileds)) {
$ar_fileds_name = array();
foreach ($fileds as $k => $v) {
if (is_int($k)) {
$ar_fileds_name[] = sprintf('`%s`', $v);
} else {
$ar_fileds_name[] = sprintf('`%s` AS %s', $v, $k);
}
}
$this->_filed = implode(',', $ar_fileds_name);
} else {
$this->_filed = $fileds;
}
}
return $this;
}
/**
* 封装WHERE 子句
* ＜code＞
* $db->select()->from('table1')->where(
* array(
* 'field' => condition,
* 'field1' => condition,
* 'field2' => condition
* )
* )->query();
* ＜/code＞
*
* ＜CODE1＞
* $db->select()->from('table1')->where(
* 'a > b and c < d'
* )->query();
* ＜/CODE1＞
*
* @param array|string $where
* @return Object_Select
*/
public function where($where = null) {
if (!empty($where)) {
if (empty($this->_where)) $this->_where = 'WHERE ';
if (is_array($where)) {
$where_link = array();
foreach ($where as $k => $v) {
$where_link[] = sprintf('`%s` = \'%s\'', $k, $v);
}
$this->_where .= implode(' AND ', $where_link);
} else {
$this->_where .= $where;
}
}
return $this;
}
/**
* 封装LIMIT子句
* @param int $start
* @param int $end
* @return Object_Select
*/
public function limit($start = 0, $end = 30) {
$this->_limit = sprintf('LIMIT %d,%d', $start, $end);
return $this;
}
/**
* 封装 ORDER 子句
* ＜code＞
* $db->select()->from()->order(
* array('fields1 desc', 'fields2 ASC', ... ...)
* )->query();
* ＜/code＞
*
* ＜code＞
* $db->select()->from()->order(
* 'fields desc'
* )->query();
* ＜/code＞
*
* @param string|array $order
* @return Object_Select
*/
public function order($order = null) {
if (!empty($order)) {
$this->_order = 'ORDER BY ';
if (is_array($order)) {
$this->_order .= implode(',', $order);
} else {
$this->_order .= $order;
}
}
return $this;
}
/**
* 封装 SQL COUNT()
*
* @param string $filed
* @return Object_Select
*/
public function count($filed = '*', $row = 'row_result') {
if ('*' === $filed) {
$this->_filed = sprintf('COUNT(%s) AS %s', $filed, $row);
} else {
$this->_filed = sprintf('COUNT(`%s`) AS %s', $filed, $row);
}
return $this;
}
/**
* 封装 SQL SUM()
*
* @param string $filed
* @return Object_Select
*/
public function sum($filed = null) {
if (!empty($filed)) {
$this->_filed = sprintf('SUM(`%s`)', $filed);
}
return $this;
}
/**
* 构造SQL 语句
*/
protected function _setSqlString() {
$sql_array = array();
$sql_array[] = $this->_filed;
if (!empty($this->_from)) {
$sql_array[] = $this->_from;
}
if (!empty($this->_where)) {
$sql_array[] = $this->_where;
}
if (!empty($this->_order)) {
$sql_array[] = $this->_order;
}
if (!empty($this->_limit)) {
$sql_array[] = $this->_limit;
}
$this->_sql .= ' ' . implode(' ', $sql_array);
}
/**
* 获得当前SQL语句
* @return string
*/
public function __toString() {
$this->_setSqlString();
return $this->_sql;
}
/**
* 返回查询结果
* @return array
*/
public function query() {
$this->_setSqlString();
return $this->_db->execute($this->_sql);
}
/**
* 重新聚合查询结果
* @param string $fileds
* @return array
*/
public function fetchAssoc($fileds = null) {
$result = array();
$rs = $this->query();
foreach($rs as $v) {
if (null === $fileds) {
if (isset($v['id'])) {
$fileds = 'id';
}
}
$result[$v[$fileds]] = $v;
}
return $result;
}
}
class Select_Db_Exception extends Exception {
public function __construct($info) {
parent::_construct($info);
}
}
?>

复制代码