TypechoJoeTheme

至尊技术网

统计
登录
用户名
密码
/
注册
用户名
邮箱

PHP面向对象封装MySQL PDO(已使用预处理)

2022-06-10
/
0 评论
/
265 阅读
/
正在检测是否收录...
06/10

Mysql.class.php

<?php
class Mysql
{
    public $link;

    public function __construct()
    {
        $this->conn();
    }

    /**
     * 连接数据库,从配置文件读取配置信息
     */
    public function conn()
    {
        $cfg = require 'config.php';
        try {
            $this->link = new PDO("mysql:dbname={$cfg['databaseName']};host={$cfg['host']};charset={$cfg['charset']};port={$cfg['port']}", $cfg['name'], $cfg['password']);
            $this->link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // 设置禁止本地模拟prepare
            //$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// 设置捕获异常
        } catch (PDOException $e) {
            die("Error!: " . $e->getMessage() . "<br/>");
        }
    }

    /**
     * 查询多行数据
     * @param string $table 表名字
     * @param string $where where条件
     * @param string $field 字段
     * @param string $additional 附加sql语句
     * @return array
     */
    public function getAll($table, $where = [], $field = "*", $additional = '')
    {
        if (strpos($field, ",") !== false) {
            $arr = explode(",", $field);
            $str = '';
            foreach ($arr as $v) {
                $str .= "`{$v}`,";
            }
            $field = substr($str, 0, -1);
        } else if ($field != "*") {
            $field = "`{$field}`";
        }
        $sql = "select {$field} from `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if ($where[1] == 'in') {
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if ($v[1] == 'in') {
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') and';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? and";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " where " . $sql2;
            }
        }
        if ($additional) {
            $sql .= ' ' . $additional;
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetchAll(PDO::FETCH_ASSOC);
        return $data;
    }

    /**
     * 查询单行数据
     * @param string $table 表名字
     * @param string $where where条件
     * @param string $field 字段
     * @param string $additional 附加sql语句
     * @return array
     */
    public function getRow($table, $where = [], $field = "*", $additional = '')
    {
        if (strpos($field, ",") !== false) {
            $arr = explode(",", $field);
            $str = '';
            foreach ($arr as $v) {
                $str .= "`{$v}`,";
            }
            $field = substr($str, 0, -1);
        } else if ($field != "*") {
            $field = "`{$field}`";
        }
        $sql = "select {$field} from `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if ($where[1] == 'in') {
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if ($v[1] == 'in') {
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') and';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? and";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " where " . $sql2;
            }
        }
        if ($additional) {
            $sql .= ' ' . $additional;
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetch(PDO::FETCH_ASSOC);
        return $data;
    }

    /**
     * 自动创建sql语句并执行
     * @param string $table 表名字
     * @param array $data 关联数组 键/值与表的列/值对应
     * @param string $act 1为insert,2为update
     * @param array $where 条件,用于update
     * @return int 成功为insert产生的主键值,update是影响的行数,失败为0
     */
    public function exec($table, $data, $act = 1, $where = [])
    {
        $value = [];
        if ($act == 1) {
            $sql = "insert into `{$table}` (`";
            $sql .= implode('`,`', array_keys($data)) . '`)';
            $str = '';
            foreach ($data as $v) {
                $str .= "?,";
            }
            $str = substr($str, 0, -1);
            $sql .= " values ({$str})";
            $value = array_values($data);
        } else {
            $sql = "update `{$table}` set ";
            foreach ($data as $k => $v) {
                $sql .= "`" . $k . '`= ' . " ?,";
                $value[] = $v;
            }
            $sql = rtrim($sql, ',');
            $sql2 = '';
            if ($where) {
                if (!is_array($where[0])) {
                    if ($where[1] == 'in') {
                        $sql2 = " `{$where[0]}` {$where[1]} (";
                        if (is_array($where[2])) {
                            foreach ($where[2] as $v) {
                                $value[] = $v;
                                $sql2 .= '?,';
                            }
                        } else {
                            $value[] = $where[2];
                            $sql2 .= '?';
                        }
                        $sql2 = rtrim($sql2, ',') . ')';
                    } else {
                        $value[] = $where[2];
                        $sql2 = " `{$where[0]}` {$where[1]} ?";
                    }
                } else {
                    foreach ($where as $v) {
                        if ($v[1] == 'in') {
                            $sql2 .= " `{$v[0]}` {$v[1]} (";
                            if (is_array($v[2])) {
                                foreach ($v[2] as $v2) {
                                    $value[] = $v2;
                                    $sql2 .= "?,";
                                }
                            } else {
                                $value[] = $v[2];
                                $sql2 .= "?";
                            }
                            $sql2 = rtrim($sql2, ',') . ') and';
                        } else {
                            $value[] = $v[2];
                            $sql2 .= " `{$v[0]}` {$v[1]} ? and";
                        }
                    }
                    $sql2 = substr($sql2, 0, -4);
                }
                if ($sql2) {
                    $sql .= " where " . $sql2;
                }
            }
        }
        $res = $this->link->prepare($sql);
        $run = $res->execute($value);
        if ($run) {
            if ($act == 1) {
                return $this->link->lastInsertId();
            } else {
                return $res->rowCount();
            }
        } else {
            return 0;
        }
    }

    /**
     * 删除数据
     * @param string $table 表名字
     * @param array $where where条件
     * @return bool
     */
    public function delete($table, $where = [])
    {
        $sql = "delete from `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if ($where[1] == 'in') {
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if ($v[1] == 'in') {
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') and';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? and";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " where " . $sql2;
            }
        }
        $res = $this->link->prepare($sql);
        return $res->execute($value);
    }

    /**
     * count数据
     * @param string $table 表名字
     * @param array $where where条件
     * @return int
     */
    public function count($table, $where = [])
    {
        $sql = "select count(*) from `{$table}`";
        $sql2 = '';
        $value = [];
        if ($where) {
            if (!is_array($where[0])) {
                if ($where[1] == 'in') {
                    $sql2 = " `{$where[0]}` {$where[1]} (";
                    if (is_array($where[2])) {
                        foreach ($where[2] as $v) {
                            $value[] = $v;
                            $sql2 .= '?,';
                        }
                    } else {
                        $value[] = $where[2];
                        $sql2 .= '?';
                    }
                    $sql2 = rtrim($sql2, ',') . ')';
                } else {
                    $value[] = $where[2];
                    $sql2 = " `{$where[0]}` {$where[1]} ?";
                }
            } else {
                foreach ($where as $v) {
                    if ($v[1] == 'in') {
                        $sql2 .= " `{$v[0]}` {$v[1]} (";
                        if (is_array($v[2])) {
                            foreach ($v[2] as $v2) {
                                $value[] = $v2;
                                $sql2 .= "?,";
                            }
                        } else {
                            $value[] = $v[2];
                            $sql2 .= "?";
                        }
                        $sql2 = rtrim($sql2, ',') . ') and';
                    } else {
                        $value[] = $v[2];
                        $sql2 .= " `{$v[0]}` {$v[1]} ? and";
                    }
                }
                $sql2 = substr($sql2, 0, -4);
            }
            if ($sql2) {
                $sql .= " where " . $sql2;
            }
        }
        $res = $this->link->prepare($sql);
        $res->execute($value);
        $data = $res->fetch(PDO::FETCH_NUM);
        return $data[0];
    }
}

config.php

<?php
return array(
    'host' => 'localhost',
    'name' => 'root',
    'password' => 'root',
    'databaseName' => 'cs_cn',
    'port' => '3306',
    'charset' => 'utf8'
);

使用方法

<?php
require 'Mysql.class.php';

$mysql = new Mysql();

// 添加
$data = [
    'code' => mt_rand(1000000000,9999999999),
    'url' => 'https://www.zzwws.cn',
    'ip' => mt_rand(1000000000,9999999999),
    'add_time' => time()
];
$res = $mysql->exec('zz_url',$data);
if($res){
    echo '添加成功';
}else{
    echo '添加失败';
}

// 修改
$data = [
    'code' => mt_rand(1000000000,9999999999)
];
$where = ['id','=',51];
// 或者
// $where = [
//     ['id','=',51],
//     ['ip','=','3755406202']
// ];

$res = $mysql->exec('zz_url',$data,2,$where);
if($res){
    echo '修改成功';
}else{
    echo '修改失败';
}

// 查询一行数据
$row = $mysql->getRow('zz_url',['id','=',51],'id,code');
if(!$row){
    echo '获取失败';
}
print_r($row);

// 查询多行数据
$rows = $mysql->getAll('zz_url');
print_r($rows);

// 删除
$where = [
    ['id','=',52]
];
// 或者
// $where = [
//     ['id','in',[1,2,3]]
// ];
$res = $mysql->delete('zz_url',$where);
var_dump($res);

// count
$count = $mysql->count('zz_url',['url','=','https://www.zzwws.cn']);
var_dump($count);

// query方法(没有预处理)
$res = $mysql->link->query("select * from zz_url where id = '1'");
$row = $res->fetch(PDO::FETCH_ASSOC);
print_r($row);
经验PHPMySQL面向对象PDO预处理
朗读
赞(0)
版权属于:

至尊技术网

本文链接:

https://www.zzwws.cn/archives/6343/(转载时请注明本文出处及文章链接)

评论 (0)