本篇内容介绍了“php链式操作mysql数据库的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
封装类常用操作使用示例
// 初始化db连接
$db = new WorkermanMySQLConnection('host', 'port', 'user', 'password', 'db_name');
// 获取所有数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
//等价于
$db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1");
// 获取一行数据
$db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row();
//等价于
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
//等价于
$db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'");
// 获取一列数据
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
//等价于
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");
// 获取单个值
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
//等价于
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
//等价于
$db->single("SELECT ID FROM `Persons` WHERE sex='M'");
// 复杂查询
$db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did'))
->limit(10)->offset(20)->bindValues(array('age' => 13));
// 等价于
$db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid`
WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20');
// 插入
$insert_id = $db->insert('Persons')->cols(array(
'Firstname'=>'abc',
'Lastname'=>'efg',
'Sex'=>'M',
'Age'=>13))->query();
等价于
$insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`)
VALUES ( 'abc', 'efg', 'M', 13)");
// 更新
$row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1')
->bindValue('sex', 'F')->query();
// 等价于
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// 等价于
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");
// 删除
$row_count = $db->delete('Persons')->where('ID=9')->query();
// 等价于
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");
// 事务
$db->beginTrans();
....
$db->commitTrans(); // or $db->rollBackTrans();封装源码（保存文件引用）
<?php
/**
* 数据库连接类，依赖 PDO_MYSQL 扩展
* 在 https://github.com/auraphp/Aura.SqlQuery 的基础上修改而成
*/
class Connection
{
/**
* SELECT
*
* @var array
*/
protected $union = array();
/**
* 是否是更新
*
* @var bool
*/
protected $for_update = false;
/**
* 选择的列
*
* @var array
*/
protected $cols = array();
/**
* 从哪些表里面 SELECT
*
* @var array
*/
protected $from = array();
/**
* $from 当前的 key
*
* @var int
*/
protected $from_key = -1;
/**
* GROUP BY 的列
*
* @var array
*/
protected $group_by = array();
/**
* HAVING 条件数组.
*
* @var array
*/
protected $having = array();
/**
* HAVING 语句中绑定的值.
*
* @var array
*/
protected $bind_having = array();
/**
* 每页多少条记录
*
* @var int
*/
protected $paging = 10;
/**
* sql 中绑定的值
*
* @var array
*/
protected $bind_values = array();
/**
* WHERE 条件.
*
* @var array
*/
protected $where = array();
/**
* WHERE 语句绑定的值
*
* @var array
*/
protected $bind_where = array();
/**
* ORDER BY 的列
*
* @var array
*/
protected $order_by = array();
/**
* ORDER BY 的排序方式,默认为升序
*
* @var bool
*/
protected $order_asc = true;
/**
* SELECT 多少记录
*
* @var int
*/
protected $limit = 0;
/**
* 返回记录的游标
*
* @var int
*/
protected $offset = 0;
/**
* flags 列表
*
* @var array
*/
protected $flags = array();
/**
* 操作哪个表
*
* @var string
*/
protected $table;
/**
* 表.列 和 last-insert-id 映射
*
* @var array
*/
protected $last_insert_id_names = array();
/**
* INSERT 或者 UPDATE 的列
*
* @param array
*/
protected $col_values;
/**
* 返回的列
*
* @var array
*/
protected $returning = array();
/**
* sql 的类型 SELECT INSERT DELETE UPDATE
*
* @var string
*/
protected $type = '';
/**
* pdo 实例
*
* @var PDO
*/
protected $pdo;
/**
* PDOStatement 实例
*
* @var PDOStatement
*/
protected $sQuery;
/**
* 数据库用户名密码等配置
*
* @var array
*/
protected $settings = array();
/**
* sql 的参数
*
* @var array
*/
protected $parameters = array();
/**
* 最后一条直行的 sql
*
* @var string
*/
protected $lastSql = '';
/**
* 是否执行成功
*
* @var bool
*/
protected $success = false;
/**
* 选择哪些列
*
* @param string|array $cols
* @return self
*/
public function select($cols = '*')
{
$this->type = 'SELECT';
if (!is_array($cols)) {
$cols = explode(',', $cols);
}
$this->cols($cols);
return $this;
}
/**
* 从哪个表删除
*
* @param string $table
* @return self
*/
public function delete($table)
{
$this->type = 'DELETE';
$this->table = $this->quoteName($table);
$this->fromRaw($this->quoteName($table));
return $this;
}
/**
* 更新哪个表
*
* @param string $table
* @return self
*/
public function update($table)
{
$this->type = 'UPDATE';
$this->table = $this->quoteName($table);
return $this;
}
/**
* 向哪个表插入
*
* @param string $table
* @return self
*/
public function insert($table)
{
$this->type = 'INSERT';
$this->table = $this->quoteName($table);
return $this;
}
/**
*
* 设置 SQL_CALC_FOUND_ROWS 标记.
*
* @param bool $enable
* @return self
*/
public function calcFoundRows($enable = true)
{
$this->setFlag('SQL_CALC_FOUND_ROWS', $enable);
return $this;
}
/**
* 设置 SQL_CACHE 标记
*
* @param bool $enable
* @return self
*/
public function cache($enable = true)
{
$this->setFlag('SQL_CACHE', $enable);
return $this;
}
/**
* 设置 SQL_NO_CACHE 标记
*
* @param bool $enable
* @return self
*/
public function noCache($enable = true)
{
$this->setFlag('SQL_NO_CACHE', $enable);
return $this;
}
/**
* 设置 STRAIGHT_JOIN 标记.
*
* @param bool $enable
* @return self
*/
public function straightJoin($enable = true)
{
$this->setFlag('STRAIGHT_JOIN', $enable);
return $this;
}
/**
* 设置 HIGH_PRIORITY 标记
*
* @param bool $enable
* @return self
*/
public function highPriority($enable = true)
{
$this->setFlag('HIGH_PRIORITY', $enable);
return $this;
}
/**
* 设置 SQL_SMALL_RESULT 标记
*
* @param bool $enable
* @return self
*/
public function smallResult($enable = true)
{
$this->setFlag('SQL_SMALL_RESULT', $enable);
return $this;
}
/**
* 设置 SQL_BIG_RESULT 标记
*
* @param bool $enable
* @return self
*/
public function bigResult($enable = true)
{
$this->setFlag('SQL_BIG_RESULT', $enable);
return $this;
}
/**
* 设置 SQL_BUFFER_RESULT 标记
*
* @param bool $enable
* @return self
*/
public function bufferResult($enable = true)
{
$this->setFlag('SQL_BUFFER_RESULT', $enable);
return $this;
}
/**
* 设置 FOR UPDATE 标记
*
* @param bool $enable
* @return self
*/
public function forUpdate($enable = true)
{
$this->for_update = (bool)$enable;
return $this;
}
/**
* 设置 DISTINCT 标记
*
* @param bool $enable
* @return self
*/
public function distinct($enable = true)
{
$this->setFlag('DISTINCT', $enable);
return $this;
}
/**
* 设置 LOW_PRIORITY 标记
*
* @param bool $enable
* @return self
*/
public function lowPriority($enable = true)
{
$this->setFlag('LOW_PRIORITY', $enable);
return $this;
}
/**
* 设置 IGNORE 标记
*
* @param bool $enable
* @return self
*/
public function ignore($enable = true)
{
$this->setFlag('IGNORE', $enable);
return $this;
}
/**
* 设置 QUICK 标记
*
* @param bool $enable
* @return self
*/
public function quick($enable = true)
{
$this->setFlag('QUICK', $enable);
return $this;
}
/**
* 设置 DELAYED 标记
*
* @param bool $enable
* @return self
*/
public function delayed($enable = true)
{
$this->setFlag('DELAYED', $enable);
return $this;
}
/**
* 序列化
*
* @return string
*/
public function __toString()
{
$union = '';
if ($this->union) {
$union = implode(' ', $this->union) . ' ';
}
return $union . $this->build();
}
/**
* 设置每页多少条记录
*
* @param int $paging
* @return self
*/
public function setPaging($paging)
{
$this->paging = (int)$paging;
return $this;
}
/**
* 获取每页多少条记录
*
* @return int
*/
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");
}
}
/**
* 获取绑定在占位符上的值
*
* @return array
*/
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;
}
/**
*
* SELECT选择哪些列
*
* @param mixed $key
* @param string $val
* @return void
*/
protected function addColSELECT($key, $val)
{
if (is_string($key)) {
$this->cols[$val] = $key;
} else {
$this->addColWithAlias($val);
}
}
/**
* SELECT 增加选择的列
*
* @param string $spec
*/
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);
}
}
/**
* from 哪个表
*
* @param string $table
* @return self
*/
public function from($table)
{
return $this->fromRaw($this->quoteName($table));
}
/**
* from的表
*
* @param string $table
* @return self
*/
public function fromRaw($table)
{
$this->from[] = array($table);
$this->from_key++;
return $this;
}
/**
*
* 子查询
*
* @param string $table
* @param string $name The alias name for the sub-select.
* @return self
*/
public function fromSubSelect($table, $name)
{
$this->from[] = array("($table) AS " . $this->quoteName($name));
$this->from_key++;
return $this;
}
/**
* 增加 join 语句
*
* @param string $table
* @param string $cond
* @param string $type
* @return self
* @throws Exception
*/
public function join($table, $cond = null, $type = '')
{
return $this->joinInternal($type, $table, $cond);
}
/**
* 增加 join 语句
*
* @param string $join inner, left, natural
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
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;
}
/**
* quote
*
* @param string $cond
* @return string
*
*/
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;
}
/**
* inner join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function innerJoin($table, $cond = null)
{
return $this->joinInternal('INNER', $table, $cond);
}
/**
* left join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function leftJoin($table, $cond = null)
{
return $this->joinInternal('LEFT', $table, $cond);
}
/**
* right join
*
* @param string $table
* @param string $cond
* @return self
* @throws Exception
*/
public function rightJoin($table, $cond = null)
{
return $this->joinInternal('RIGHT', $table, $cond);
}
/**
* joinSubSelect
*
* @param string $join inner, left, natural
* @param string $spec
* @param string $name sub-select 的别名
* @param string $cond
* @return self
* @throws Exception
*/
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;
}
/**
* group by 语句
*
* @param array $cols
* @return self
*/
public function groupBy(array $cols)
{
foreach ($cols as $col) {
$this->group_by[] = $this->quoteNamesIn($col);
}
return $this;
}
/**
* having 语句
*
* @param string $cond
* @return self
*/
public function having($cond)
{
$this->addClauseCondWithBind('having', 'AND', func_get_args());
return $this;
}
/**
* or having 语句
*
* @param string $cond The HAVING condition.
* @return self
*/
public function orHaving($cond)
{
$this->addClauseCondWithBind('having', 'OR', func_get_args());
return $this;
}
/**
* 设置每页的记录数量
*
* @param int $page
* @return self
*/