mysqli扩展库操作mysql数据库

• 配置环境

配置php.ini文件让php支持mysqli扩展库

extension=php_mysqli.dll

• 建库建表

详见博客 “mysql扩展库操作mysql数据库”

• 查询数据库

<?php
    //mysqli扩展库操作mysql数据库，面向对象
    $mysqli=new MySQLi("localhost","root","root","test");
    if($mysqli->connect_error){
        die("连接数据库失败".$mysqli->connect_error);
    }
    $sql="select * from user1";
    $res=$mysqli->query($sql);
    while($row=$res->fetch_row()){
        foreach($row as $key=>$val){
            echo "--".$val;
        }
        echo "<br/>";
    }
    $res->free();
    $mysqli->close();
?>

结果如下：

• 增删改数据库

<?php
    //mysqli扩展库操作mysql数据库，面向对象
    $mysqli=new MySQLi("localhost","root","root","test");
    if($mysqli->connect_error){
        die("连接数据库失败".$mysqli->connect_error);
    }
    //$sql="insert into user1 (name,password,age,birthday) values ('wpp',md5('wpp'),25,'1991-05-05')";
    //$sql="update user1 set age=24 where name='wpp'";
    $sql="delete from user1 where name='wpp'";
    $res=$mysqli->query($sql);
    if(!$res){
        echo "操作失败".$mysqli->error;
    }else{
        if($mysqli->affected_rows>0){
            echo "操作成功";
        }else{
            echo "没有行受到影响";
        }
    }
    $mysqli->close();
?>

•  把数据库操作封装成类

mysqliTool.class.php  数据库操作文件

<?php
    class MysqliTool{
        private $mysqli;
        private $host="localhost";
        private $userName="root";
        private $pwd="root";
        private $db="test";
        public function __construct(){
            $this->mysqli=new MySQLi($this->host,$this->userName,$this->pwd,$this->db);
            if($this->mysqli->connect_error){
                die("连接失败".$this->mysqli->connect_error);
            }
            $this->mysqli->query("set names utf8");
        }
        public function executeDql($sql){
            $res=$this->mysqli->query($sql) or die("操作dql失败".$this->mysqli->error);
            return $res;
        }
        public function executeDml($sql){
            $res=$this->mysqli->query($sql);
            if(!$res){
                return 0;//0表示操作失败
            }else{
                if($this->mysqli->affected_rows>0){
                    return 1;//1表示操作成功
                }else{
                    return 2;//2表示没有行受到影响
                }
            }
        }
        public function mysqliClo(){
            $this->mysqli->close();
        }
    }
?>

执行dql语句：

<?php
    //引入类文件
    require_once "mysqliTool.class.php";
    //把类实例化
    $mysqliTool=new MysqliTool();
    //dql语句
    $sql="select * from user1";
    //执行sql语句
    $res=$mysqliTool->executeDql($sql);
    while($row=$res->fetch_row()){
        foreach($row as $key=>$val){
            echo "--".$val;
        }
        echo "<br/>";
    }
    $res->free();
    $mysqliTool->mysqliClo();
?>

执行dml语句：

<?php
    //引入类文件
    require_once "mysqliTool.class.php";
    //把类实例化
    $mysqliTool=new MysqliTool();
    //dml语句
    //$sql="insert into user1 (name,password,age,birthday) values ('wpp',md5('wpp'),25,'1991-05-05')";
    //$sql="update user1 set age=24 where name='wpp'";
    $sql="delete from user1 where name='wpp'";
    //执行sml语句
    $res=$mysqliTool->executeDql($sql);
    if($res==0){
        echo "操作失败";
    }else if($res==1){
        echo "操作成功";
    }else if($res==2){
        echo "没有行受到影响";
    }
    $mysqliTool->mysqliClo();
?>