本篇内容介绍了“php链式操作mysql数据库的方法是什么”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
// 初始化db连接 $db = new \Workerman\MySQL\Connection('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 */ 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; } /** * union * * @return self */ public function union() { $this->union[] = $this->build() . ' UNION'; $this->reset(); return $this; } /** * unionAll * * @return self */ 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(); } /** * 创建 SELECT SQL * * @return string */ protected function buildSELECT() { return 'SELECT' . $this->buildFlags() . $this->buildCols() . $this->buildFrom() . $this->buildWhere() . $this->buildGroupBy() . $this->buildHaving() . $this->buildOrderBy() . $this->buildLimit() . $this->buildForUpdate(); } /** * 创建 DELETE SQL */ protected function buildDELETE() { return 'DELETE' . $this->buildFlags() . $this->buildFrom() . $this->buildWhere() . $this->buildOrderBy() . $this->buildLimit() . $this->buildReturning(); } /** * 生成 SELECT 列语句 * * @return string * @throws Exception */ 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); } /** * 生成 FROM 语句. * * @return string */ protected function buildFrom() { if (!$this->from) { return ''; } $refs = array(); foreach ($this->from as $from) { $refs[] = implode(' ', $from); } return ' FROM' . $this->indentCsv($refs); } /** * 生成 GROUP BY 语句. * * @return string */ protected function buildGroupBy() { if (!$this->group_by) { return ''; } return ' GROUP BY' . $this->indentCsv($this->group_by); } /** * 生成 HAVING 语句. * * @return string */ protected function buildHaving() { if (!$this->having) { return ''; } return ' HAVING' . $this->indent($this->having); } /** * 生成 FOR UPDATE 语句 * * @return string */ protected function buildForUpdate() { if (!$this->for_update) { return ''; } return ' FOR UPDATE'; } /** * where * * @param string|array $cond * @return self */ 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; } /** * or where * * @param string|array $cond * @return self */ 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; } /** * limit * * @param int $limit * @return self */ public function limit($limit) { $this->limit = (int)$limit; return $this; } /** * limit offset * * @param int $offset * @return self */ public function offset($offset) { $this->offset = (int)$offset; return $this; } /** * orderby. * * @param array $cols * @return self */ public function orderBy(array $cols) { return $this->addOrderBy($cols); } /** * order by ASC OR DESC * * @param array $cols * @param bool $order_asc * @return self */ public function orderByASC(array $cols, $order_asc = true) { $this->order_asc = $order_asc; return $this->addOrderBy($cols); } /** * order by DESC * * @param array $cols * @return self */ public function orderByDESC(array $cols) { $this->order_asc = false; return $this->addOrderBy($cols); } // -------------abstractquery---------- /** * 返回逗号分隔的字符串 * * @param array $list * @return string */ protected function indentCsv(array $list) { return ' ' . implode(',', $list); } /** * 返回空格分隔的字符串 * * @param array $list * @return string */ protected function indent(array $list) { return ' ' . implode(' ', $list); } /** * 批量为占位符绑定值 * * @param array $bind_values * @return self * */ public function bindValues(array $bind_values) { foreach ($bind_values as $key => $val) { $this->bindValue($key, $val); } return $this; } /** * 单个为占位符绑定值 * * @param string $name * @param mixed $value * @return self */ public function bindValue($name, $value) { $this->bind_values[$name] = $value; return $this; } /** * 生成 flag * * @return string */ protected function buildFlags() { if (!$this->flags) { return ''; } return ' ' . implode(' ', array_keys($this->flags)); } /** * 设置 flag. * * @param string $flag * @param bool $enable */ protected function setFlag($flag, $enable = true) { if ($enable) { $this->flags[$flag] = true; } else { unset($this->flags[$flag]); } } /** * 重置 flag */ protected function resetFlags() { $this->flags = array(); } /** * * 添加 where 语句 * * @param string $andor 'AND' or 'OR * @param array $conditions * @return self * */ protected function addWhere($andor, $conditions) { $this->addClauseCondWithBind('where', $andor, $conditions); return $this; } /** * 添加条件和绑定值 * * @param string $clause where 、having等 * @param string $andor AND、OR等 * @param array $conditions */ protected function addClauseCondWithBind($clause, $andor, $conditions) { $cond = array_shift($conditions); $cond = $this->quoteNamesIn($cond); $bind =& $this->{"bind_{$clause}"}; foreach ($conditions as $value) { $bind[] = $value; } $clause =& $this->$clause; if ($clause) { $clause[] = "$andor $cond"; } else { $clause[] = $cond; } } /** * 生成 where 语句 * * @return string */ protected function buildWhere() { if (!$this->where) { return ''; } return ' WHERE' . $this->indent($this->where); } /** * 增加 order by * * @param array $spec The columns and direction to order by. * @return self */ protected function addOrderBy(array $spec) { foreach ($spec as $col) { $this->order_by[] = $this->quoteNamesIn($col); } return $this; } /** * 生成 order by 语句 * * @return string */ protected function buildOrderBy() { if (!$this->order_by) { return ''; } if ($this->order_asc) { return ' ORDER BY' . $this->indentCsv($this->order_by) . ' ASC'; } else { return ' ORDER BY' . $this->indentCsv($this->order_by) . ' DESC'; } } /** * 生成 limit 语句 * * @return string */ protected function buildLimit() { $has_limit = $this->type == 'DELETE' || $this->type == 'UPDATE'; $has_offset = $this->type == 'SELECT'; if ($has_offset && $this->limit) { $clause = " LIMIT {$this->limit}"; if ($this->offset) { $clause .= " OFFSET {$this->offset}"; } return $clause; } elseif ($has_limit && $this->limit) { return " LIMIT {$this->limit}"; } return ''; } /** * Quotes * * @param string $spec * @return string|array */ public function quoteName($spec) { $spec = trim($spec); $seps = array(' AS ', ' ', '.'); foreach ($seps as $sep) { $pos = strripos($spec, $sep); if ($pos) { return $this->quoteNameWithSeparator($spec, $sep, $pos); } } return $this->replaceName($spec); } /** * 指定分隔符的 Quotes * * @param string $spec * @param string $sep * @param int $pos * @return string */ protected function quoteNameWithSeparator($spec, $sep, $pos) { $len = strlen($sep); $part1 = $this->quoteName(substr($spec, 0, $pos)); $part2 = $this->replaceName(substr($spec, $pos + $len)); return "{$part1}{$sep}{$part2}"; } /** * Quotes "table.col" 格式的字符串 * * @param string $text * @return string|array */ public function quoteNamesIn($text) { $list = $this->getListForQuoteNamesIn($text); $last = count($list) - 1; $text = null; foreach ($list as $key => $val) { if (($key + 1) % 3) { $text .= $this->quoteNamesInLoop($val, $key == $last); } } return $text; } /** * 返回 quote 元素列表 * * @param string $text * @return array */ protected function getListForQuoteNamesIn($text) { $apos = "'"; $quot = '"'; return preg_split( "/(($apos+|$quot+|\\$apos+|\\$quot+).*?\\2)/", $text, -1, PREG_SPLIT_DELIM_CAPTURE ); } /** * 循环 quote * * @param string $val * @param bool $is_last * @return string */ protected function quoteNamesInLoop($val, $is_last) { if ($is_last) { return $this->replaceNamesAndAliasIn($val); } return $this->replaceNamesIn($val); } /** * 替换成别名 * * @param string $val * @return string */ protected function replaceNamesAndAliasIn($val) { $quoted = $this->replaceNamesIn($val); $pos = strripos($quoted, ' AS '); if ($pos !== false) { $bracket = strripos($quoted, ')'); if ($bracket === false) { $alias = $this->replaceName(substr($quoted, $pos + 4)); $quoted = substr($quoted, 0, $pos) . " AS $alias"; } } return $quoted; } /** * Quotes name * * @param string $name * @return string */ protected function replaceName($name) { $name = trim($name); if ($name == '*') { return $name; } return '`' . $name . '`'; } /** * Quotes * * @param string $text * @return string|array */ protected function replaceNamesIn($text) { $is_string_literal = strpos($text, "'") !== false || strpos($text, '"') !== false; if ($is_string_literal) { return $text; } $word = '[a-z_][a-z0-9_]*'; $find = "/(\\b)($word)\\.($word)(\\b)/i"; $repl = '$1`$2`.`$3`$4'; $text = preg_replace($find, $repl, $text); return $text; } // ---------- insert -------------- /** * 设置 `table.column` 与 last-insert-id 的映射 * * @param array $last_insert_id_names */ public function setLastInsertIdNames(array $last_insert_id_names) { $this->last_insert_id_names = $last_insert_id_names; } /** * insert into. * * @param string $table * @return self */ public function into($table) { $this->table = $this->quoteName($table); return $this; } /** * 生成 INSERT 语句 * * @return string */ protected function buildINSERT() { return 'INSERT' . $this->buildFlags() . $this->buildInto() . $this->buildValuesForInsert() . $this->buildReturning(); } /** * 生成 INTO 语句 * * @return string */ protected function buildInto() { return " INTO " . $this->table; } /** * PDO::lastInsertId() * * @param string $col * @return mixed */ public function getLastInsertIdName($col) { $key = str_replace('`', '', $this->table) . '.' . $col; if (isset($this->last_insert_id_names[$key])) { return $this->last_insert_id_names[$key]; } return null; } /** * 设置一列,如果有第二各参数,则把第二个参数绑定在占位符上 * * @param string $col * @return self */ public function col($col) { return call_user_func_array(array($this, 'addCol'), func_get_args()); } /** * 设置多列 * * @param array $cols * @return self */ public function cols(array $cols) { if ($this->type == 'SELECT') { foreach ($cols as $key => $val) { $this->addColSELECT($key, $val); } return $this; } return $this->addCols($cols); } /** * 直接设置列的值 * * @param string $col * @param string $value * @return self */ public function set($col, $value) { return $this->setCol($col, $value); } /** * 为 INSERT 语句绑定值 * * @return string */ protected function buildValuesForInsert() { return ' (' . $this->indentCsv(array_keys($this->col_values)) . ') VALUES (' . $this->indentCsv(array_values($this->col_values)) . ')'; } // ------update------- /** * 更新哪个表 * * @param string $table * @return self */ public function table($table) { $this->table = $this->quoteName($table); return $this; } /** * 生成完整 SQL 语句 * * @return string * @throws Exception */ protected function build() { switch ($this->type) { case 'DELETE': return $this->buildDELETE(); case 'INSERT': return $this->buildINSERT(); case 'UPDATE': return $this->buildUPDATE(); case 'SELECT': return $this->buildSELECT(); } throw new Exception("type empty"); } /** * 生成更新的 SQL 语句 */ protected function buildUPDATE() { return 'UPDATE' . $this->buildFlags() . $this->buildTable() . $this->buildValuesForUpdate() . $this->buildWhere() . $this->buildOrderBy() . $this->buildLimit() . $this->buildReturning(); } /** * 哪个表 * * @return string */ protected function buildTable() { return " {$this->table}"; } /** * 为更新语句绑定值 * * @return string */ protected function buildValuesForUpdate() { $values = array(); foreach ($this->col_values as $col => $value) { $values[] = "{$col} = {$value}"; } return ' SET' . $this->indentCsv($values); } // ----------Dml--------------- /** * 获取绑定的值 * * @return array */ public function getBindValuesCOMMON() { $bind_values = $this->bind_values; $i = 1; foreach ($this->bind_where as $val) { $bind_values[$i] = $val; $i++; } return $bind_values; } /** * 设置列 * * @param string $col * @return self */ protected function addCol($col) { $key = $this->quoteName($col); $this->col_values[$key] = ":$col"; $args = func_get_args(); if (count($args) > 1) { $this->bindValue($col, $args[1]); } return $this; } /** * 设置多个列 * * @param array $cols * @return self */ protected function addCols(array $cols) { foreach ($cols as $key => $val) { if (is_int($key)) { $this->addCol($val); } else { $this->addCol($key, $val); } } return $this; } /** * 设置单列的值 * * @param string $col . * @param string $value * @return self */ protected function setCol($col, $value) { if ($value === null) { $value = 'NULL'; } $key = $this->quoteName($col); $value = $this->quoteNamesIn($value); $this->col_values[$key] = $value; return $this; } /** * 增加返回的列 * * @param array $cols * @return self * */ protected function addReturning(array $cols) { foreach ($cols as $col) { $this->returning[] = $this->quoteNamesIn($col); } return $this; } /** * 生成 RETURNING 语句 * * @return string */ protected function buildReturning() { if (!$this->returning) { return ''; } return ' RETURNING' . $this->indentCsv($this->returning); } /** * 构造函数 * * @param string $host * @param int $port * @param string $user * @param string $password * @param string $db_name * @param string $charset */ public function __construct($host, $port, $user, $password, $db_name, $charset = 'utf8') { $this->settings = array( 'host' => $host, 'port' => $port, 'user' => $user, 'password' => $password, 'dbname' => $db_name, 'charset' => $charset, ); $this->connect(); } /** * 创建 PDO 实例 */ protected function connect() { $dsn = 'mysql:dbname=' . $this->settings["dbname"] . ';host=' . $this->settings["host"] . ';port=' . $this->settings['port']; $this->pdo = new PDO($dsn, $this->settings["user"], $this->settings["password"], array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . (!empty($this->settings['charset']) ? $this->settings['charset'] : 'utf8') )); $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $this->pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false); $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); } /** * 关闭连接 */ public function closeConnection() { $this->pdo = null; } /** * 执行 * * @param string $query * @param string $parameters * @throws PDOException */ protected function execute($query, $parameters = "") { try { if (is_null($this->pdo)) { $this->connect(); } $this->sQuery = @$this->pdo->prepare($query); $this->bindMore($parameters); if (!empty($this->parameters)) { foreach ($this->parameters as $param) { $this->sQuery->bindParam($param[0], $param[1]); } } $this->success = $this->sQuery->execute(); } catch (PDOException $e) { // 服务端断开时重连一次 if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) { $this->closeConnection(); $this->connect(); try { $this->sQuery = $this->pdo->prepare($query); $this->bindMore($parameters); if (!empty($this->parameters)) { foreach ($this->parameters as $param) { $this->sQuery->bindParam($param[0], $param[1]); } } $this->success = $this->sQuery->execute(); } catch (PDOException $ex) { $this->rollBackTrans(); throw $ex; } } else { $this->rollBackTrans(); $msg = $e->getMessage(); $err_msg = "SQL:".$this->lastSQL()." ".$msg; $exception = new \PDOException($err_msg, (int)$e->getCode()); throw $exception; } } $this->parameters = array(); } /** * 绑定 * * @param string $para * @param string $value */ public function bind($para, $value) { if (is_string($para)) { $this->parameters[sizeof($this->parameters)] = array(":" . $para, $value); } else { $this->parameters[sizeof($this->parameters)] = array($para, $value); } } /** * 绑定多个 * * @param array $parray */ public function bindMore($parray) { if (empty($this->parameters) && is_array($parray)) { $columns = array_keys($parray); foreach ($columns as $i => &$column) { $this->bind($column, $parray[$column]); } } } /** * 执行 SQL * * @param string $query * @param array $params * @param int $fetchmode * @return mixed */ public function query($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC) { $query = trim($query); if (empty($query)) { $query = $this->build(); if (!$params) { $params = $this->getBindValues(); } } $this->resetAll(); $this->lastSql = $query; $this->execute($query, $params); $rawStatement = explode(" ", $query); $statement = strtolower(trim($rawStatement[0])); if ($statement === 'select' || $statement === 'show') { return $this->sQuery->fetchAll($fetchmode); } elseif ($statement === 'update' || $statement === 'delete' || $statement === 'replace') { return $this->sQuery->rowCount(); } elseif ($statement === 'insert') { if ($this->sQuery->rowCount() > 0) { return $this->lastInsertId(); } } else { return null; } return null; } /** * 返回一列 * * @param string $query * @param array $params * @return array */ public function column($query = '', $params = null) { $query = trim($query); if (empty($query)) { $query = $this->build(); if (!$params) { $params = $this->getBindValues(); } } $this->resetAll(); $this->lastSql = $query; $this->execute($query, $params); $columns = $this->sQuery->fetchAll(PDO::FETCH_NUM); $column = null; foreach ($columns as $cells) { $column[] = $cells[0]; } return $column; } /** * 返回一行 * * @param string $query * @param array $params * @param int $fetchmode * @return array */ public function row($query = '', $params = null, $fetchmode = PDO::FETCH_ASSOC) { $query = trim($query); if (empty($query)) { $query = $this->build(); if (!$params) { $params = $this->getBindValues(); } } $this->resetAll(); $this->lastSql = $query; $this->execute($query, $params); return $this->sQuery->fetch($fetchmode); } /** * 返回单个值 * * @param string $query * @param array $params * @return string */ public function single($query = '', $params = null) { $query = trim($query); if (empty($query)) { $query = $this->build(); if (!$params) { $params = $this->getBindValues(); } } $this->resetAll(); $this->lastSql = $query; $this->execute($query, $params); return $this->sQuery->fetchColumn(); } /** * 返回 lastInsertId * * @return string */ public function lastInsertId() { return $this->pdo->lastInsertId(); } /** * 返回最后一条执行的 sql * * @return string */ public function lastSQL() { return $this->lastSql; } /** * 开始事务 */ public function beginTrans() { try { if (is_null($this->pdo)) { $this->connect(); } return $this->pdo->beginTransaction(); } catch (PDOException $e) { // 服务端断开时重连一次 if ($e->errorInfo[1] == 2006 || $e->errorInfo[1] == 2013) { $this->closeConnection(); $this->connect(); return $this->pdo->beginTransaction(); } else { throw $e; } } } /** * 提交事务 */ public function commitTrans() { return $this->pdo->commit(); } /** * 事务回滚 */ public function rollBackTrans() { if ($this->pdo->inTransaction()) { return $this->pdo->rollBack(); } return true; } }
链式操作的关键是在做完操作后要return $this。return $this表示方法结束后,返回的是当前对象,它可以实现链式操作。
“php链式操作mysql数据库的方法是什么”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。