php数据库类

曲文 · · 112 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

想自己写一个框架,便于理解php框架的整体运行体系,一步步来,这个是数据库类。confing在下面。

<?php

$config = include 'confing.php';
$m = new Model($config);
var_dump($m->table('user')->getByAge(18));
// $m->limit('0,5')->table('user')->field('age,name')->order('money desc')->where('id>1')->select();
//$data = $m->table('user')->field('id,age,name')->limit('0,1')->where('id>0')->order('age desc')->select();
/*$data = [
    'name'=>'成龙',
    'age'=>'25',
    'money'=>'9999'
];*/
// $insert = $m->table('user')->insert($data);
// $delete = $m->table('user')->where('id=4')->delete();
//$update = $m->table('user')->where('id=3')->update($data);
// $max = $m->table('user')->max('money');
// var_dump($m->sql);
// var_dump($max);

class Model
{
    //地址
    protected $host;
    //用户名
    protected $user;
    //密码
    protected $pwd;
    //库名
    protected $dbname;
    //字符集
    protected $charset;
    //表前缀
    protected $prefix;

    //链接资源
    protected $link;
    //表名
    protected $tableName;

    //sql语句
    protected $sql;
    //存放查询条件
    protected $options;


    //构造方法,成员变量初始化
    function __construct($config)
    {
        $this->host = $config['DB_HOST'];
        $this->user = $config['DB_USER'];
        $this->pwd = $config['DB_PWD'];
        $this->dbname = $config['DB_NAME'];
        $this->charset = $config['DB_CHARSET'];
        $this->prefix = $config['DB_PREFIX'];

        //连接数据库
        $this->link = $this->connect();

        //得到数据表名
        $this->tableName = $this->getTableName();

        //初始化options数组
        $this->initOptions();
    }

    protected function connect()
    {
        $link = mysqli_connect($this->host,$this->user,$this->pwd);
        
        if (!$link){
            
            die('数据库连接失败');
        }
        mysqli_select_db($link,$this->dbname);//选择数据库
        mysqli_set_charset($link,$this->charset);//设置字符集
        return $link;
    }

    protected function getTableName()
    {
        //1,已有成员变量
        if (!empty($this->tableName)) {
            return $this->prefix.$this->tableName;
        }
        //2,无成员变量
        $className = get_class($this);//获得类名字符串
        $table = strtolower(substr($className, 0, -5));//如ArticleModel截取表名截掉后五个字符并转为小写
        return $this->prefix.$table;
    }

    protected function initOptions()
    {
        $arr = ['where','table','field','order','group','having','limit'];
        foreach ($arr as $value) {
            //数组内键对应的值清空
            $this->options[$value] = '';
            //table默认设置为tableName
            if($value == 'table'){
                $this->options[$value] = $this->tableName;
            }elseif ($value == 'field') {
                $this->options[$value] = '*';
            }
        }
    }

    //field
    function field($field)
    {
        if (!empty($field)) {
            if (is_string($field)) {
                $this->options['field'] = $field;
            }elseif (is_array($field)) {
                $this->options['field'] = join(',',$field);
            }
        }
        return $this;
    }
    //table
    function table($table)
    {
        if (!empty($table)) {
            $this->options['table'] = $table;
        }
        return $this;
    }
    //where
    function where($where)
    {
        if (!empty($where)) {
            $this->options['where'] = 'where '.$where;
        }
        return $this;
    }
    //group
    function group($group)
    {
        if (!empty($group)) {
            $this->options['group'] = 'group by '.$group;
        }
        return $this;
    }
    //having
    function having($having)
    {
        if (!empty($having)) {
            $this->options['having'] = 'having '.$having;
        }
        return $this;
    }
    //order
    function order($order)
    {
        if (!empty($order)) {
            $this->options['order'] = 'order by '.$order;
        }
        return $this;
    }
    //limit
    function limit($limit)
    {
        if (!empty($limit)) {
            if (is_string($limit)) {
                $this->options['limit'] = 'limit '.$limit;
            }elseif (is_array($limit)) {
                $this->options['limit'] = 'limit '.join(',',$limit);
            }
        }
        return $this;
    }
    //select
    function select()
    {
        //预写一个带占位符的sql
        $sql = 'select %FIELD% FROM %TABLE% %WHERE% %GROUP% %HAVING% %ORDER% %LIMIT%';
        //将options中值一次替换占位符。

        $sql = str_replace(
            ['%FIELD%','%TABLE%','%WHERE%','%GROUP%','%HAVING%','%ORDER%','%LIMIT%'],
            [
            $this->options['field'],
            $this->options['table'],
            $this->options['where'],
            $this->options['group'],
            $this->options['having'],
            $this->options['order'],
            $this->options['limit']
            ],$sql);
        //保存sql语句
        $this->sql = $sql;
        //执行sql语句
        // echo $sql;die;
        return $this->query($sql);
    }
    //query
    function query($sql)
    {
        //清空options数组
        $this->initOptions();
        
        $result = mysqli_query($this->link,$sql);
        //提取结果集存放到数组中
        // var_dump($result);
        // die;
        if ($result && mysqli_affected_rows($this->link)) {
            while ($data = mysqli_fetch_assoc($result))
            {
                $newData[] = $data;
            }
        }
        //返回结果集
        return $newData;
    }
    
    //exec
    function exec($sql,$isInsert = false)
    {
        $this->initOptions();
        //执行sql
        $result = mysqli_query($this->link,$sql);
        if ($result && mysqli_affected_rows($this->link)) {
            //判断是否插入,返回对应结果
            if ($isInsert) {
                return mysqli_insert_id($this->link);
            }else{
                return mysqli_affected_rows($this->link);
            }
        }
        return false;
    }

    function __get($name)
    {
        if ($name = 'sql') {//获取sql语句
            return $this->sql;
        }
        return false;
    }

    //insert
    //$data数组,键为字段名
    function insert($data)
    {
        $data = $this->parseValue($data);

        //提取键值
        $keys = array_keys($data);
        $values = array_values($data);

        $sql = 'insert into %TABLE%(%FIELD%) VALUES(%VALUES%)';
        $sql = str_replace(
            ['%TABLE%','%FIELD%','%VALUES%'],
            [$this->options['table'], join(',',$keys),join(',',$values)],$sql);
        $this->sql = $sql;
        return $this->exec($sql,true);
    }

    //给字符串值添加引号
    protected function parseValue($data)
    {
        foreach ($data as $key => $value) {
            if (is_string($value)) {
                $value = "'".$value."'";
            }
            $newData[$key] = $value;
        }
        //返回处理后数组
        return $newData;
    }

    //删除
    function delete()
    {
        $sql = 'delete from %TABLE% %WHERE%';
        $sql = str_replace(
            ['%TABLE%','%WHERE%'],
            [$this->options['table'], $this->options['where']],$sql);
        $this->sql = $sql;
        return $this->exec($sql);
    }

    //更新
    function update($data)
    {
        $data = $this->parseValue($data);

        $value = $this->parseUpdate($data);

        $sql = 'update %TABLE% set %VALUE% %WHERE%';
        $sql = str_replace(
            ['%TABLE%','%VALUE%','%WHERE%'],
            [$this->options['table'], $value,$this->options['where']],$sql);
        $this->sql = $sql;
        return $this->exec($sql);
    }
    //拼接语句
    protected function parseUpdate($data)
    {
        foreach ($data as $key => $value) {
            $newData[] = $key.'='.$value;
        }
        return join(',',$newData);
    }

    //聚合
    function max($field)
    {
        //调用封装方法查询

        $result = $this->field('max('.$field.') as max')->select();
        //因为查询的是二维数组,所以给出一个下标
        return $result[0]['max'];
    }

    //析构方法,对象销毁时被调用
    function __destruct()
    {
        mysqli_close($this->link);
    }

    //getByName  getByAge//魔术方法,调用不存在方法时激活
    function __call($name,$args)
    {
        //截取方法名
        $str = substr($name,0,5);
        //截取字段名
        $field = substr($name,5);
        if ($str == 'getBy') {//判断方法名是否正确
            return $this->where($field.'="'.$args[0].'"')->select();
        }
        return false;
    }
}


?>

 

<?php 

return [
	'DB_HOST' => 'localhost',
	'DB_USER' => 'root',
	'DB_PWD' => '',
	'DB_NAME' => 'tt',
	'DB_CHARSET' => 'utf8',
	'DB_PREFIX' => '',
];

?>

 

本文来自:开源中国博客

感谢作者:曲文

查看原文:php数据库类

112 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet