PHP操作数据库(MySQLi版)学习心得

namespace mysqli;

//建表语句：

/*

CREATE TABLE `t_category` (

`id`                 int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

`name`             varchar(50) NOT NULL ,

`createtime`   date NOT NULL ,

`status`           int(11) NOT NULL,

`state`             int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

*/

return [

         'type'=>$type ?? 'mysql',

         'host'=>$host ?? 'localhost',

         'dbname'=>$dbname ?? 'php7',

         'charset'=>$charset ?? 'utf8',

         'port'=>$port ?? '3306',

         'username'=>$username ?? 'root',

         'password'=>$password ?? ''

];

namespace mysqli;

        $config=require __DIR__.'/database.php';

//mysqli连接四大参数：host,username,password,dbname;

        extract($config);//将关联数组的键值对改为键名为变量名的名值对；

//连接

        $mysqli=new \mysqli($host, $username, $password,$dbname);

        if($mysqli->connect_errno) echo $mysqli->connect_error;

//设置客户端默认的字符编码集：

        $mysqli->set_charset($charset);

        

        var_dump($mysqli);

        

namespace mysqli;

//1.连接数据库-操作数据库-关闭数据库；

        require 'connect1.php';

        $o=['c6','20200515',1,1];

        array_walk($o,function (&$o,$k,$length){

                 if($k<$length-2){

                 $o="'$o'";

         }

        },count($o));

        $data=implode(',',$o);

        

        $sql="INSERT t_category (`name`,`createtime`,`state`,`status`) VALUES($data)";

        

        if($mysqli->query($sql)){

                 if($mysqli->affected_rows>0){

                          echo '成功添加了'.$mysqli->affected_rows.'条记录，心中记录主键ID：'.$mysqli->insert_id;

                 }else {

                         die('添加失败'.$mysqli->errno.':'.$mysqli->errno);

                 }

        }

        

//关闭链接【可选】；

        $mysqli->close();

namespace mysqli;

//1.连接数据库-操作数据库-关闭数据库；

        require 'connect1.php';

        

        $sql='SELECT * FROM `t_category` WHERE `id`=1';

        

        $res=$mysqli->query($sql);

        

//fetch_array:返回一条记录，指针下移1；所以在使用前，需要指针复位

        $res->data_seek(0);

        $res1=$res->fetch_array(MYSQLI_ASSOC);//索引部分参数：MYSQLI_NUM;全部：BOTH

//多条：fetch_all(MYSQLI_ASSOC);

//严谨写法

        if($res && $res->num_rows>0){

                 var_dump($res1);

        }else {

                 echo '无记录';

        }

echo '<hr>';

//关闭链接【可选】；

        $res->free_result();

        $mysqli->close();

namespace mysqli;

//1.连接数据库-操作数据库-关闭数据库；

        require 'connect1.php';

        $o=['name'=>'c7','createtime'=>'20200515','status'=>1,'state'=>1];

        array_walk($o,function (&$o,$k,$length){

         if($k<$length-2){

                  $o="`$k`='$o'";

         }

        },count($o));

        $data=implode(',',$o);

        

        $sql="UPDATE `t_category` SET".$data."WHERE `id`=7";

        

        if($mysqli->query($sql)){

                 if($mysqli->affected_rows>0){

                          echo '成功更新了'.$mysqli->affected_rows.'条记录';

                 }else {

                          echo '没有更新任何记录';

                 }

        }else {

                 die('更新失败'.$mysqli->errno.':'.$mysqli->errno);

        }

        

//关闭链接【可选】；

$mysqli->close();

namespace mysqli;

//1.连接数据库-操作数据库-关闭数据库；

        require 'connect1.php';

        

        $sql="DELETE FROM `t_category` WHERE `id`=7";

        

        if($mysqli->query($sql)){

                 if($mysqli->affected_rows>0){

                          echo '成功删除';

                 }else {

                         echo '没有删除任何记录';

                 }

        }else {

                 die('删除失败'.$mysqli->errno.':'.$mysqli->errno);

        }

//关闭链接【可选】；

        $mysqli->close();