php链式操作mysql数据库(封装类带使用示例)（php连接数据库的方法是什么）不看后悔

随心笔谈2年前发布编辑

183 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

<?php

class Connection
{

protected $union=array();

protected $for_update=false;

protected $cols=array();

protected $from=array();

protected $from_key=-1;

protected $group_by=array();

protected $having=array();

protected $bind_having=array();

protected $paging=10;

protected $bind_values=array();

protected $where=array();

protected $bind_where=array();

protected $order_by=array();

protected $order_asc=true;

protected $limit=0;

protected $offset=0;

protected $flags=array();

protected $table;

protected $last_insert_id_names=array();

protected $col_values;

protected $returning=array();

protected $type=”;

protected $pdo;

protected $sQuery;

protected $settings=array();

protected $parameters=array();

protected $lastSql=”;

protected $success=false;

public function select($cols=’*’)
{
$this->type=’SELECT’;
if (!is_array($cols)) {
$cols=explode(‘,’, $cols);
}
$this->cols($cols);
return $this;
}

public function delete($table)
{
$this->type=’DELETE’;
$this->table=$this->quoteName($table);
$this->fromRaw($this->quoteName($table));
return $this;
}

public function update($table)
{
$this->type=’UPDATE’;
$this->table=$this->quoteName($table);
return $this;
}

public function insert($table)
{
$this->type=’INSERT’;
$this->table=$this->quoteName($table);
return $this;
}

public function calcFoundRows($enable=true)
{
$this->setFlag(‘SQL_CALC_FOUND_ROWS’, $enable);
return $this;
}

public function cache($enable=true)
{
$this->setFlag(‘SQL_CACHE’, $enable);
return $this;
}

public function noCache($enable=true)
{
$this->setFlag(‘SQL_NO_CACHE’, $enable);
return $this;
}

public function straightJoin($enable=true)
{
$this->setFlag(‘STRAIGHT_JOIN’, $enable);
return $this;
}

public function highPriority($enable=true)
{
$this->setFlag(‘HIGH_PRIORITY’, $enable);
return $this;
}

public function smallResult($enable=true)
{
$this->setFlag(‘SQL_SMALL_RESULT’, $enable);
return $this;
}

public function bigResult($enable=true)
{
$this->setFlag(‘SQL_BIG_RESULT’, $enable);
return $this;
}

public function bufferResult($enable=true)
{
$this->setFlag(‘SQL_BUFFER_RESULT’, $enable);
return $this;
}

public function forUpdate($enable=true)
{
$this->for_update=(bool)$enable;
return $this;
}

public function distinct($enable=true)
{
$this->setFlag(‘DISTINCT’, $enable);
return $this;
}

public function lowPriority($enable=true)
{
$this->setFlag(‘LOW_PRIORITY’, $enable);
return $this;
}

public function ignore($enable=true)
{
$this->setFlag(‘IGNORE’, $enable);
return $this;
}

public function quick($enable=true)
{
$this->setFlag(‘QUICK’, $enable);
return $this;
}

public function delayed($enable=true)
{
$this->setFlag(‘DELAYED’, $enable);
return $this;
}

public function __toString()
{
$union=”;
if ($this->union) {
$union=implode(‘ ‘, $this->union) . ‘ ‘;
}
return $union . $this->build();
}

public function setPaging($paging)
{
$this->paging=(int)$paging;
return $this;
}

public function getPaging()
{
return $this->paging;
}

public function getBindValues()
{
switch ($this->type) {
case ‘SELECT’:
return $this->getBindValuesSELECT();
case ‘DELETE’:
case ‘UPDATE’:
case ‘INSERT’:
return $this->getBindValuesCOMMON();
default :
throw new Exception(“type err”);
}
}

public function getBindValuesSELECT()
{
$bind_values=$this->bind_values;
$i =1;
foreach ($this->bind_where as $val) {
$bind_values[$i]=$val;
$i++;
}
foreach ($this->bind_having as $val) {
$bind_values[$i]=$val;
$i++;
}
return $bind_values;
}

protected function addColSELECT($key, $val)
{
if (is_string($key)) {
$this->cols[$val]=$key;
} else {
$this->addColWithAlias($val);
}
}

protected function addColWithAlias($spec)
{
$parts=explode(‘ ‘, $spec);
$count=count($parts);
if ($count==2 && trim($parts[0]) !=” && trim($parts[1]) !=”) {
$this->cols[$parts[1]]=$parts[0];
} elseif ($count==3 && strtoupper($parts[1])==’AS’) {
$this->cols[$parts[2]]=$parts[0];
} else {
$this->cols[]=trim($spec);
}
}

public function from($table)
{
return $this->fromRaw($this->quoteName($table));
}

public function fromRaw($table)
{
$this->from[]=array($table);
$this->from_key++;
return $this;
}

public function fromSubSelect($table, $name)
{
$this->from[]=array(“($table) AS ” . $this->quoteName($name));
$this->from_key++;
return $this;
}

public function join($table, $cond=null, $type=”)
{
return $this->joinInternal($type, $table, $cond);
}

protected function joinInternal($join, $table, $cond=null)
{
if (!$this->from) {
throw new Exception(‘Cannot join() without from()’);
}

$join =strtoupper(ltrim(“$join JOIN”));
$table =$this->quoteName($table);
$cond =$this->fixJoinCondition($cond);
$this->from[$this->from_key][]=rtrim(“$join $table $cond”);
return $this;
}

protected function fixJoinCondition($cond)
{
if (!$cond) {
return ”;
}

$cond=$this->quoteNamesIn($cond);

if (strtoupper(substr(ltrim($cond), 0, 3))==’ON ‘) {
return $cond;
}

if (strtoupper(substr(ltrim($cond), 0, 6))==’USING ‘) {
return $cond;
}

return ‘ON ‘ . $cond;
}

public function innerJoin($table, $cond=null)
{
return $this->joinInternal(‘INNER’, $table, $cond);
}

public function leftJoin($table, $cond=null)
{
return $this->joinInternal(‘LEFT’, $table, $cond);
}

public function rightJoin($table, $cond=null)
{
return $this->joinInternal(‘RIGHT’, $table, $cond);
}

public function joinSubSelect($join, $spec, $name, $cond=null)
{
if (!$this->from) {
throw new \Exception(‘Cannot join() without from() first.’);
}

$join =strtoupper(ltrim(“$join JOIN”));
$name =$this->quoteName($name);
$cond =$this->fixJoinCondition($cond);
$this->from[$this->from_key][]=rtrim(“$join ($spec) AS $name $cond”);
return $this;
}

public function groupBy(array $cols)
{
foreach ($cols as $col) {
$this->group_by[]=$this->quoteNamesIn($col);
}
return $this;
}

public function having($cond)
{
$this->addClauseCondWithBind(‘having’, ‘AND’, func_get_args());
return $this;
}

public function orHaving($cond)
{
$this->addClauseCondWithBind(‘having’, ‘OR’, func_get_args());
return $this;
}

public function page($page)
{
$this->limit=0;
$this->offset=0;

$page=(int)$page;
if ($page > 0) {
$this->limit=$this->paging;
$this->offset=$this->paging * ($page – 1);
}
return $this;
}

public function union()
{
$this->union[]=$this->build() . ‘ UNION’;
$this->reset();
return $this;
}

public function unionAll()
{
$this->union[]=$this->build() . ‘ UNION ALL’;
$this->reset();
return $this;
}

protected function reset()
{
$this->resetFlags();
$this->cols=array();
$this->from=array();
$this->from_key=-1;
$this->where=array();
$this->group_by=array();
$this->having=array();
$this->order_by=array();
$this->limit=0;
$this->offset=0;
$this->for_update=false;
}

protected function resetAll()
{
$this->union =array();
$this->for_update =false;
$this->cols =array();
$this->from =array();
$this->from_key =-1;
$this->group_by =array();
$this->having =array();
$this->bind_having=array();
$this->paging =10;
$this->bind_values=array();
$this->where =array();
$this->bind_where =array();
$this->order_by =array();
$this->limit =0;
$this->offset =0;
$this->flags =array();
$this->table =”;
$this->last_insert_id_names=array();
$this->col_values =array();
$this->returning =array();
$this->parameters =array();
}

protected function buildSELECT()
{
return ‘SELECT’
. $this->buildFlags()
. $this->buildCols()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildGroupBy()
. $this->buildHaving()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildForUpdate();
}

protected function buildDELETE()
{
return ‘DELETE’
. $this->buildFlags()
. $this->buildFrom()
. $this->buildWhere()
. $this->buildOrderBy()
. $this->buildLimit()
. $this->buildReturning();
}

protected function buildCols()
{
if (!$this->cols) {
throw new Exception(‘No columns in the SELECT.’);
}

$cols=array();
foreach ($this->cols as $key=> $val) {
if (is_int($key)) {
$cols[]=$this->quoteNamesIn($val);
} else {
$cols[]=$this->quoteNamesIn(“$val AS $key”);
}
}

return $this->indentCsv($cols);
}

protected function buildFrom()
{
if (!$this->from) {
return ”;
}

$refs=array();
foreach ($this->from as $from) {
$refs[]=implode(‘ ‘, $from);
}
return ‘ FROM’ . $this->indentCsv($refs);
}

protected function buildGroupBy()
{
if (!$this->group_by) {
return ”;
}
return ‘ GROUP BY’ . $this->indentCsv($this->group_by);
}

protected function buildHaving()
{
if (!$this->having) {
return ”;
}
return ‘ HAVING’ . $this->indent($this->having);
}

protected function buildForUpdate()
{
if (!$this->for_update) {
return ”;
}
return ‘ FOR UPDATE’;
}

public function where($cond)
{
if (is_array($cond)) {
foreach ($cond as $key=> $val) {
if (is_string($key)) {
$this->addWhere(‘AND’, array($key, $val));
} else {
$this->addWhere(‘AND’, array($val));
}
}
} else {
$this->addWhere(‘AND’, func_get_args());
}
return $this;
}

public function orWhere($cond)
{
if (is_array($cond)) {
foreach ($cond as $key=> $val) {
if (is_string($key)) {
$this->addWhere(‘OR’, array($key, $val));
} else {
$this->addWhere(‘OR’, array($val));
}
}
} else {
$this->addWhere(‘OR’, func_get_args());
}
return $this;
}

public function limit($limit)
{
$this->limit=(int)$limit;
return $this;
}

public function offset($offset)
{
$this->offset=(int)$offset;
return $this;
}

public function orderBy(array $cols)
{
return $this->addOrderBy($cols);
}

public function orderByASC(array $cols, $order_asc=true)
{
$this->order_asc=$order_asc;
return $this->addOrderBy($cols);
}

public function orderByDESC(array $cols)
{
$this->order_asc=false;
return $this->addOrderBy($cols);
}

// ————-abstractquery———-

protected function indentCsv(array $list)
{
return ‘ ‘ . implode(‘,’, $list);
}

protected function indent(array $list)
{
return ‘ ‘ . implode(‘ ‘, $list);
}

public function bindValues(array $bind_values)
{
foreach ($bind_values as $key=> $val) {
$this->bindValue($key, $val);
}
return $this;
}

public function bindValue($name, $value)
{
$this->bind_values[$name]=$value;
return $this;
}

protected function buildFlags()
{
if (!$this->flags) {
return ”;
}
return ‘ ‘ . implode(‘ ‘, array_keys($this->flags));
}

protected function setFlag($flag, $enable=true)
{
if ($enable) {
$this->flags[$flag]=true;
} else {
unset($this->flags[$flag]);
}
}