PHP操作mysql的实例

 //连接查询

$conn=null;
    function query($sql){
        //empty 判断一个变量的值是否为空 
        global $conn;
        $conn=mysqli_connect('localhost','root','');//连接数据库服务器
        mysqli_select_db($conn,'1605db');//选中要操作的数据库
        mysqli_query($conn,'set names utf8');//执行一个SQL语句
        $result=mysqli_query($conn,$sql);//设置指定编码格式
        return $result;    
    }
    
    /*
    *执行SQL查询数据
    *@param[参数] $sql  执行查询的SQL语句
    *@param[参数] $isnum 是否返回数字键的数组  如果为true返回数字键数组//否则返回false
    *@return[返回] array 查询的结果数组 如果返回false查询失败
    */
    function select($sql,$isnum=false){
        $result=query($sql);
        if($result){
            $resultArray=array();
            if($isnum){
                while($arr=mysqli_fetch_row($result)){
                    array_push($resultArray,$arr);
                }
            }else{
                while($arr=mysqli_fetch_assoc($result)){
                    array_push($resultArray,$arr);
                }
            }
            return $resultArray;            
        }else{
            return false;
        }
    }
    
    //验证
    $arr=select('select * from admin');
    print_r($arr);

　　

-----------------------------------------------------------------------------------------------------------------------------------------------

 

//连接插入

$conn=null;
    function query($sql){
        //empty 判断一个变量的值是否为空 
        global $conn;
        $conn=mysqli_connect('localhost','root','');//连接数据库服务器
        mysqli_select_db($conn,'1605db');//选中要操作的数据库
        //mysqli_query($conn,'set names utf8');//执行一个SQL语句
        $result=mysqli_query($conn,$sql);//设置指定编码格式
        return $result;    
    }
    
    /*
    *执行SQL插入数据
    *@param[参数] $sql  执行插入的SQL语句
    *@param[参数] return boolean||int  如果执行失败 返回false，如果插入成功 返回插入的ID
    */
    function insert($sql){
        global $conn;//声明为全局
        $result=query($sql);
        if($result){
            return mysqli_insert_id($conn); //如果插入成功，返回ID
        }
        else{
            return false;
        }
    }
    
    //验证
    //一定要注意MySQL的单引号和双引号的区别，牢记，妹的坑了我一天时间不出结果……
    $sql='insert into admin (`id`,`username`,`password`,`gender`,`age`) values ("","xkc","xkc",1,15)';
    $arr=insert($sql); //这里实际上是我在写的insert函数返回的一个判断，（如果执行成功则返回插入成功行的ID ，否则返回false）
    print_r($arr);

　　

-----------------------------------------------------------------------------------------------------------------------------------------------------

 

//连接删除

 

  

  $conn=null;
    function query($sql){
        //empty 判断一个变量的值是否为空 
        global $conn;
        $conn=mysqli_connect('localhost','root','');//连接数据库服务器
        mysqli_select_db($conn,'1605db');//选中要操作的数据库
        //mysqli_query($conn,'set names utf8');//执行一个SQL语句
        $result=mysqli_query($conn,$sql);//设置指定编码格式
        return $result;    
    }
    
    /*
    *执行SQL删除数据
    *@param[参数] $sql  执行删除的SQL语句
    *@param[参数] return boolean||int  如果执行失败 返回false，如果删除成功 返回删除了几条数据（即受影响的行数）
    */
    function del($sql){
        global $conn;//声明为全局
        $result=query($sql);
        if($result){
            return mysqli_affected_rows($conn); //如果执行成功，返回改变了多少行（返回受影响的行数）
        }
        else{
            return false;
        }
    }
    
    $sql='delete from admin where id>10';
    $arr=del($sql);//del返回受影响的行数
    print_r($arr);

　　

------------------------------------------------------------------------------------------------------------------------------------------

 

连接更改

 

$conn=null;
    function query($sql){
        //empty 判断一个变量的值是否为空 
        global $conn;
        $conn=mysqli_connect('localhost','root','');//连接数据库服务器
        mysqli_select_db($conn,'1605db');//选中要操作的数据库
        //mysqli_query($conn,'set names utf8');//执行一个SQL语句
        $result=mysqli_query($conn,$sql);//设置指定编码格式
        return $result;    
    }
    
    /*
    *执行SQL修改数据
    *@param[参数] $sql  执行修改的SQL语句
    *@param[参数] return boolean||int  如果执行失败 返回false，如果修改成功 返回修改了几条数据（即受影响的行数）
    */
    function update($sql){
        global $conn;//声明为全局
        $result=query($sql);
        if($result){
            return mysqli_affected_rows($conn); //如果执行成功，返回受影响的行数
        }
        else{
            return false;
        }
    }
    
    $sql='update admin set password ="123456" where id>5';
    $arr=update($sql);//del返回受影响的行数
    print_r($arr);

　　

-------------------------------------------------------------------------------------------------------------------------------------------------

 

下面只返回一维数组的查询结果：

//查询

/*
*执行SQL查询数据
*@param[参数] $sql 执行查询的SQL语句
*@param[参数] $isnum 是否返回数字键的数组 如果为true返回数字键数组//否则返回false
*@return[返回] array 查询的结果数组 如果返回false查询失败
*/

function getOne($sql,$isnum=false){
　　$result=query($sql);
　　if($result){
　　　　$resultArray=array();
　　　　if($isnum){
　　　　　　$resultArray=mysqli_fetch_row($result);
　　　　}else{
　　　　　　$resultArray=mysqli_fetch_assoc($result);
　　　　}
　　　　return $resultArray; 
　　}else{
　　　　return false;
　　}
}

　　

基于PHP封装的操作mysql的类

  1 <?php
  2 /**
  3  * 数据库封装类
  4  * 基于PDO
  5  * @author Rianley
  6  */
  7 namespace koboshi\tool;
  8 class Database
  9 {
 10     /**
 11      * @var string
 12      */
 13     private $host;
 14     /**
 15      * @var string
 16      */
 17     private $user;
 18     /**
 19      * @var string
 20      */
 21     private $password;
 22     /**
 23      * @var int
 24      */
 25     private $port;
 26     /**
 27      * @var string
 28      */
 29     private $dbName;
 30     /**
 31      * @var string
 32      */
 33     private $charset;
 34     /**
 35      * @var string
 36      */
 37     private $lastSql;
 38     /**
 39      * @var int
 40      */
 41     private $affectedRows = 0;
 42     /**
 43      * @var \PDO
 44      */
 45     private $pdoHandle;
 46     /**
 47      * Database constructor.
 48      * @param string $host
 49      * @param string $user
 50      * @param string $password
 51      * @param int $port
 52      * @param string $dbName
 53      * @param string $charset
 54      */
 55     public function __construct($host, $user, $password, $port = 3306, $dbName = '', $charset = 'utf8')
 56     {
 57         $this->host = $host;
 58         $this->user = $user;
 59         $this->password = $password;
 60         $this->port = $port;
 61         $this->dbName = $dbName;
 62         $this->charset = $charset;
 63     }
 64     public function __destruct()
 65     {
 66         $this->pdoHandle = null;
 67     }
 68     private function connect($force = false)
 69     {
 70         if (is_null($this->pdoHandle) || $force) {
 71             $dsn = "mysql:host={$this->host};port={$this->port};dbname={$this->dbName};charset={$this->charset}";
 72             $handle = new \PDO($dsn, $this->user, $this->password, array(
 73                 \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
 74                 \PDO::MYSQL_ATTR_COMPRESS => false
 75             ));
 76             $handle->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
 77             $this->pdoHandle = $handle;
 78         }
 79     }
 80     /**
 81      * @param string $dbName
 82      */
 83     public function selectDatabase($dbName)
 84     {
 85         $this->exec("USE {$dbName};");
 86         $this->dbName = $dbName;
 87     }
 88     public function lastSql()
 89     {
 90         return $this->lastSql;
 91     }
 92     public function queryOne($sql, $params = array())
 93     {
 94         $statement = $this->_query($sql, $params);
 95         $output = $statement->fetch(\PDO::FETCH_ASSOC);
 96         return empty($output) ? array() : $output;
 97     }
 98     public function query($sql, $params = array())
 99     {
100         $statement = $this->_query($sql, $params);
101         $output = $statement->fetchAll(\PDO::FETCH_ASSOC);
102         return empty($output) ? array() : $output;
103     }
104     private function _query($sql, $params)
105     {
106         $this->connect();
107         $statement = $this->pdoHandle->prepare($sql);
108         foreach ($params as $k => $v) {
109             $this->bindParam($k, $v, $statement);
110         }
111         $flag = $statement->execute();
112         if ($flag === false) {
113             $this->handleError($statement);
114         }
115         $this->affectedRows = $statement->rowCount();
116         $this->lastSql = $statement->queryString;
117         return $statement;
118     }
119     private function _insert(array $data, $tbl, $db, $type)
120     {
121         $type = strtoupper($type);
122         $output = array();
123         foreach ($data as $k => $v) {
124             $output[] = "`{$k}` = :{$k}";
125         }
126         if (empty($db)) {
127             $tblStr = "`{$tbl}`";
128         } else {
129             $tblStr = "`{$db}`.`{$tbl}";
130         }
131         $setStr = implode(', ', $output);
132         $sql = "{$type} INTO {$tblStr} SET {$setStr};";
133         return $sql;
134     }
135     public function delete($whereStr, $tbl, $db = null)
136     {
137         $this->connect();
138         if (empty($db)) {
139             $tblStr = "`{$tbl}`";
140         } else {
141             $tblStr = "`{$db}`.`{$tbl}";
142         }
143         $sql = "DELETE FROM {$tblStr} WHERE {$whereStr}";
144         $statement = $this->pdoHandle->prepare($sql);
145         $flag = $statement->execute();
146         if ($flag === false) {
147             $this->handleError($statement);
148         }
149         $this->affectedRows = $statement->rowCount();
150         $this->lastSql = $statement->queryString;
151         return $this->affectedRows();
152     }
153     public function update(array $data, $whereStr, $tbl, $db = null)
154     {
155         $this->connect();
156         $output = array();
157         foreach ($data as $k => $v) {
158             $output[] = "`{$k}` = :{$k}";
159         }
160         if (empty($db)) {
161             $tblStr = "`{$tbl}`";
162         } else {
163             $tblStr = "`{$db}`.`{$tbl}";
164         }
165         $setStr = implode(', ', $output);
166         $sql = "UPDATE {$tblStr} SET {$setStr} WHERE {$whereStr};";
167         $statement = $this->pdoHandle->prepare($sql);
168         foreach ($data as $k => $v) {
169             $this->bindParam(':' . $k, $v, $statement);
170         }
171         $flag = $statement->execute();
172         if ($flag === false) {
173             $this->handleError($statement);
174         }
175         $this->affectedRows = $statement->rowCount();
176         $this->lastSql = $statement->queryString;
177         return $this->affectedRows();
178     }
179     public function insert(array $data, $tbl, $db = null)
180     {
181         $this->connect();
182         $sql = $this->_insert($data, $tbl, $db, 'INSERT');
183         $statement = $this->pdoHandle->prepare($sql);
184         foreach ($data as $k => $v) {
185             $this->bindParam(':' . $k, $v, $statement);
186         }
187         $flag = $statement->execute();
188         if ($flag === false) {
189             $this->handleError($statement);
190         }
191         $this->affectedRows = $statement->rowCount();
192         $this->lastSql = $statement->queryString;
193         return $this->lastInsertId();
194     }
195     public function replace(array $data, $tbl, $db = null)
196     {
197         $this->connect();
198         $sql = $this->_insert($data, $tbl, $db, 'REPLACE');
199         $statement = $this->pdoHandle->prepare($sql);
200         foreach ($data as $k => $v) {
201             $this->bindParam(':' . $k, $v, $statement);
202         }
203         $flag = $statement->execute();
204         if ($flag === false) {
205             $this->handleError($statement);
206         }
207         $this->affectedRows = $statement->rowCount();
208         $this->lastSql = $statement->queryString;
209         return $this->lastInsertId();
210     }
211     public function ignore(array $data, $tbl, $db = null)
212     {
213         $this->connect();
214         $sql = $this->_insert($data, $tbl, $db, 'INSERT IGNORE');
215         $statement = $this->pdoHandle->prepare($sql);
216         foreach ($data as $k => $v) {
217             $this->bindParam(':' . $k, $v, $statement);
218         }
219         $flag = $statement->execute();
220         if ($flag === false) {
221             $this->handleError($statement);
222         }
223         $this->affectedRows = $statement->rowCount();
224         $this->lastSql = $statement->queryString;
225         return $this->lastInsertId();
226     }
227     public function exec($sql)
228     {
229         $this->connect();
230         $flag = $this->pdoHandle->exec($sql);
231         if ($flag === false) {
232             $this->handleError($this->pdoHandle);
233         }
234         $this->affectedRows = $flag;
235         $this->lastSql = $sql;
236         return $this->affectedRows();
237     }
238     public function affectedRows()
239     {
240         return $this->affectedRows;
241     }
242     public function lastInsertId()
243     {
244         $this->connect();
245         return $this->pdoHandle->lastInsertId();
246     }
247     public function begin()
248     {
249         $this->connect();
250         if ($this->pdoHandle->inTransaction()) {
251             throw new \PDOException('in transaction already!');
252         } else {
253             $this->pdoHandle->beginTransaction();
254         }
255     }
256     public function commit()
257     {
258         $this->connect();
259         $this->pdoHandle->commit();
260     }
261     public function rollback()
262     {
263         $this->connect();
264         $this->pdoHandle->rollBack();
265     }
266     public function escape($str)
267     {
268         $this->connect();
269         return $this->pdoHandle->quote($str);
270     }
271     /**
272      * @param \PDO|\PDOStatement $obj
273      */
274     private function handleError($obj)
275     {
276         $tmp = $obj->errorInfo();
277         $errCode = intval($tmp[1]);
278         $errMsg = strval($tmp[2]);
279         throw new \PDOException($errMsg, $errCode);
280     }
281     /**
282      * @param string $key
283      * @param mixed $value
284      * @param \PDOStatement $statement
285      */
286     private function bindParam($key, $value, $statement)
287     {
288         if (is_numeric($value)) {
289             $statement->bindParam($key, $value, \PDO::PARAM_INT);
290         } elseif (is_null($value)) {
291             $statement->bindParam($key, $value, \PDO::PARAM_NULL);
292         } else {
293             $statement->bindParam($key, $value, \PDO::PARAM_STR);
294         }
295     }
296 }
基于php封装的操作mysql的类

 

posted @ 2018-07-20 10:14  rianley  阅读(8561)  评论(0)  编辑  收藏  举报