重构PHP通用查询系统之5----PDO操作MYSQL数据库的常用方法
2020-01-26 admin php mysql 1491
作为PHP最佳搭档MYSQL，必须熟悉其基本的使用方法。mysqli方式大家都很熟悉了，而最新的PDO方式支持多种数据库，因接触少较不熟悉。这章我们来熟悉一下基本的操作：
首先是在config.php中放置数据库相关信息：
// 数据库配置 $config['db']['host'] = 'localhost'; $config['db']['username'] = 'pdo_dzbfsj_com'; $config['db']['password'] = 'pWRYwhMmhFjJwGPd'; $config['db']['dbname'] = 'pdo_dzbfsj_com'; // 默认控制器和操作名 $config['defaultController'] = 'Home'; $config['defaultAction'] = 'index'; return $config;
之后，在框架主程序Fastphp.php中初始化常量：
// 配置数据库信息
public function setDbConfig()
{
if ($this->config['db']) {
define('DB_HOST', $this->config['db']['host']);
define('DB_NAME', $this->config['db']['dbname']);
define('DB_USER', $this->config['db']['username']);
define('DB_PASS', $this->config['db']['password']);
}
}然后在DB类中创建对象：
namespace fastphp\db;
use PDO;
use PDOException;
/**
* 数据库操作类。
* 其$pdo属性为静态属性，所以在页面执行周期内，
* 只要一次赋值，以后的获取还是首次赋值的内容。
* 这里就是PDO对象，这样可以确保运行期间只有一个
* 数据库连接对象，这是一种简单的单例模式
* Class Db
*/
class Db
{
private static $pdo = null;
public static function pdo()
{
if (self::$pdo !== null) {
return self::$pdo;
}
try {
$dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8', DB_HOST, DB_NAME);
$option = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
return self::$pdo = new PDO($dsn, DB_USER, DB_PASS, $option);
} catch (PDOException $e) {
exit($e->getMessage());
}
}
}现在可以在Sql.php中写上常用的读写查删数据库的方法了：
namespace fastphp\db;
use \PDOStatement;
class Sql
{
// 数据库表名，pdo不支持绑定中文字段，因此需要判断是中文就用Md5
protected $table;
// 数据库主键
protected $primary = 'id';
// WHERE和ORDER拼装后的条件
private $filter = '';
// Pdo bindParam()绑定的参数集合
private $param = array();
/**
* 查询条件拼接，使用方式：
*
* $this->where(['id = 1','and title="Web"', ...])->fetch();
* 为防止注入，建议通过$param方式传入参数：
* $this->where(['id = :id'], [':id' => $id])->fetch();
* $this->where(['id = ?'], [$id])->fetch();
* @param array $where 条件
* @return $this 当前对象
*/
public function where($where = array(), $param = array())
{
if ($where) {
$this->filter = $this->filter.' WHERE ';
$this->filter = $this->filter.implode(' ', $where);//把数组元素组合为一个字符串id=?,xmmc=?
$this->param = $param;
}
return $this;
}
//直接传入关联数组查询
public function wherearray($data = array())
{
if ($data) {
$i = 0;
foreach($data as $key=>$val){
//转换为pdo查询语句
if($i == 0){
$fil[$i] = $key.' = ?';
}else{
$fil[$i] = 'and '.$key.' = ?';
}
$pal[$i] = $val;
$i++;
}
$this->filter = $this->filter.' WHERE ';
$this->filter = $this->filter.implode(' ', $fil);//把数组元素组合为一个字符串id=?,xmmc=?
$this->param = $pal;
}
return $this;
}
/**
* 拼装排序条件，使用方式：
*
* $this->order(['id DESC', 'title ASC', ...])->fetch();
*
* @param array $order 排序条件
* @return $this
*/
public function order($order = array())
{
if($order) {
$this->filter .= ' ORDER BY ';
$this->filter .= implode(',', $order);
}
return $this;
}
// 查询所有
public function fetchAll()
{
$sql = sprintf("select * from `%s` %s", $this->table, $this->filter);
$sth = Db::pdo()->prepare($sql);
$sth = $this->formatParam($sth, $this->param);
$sth->execute();
return $sth->fetchAll();
}
// 查询一条
public function fetch()
{
$sql = sprintf("select * from `%s` %s", $this->table, $this->filter);
$sth = Db::pdo()->prepare($sql);
$sth = $this->formatParam($sth, $this->param);
$sth->execute();
return $sth->fetch();
}
// 根据条件 (id) 删除
public function delete($id)
{
$sql = sprintf("delete from `%s` where `%s` = :%s", $this->table, $this->primary, $this->primary);
$sth = Db::pdo()->prepare($sql);
$sth = $this->formatParam($sth, [$this->primary => $id]);
$sth->execute();
return $sth->rowCount();
}
// 新增一条数据，不支持中文字段
public function add($data)
{
$sql = sprintf("insert into `%s` %s", $this->table, $this->formatInsert($data));
$sth = Db::pdo()->prepare($sql);
$sth = $this->formatParam($sth, $data);
$sth = $this->formatParam($sth, $this->param);
//var_dump($sth);exit;
$sth->execute();
return $sth->rowCount();
}
// 修改数据，不支持中文字段
public function update($data)
{
$sql = sprintf("update `%s` set %s %s", $this->table, $this->formatUpdate($data), $this->filter);
$sth = Db::pdo()->prepare($sql);
$sth = $this->formatParam($sth, $data);
$sth = $this->formatParam($sth, $this->param);
//var_dump($sth);exit;
$sth->execute();
return $sth->rowCount();
}
/**
* 占位符绑定具体的变量值
* @param PDOStatement $sth 要绑定的PDOStatement对象
* @param array $params 参数，有三种类型：
* 1）如果SQL语句用问号?占位符，那么$params应该为
* [$a, $b, $c]
* 2）如果SQL语句用冒号:占位符，那么$params应该为
* ['a' => $a, 'b' => $b, 'c' => $c]
* 或者
* [':a' => $a, ':b' => $b, ':c' => $c]
*
* @return PDOStatement
*/
public function formatParam(PDOStatement $sth, $params = array())
{
foreach ($params as $param => &$value) {
$param = is_int($param) ? $param + 1 : ':' . ltrim($param, ':');
$sth->bindParam($param, $value);
}
return $sth;
}
// 将数组转换成插入格式的sql语句
private function formatInsert($data)
{
$fields = array();
$names = array();
foreach ($data as $key => $value) {
$fields[] = sprintf("`%s`", $key);//sprintf格式化字符，两边加上`
$names[] = sprintf(":%s", $key);//sprintf格式化字符，前面加上:
}
$field = implode(',', $fields);
$name = implode(',', $names);
//(`id`,`姓名`,`身份证号`,`基本工资`,`奖励工资`,`其它`,`总计`) values
//(:id,:姓名,:身份证号,:基本工资,:奖励工资,:其它,:总计)
return sprintf("(%s) values (%s)", $field, $name);
}
// 将数组转换成更新格式的sql语句
private function formatUpdate($data)
{
$fields = array();
foreach ($data as $key => $value) {
//pdo不支持绑定中文字段
$key = str_replace('.', '_', $key);
if (preg_match("/[\x7f-\xff]/", $key)) {//add_判断字符串中是否有中文
$key2 = 'd'.time().'t';//随机命名
$fields[] = sprintf("`%s` = :%s", $key, $key2);
}else{
$fields[] = sprintf("`%s` = :%s", $key, $key);
}
}
return implode(',', $fields);
}
}这种绑定参数的方式操作数据库，是不支持中文字段的，因此，在Model中我们经常要写上自定义的方法，像删除插入一类的，可以使用pdo->exec方式，只返回成功执行受影响的行数，不返回数据。pdo支持批量插入数据，在此重构中，用到的自定义方法有：
namespace app\models;
use fastphp\base\Model;
use fastphp\db\Db;
/**
* 创建临时表字段
*/
class Lssjb extends Model
{
//创建bgsx初始表，表名为xmmc的md5值，字段有id,bm,xmmc,cxtj,kfcxzd,kftbzd,beizhu,cjrq的数据表
public function create_table($bm, $zdsz){
$sql = 'id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,';
//将字段数组转成sql字段语句
$num = count($zdsz) - 1;
$i = 0;
foreach($zdsz as $zd){
if($i == $num){
//最后一个，不加,
$sql = $sql."`$zd` VARCHAR(255) NULL";
}else{
$sql = $sql."`$zd` VARCHAR(255) NULL,";
}
$i++;
}
$sql = "CREATE TABLE $bm ($sql)";
//使用 exec(),$fh = int(0) 时执行成功
$fh = Db::pdo()->exec($sql);
return $fh;
}
//批量插入
public function insert($table,$arrData) {
$name = $values = '';
$flag = $flagV = 1;
$true = is_array( current($arrData) );//判断是否一次插入多条数据
if($true) {
//构建插入多条数据的sql语句
foreach($arrData as $arr) {
$values .= $flag ? '(' : ',(';
foreach($arr as $key => $value) {
if($flagV) {
if($flag) $name .= "$key";
$values .= "'$value'";
$flagV = 0;
} else {
if($flag) $name .= ",$key";
$values .= ",'$value'";
}
}
$values .= ') ';
$flag = 0;
$flagV = 1;
}
} else {
//构建插入单条数据的sql语句
foreach($arrData as $key => $value) {
if($flagV) {
$name = "$key";
$values = "('$value'";
$flagV = 0;
} else {
$name .= ",$key";
$values .= ",'$value'";
}
}
$values .= ") ";
}
$strSql = "insert into $table ($name) values $values";
$fh = Db::pdo()->exec($strSql);
if($fh > 0) {
return true;
}
return false;
}
//获取$table表的所有字段
public function hqsyzd($table){
$sql="SHOW COLUMNS FROM `$table`";
$sth = Db::pdo()->prepare($sql);
$sth->execute();
while($result=$sth->fetch()){
$zdsz[] = $result['Field'];
}
return $zdsz;
}
//删除数据表
public function droptable($table){
$sql = "DROP TABLE $table";
$fh = Db::pdo()->exec($sql);
return $fh;
}
}还有个通用的Model，数据表名和字段都不确定时使用：
namespace app\models;
use fastphp\base\Model;
use fastphp\db\Db;
/**
* 通用Model
*/
class Table extends Model
{
//构造函数属性声明是由关键字 public，protected 或者 private 开头，然后跟一个普通的变量声明来组成。
protected $table;//创建属性
public function __construct($bm)//接收控制器变量bm,在函数内部通过func_get_args()函数就可以取得所有传入的参数
{
$this->table = $bm;//赋值给table
}
// 修改数据，exec执行支持中文字段
public function updateArray($where,$data)
{
$sh = Db::pdo();
foreach($data as $k=>$v){
$zd = "`$k`";
$nr = $sh->quote($v);//过滤参数,自动加引号
}
$zdnr = "$zd = $nr";
$sql = "UPDATE `$this->table` SET $zdnr WHERE $where";
$stmt = $sh->exec($sql);
return $stmt;
}
}因对PDO很陌生，只记录用过的方法，待深入了解后再修改。