Detailed explanation of PHP operating database based on ORM

<?php

  /*

   * author:Tammy Pi

   * function:Model类的基类，封装set/get操作

   */

  class BasicModel{

    private $map = null;

    function TbUser() {

      $this->map = array();

    }

    function __set($key,$value){

      $this->map[$key] = $value;

    }

    function __get($key){

      return $this->map[$key];

    }

    function __call($name,$arguments) {

      if(substr($name,0,3)=='set'){

        $this->__set(strtolower(substr($name,3)),$arguments[0]);

      }else{

        return $this->__get(strtolower(substr($name,3)));

      }

    }

  }

?>

<?php

  require_once("BasicModel.php");

  class TbUser extends BasicModel{

  }

?>

<?php

  interface IBasicDAO {

    public function findByWhere($where);

    public function findWhereOrderBy($where,$order,$start=null,$limit=null);

    public function save($obj);

    public function delete($obj);

    public function update($obj);

  }

?>

<?php

  require_once("IBasicDAO.php");

  class BasicDAO implements IBasicDAO{

    protected $modelName = null;

    private $tableName = null;

    private $h = "localhost";

    private $user = "root";

    private $pass = "root";

    private $db = "db_toilet";

    //获得连接

    public function getConnection(){

      $conn = mysqli_connect($this->h,$this->user,$this->pass,$this->db);

      return $conn;

    }

    //初始化

    public function init() {

      //根据model的名字得到表的名字

      $this->tableName = strtolower(substr($this->modelName,0,2))."_".strtolower(substr($this->modelName,2));

    }

    //获得一个表的列名

    public function getColumn($tableName) {

      $sql = "show columns from ".$tableName;

      $conn = $this->getConnection();

      $columns = array();

      if($conn!=null){

        $rtn = mysqli_query($conn,$sql);

        while($rtn!==false&&($row=mysqli_fetch_array($rtn))!=null){

          $columns[] = $row[0];

        }

        mysqli_close($conn);

      }

      return $columns;

    }

    //条件查询

    public function findByWhere($where){

      //获得数据表的列名

      $columns = $this->getColumn($this->tableName);

      //拼接sql语句

      $sql = "select * from ".$this->tableName." where ".$where;

      $conn = $this->getConnection();

      $arr = array();

      if($conn!=null){

        $rtn = mysqli_query($conn,$sql);

        while($rtn!==false&&($row=mysqli_fetch_array($rtn))!=null){

          $index = -1;

          $obj = new $this->modelName();

          foreach($columns as $column){

            $obj->{"set".ucfirst($column)}($row[++$index]);

          }

          $arr[] = $obj;

        }

        mysqli_close($conn);

      }

      return $arr;

    }

    //分页查询；支持排序

    public function findWhereOrderBy($where,$order,$start=null,$limit=null){

      //获得数据表的列名

      $columns = $this->getColumn($this->tableName);

      //拼接sql语句

      $sql = "select * from ".$this->tableName." where ".$where." order by ".$order;

      if($start!=null&&$limit!=null){

        $sql .= "limit ".$start.",".$limit;

      }

      $conn = $this->getConnection();

      $arr = array();

      if($conn!=null){

        $rtn = mysqli_query($conn,$sql);

        while($rtn!==false&&($row=mysqli_fetch_array($rtn))!=null){

          $index = -1;

          $obj = new $this->modelName();

          foreach($columns as $column){

            $obj->{"set".ucfirst($column)}($row[++$index]);

          }

          $arr[] = $obj;

        }

        mysqli_close($conn);

      }

      return $arr;

    }

    //保存操作

    public function save($obj){

      $columns = $this->getColumn($this->tableName);

      $conn = $this->getConnection();

      $tag = false;

      if($conn!=null){

        $sql = "insert into ".$this->tableName."(";

        foreach($columns as $column){

          $sql .= $column.",";

        }

        $sql = substr($sql,0,strlen($sql)-1).") values(";

        foreach($columns as $column){

          $value = $obj->{"get".ucfirst($column)}();

          //判断$value的类型

          if($value==null){

            $sql .= "null,";

          }else if(preg_match("/^[0-9]*$/", $value)){

            //是数字

            $sql .= $value.",";

          }else{

            $sql .= "'".$value."',";

          }

        }

        $sql = substr($sql,0,strlen($sql)-1);

        $sql .= ")";

        //执行sql语句

        mysqli_query($conn,$sql);

        $tag = true;

        mysqli_close($conn);

      }

      return $tag;

    }

    //删除操作

    public function delete($obj){

      $conn = $this->getConnection();

      $tag = false;

      if($conn!=null){

        $sql = "delete from ".$this->tableName." where ";

        $columns = $this->getColumn($this->tableName);

        $value = $obj->{"get".ucfirst($columns[0])}();

        if($value!=null){

          //是数字

          if(preg_match("/^[0-9]*$/", $value)){

            $sql .= $columns[0]."=".$value;

          }else{

            $sql .= $columns[0]."='".$value."'";

          }

          //执行

          mysqli_query($conn,$sql);

          $tag = true;

        }

        mysqli_close($conn);

      }

      return $tag;

    }

    //更新操作

    public function update($obj){

      $conn = $this->getConnection();

      $columns = $this->getColumn($this->tableName);

      $tag = false;

      if($conn!=null){

        $sql = "update ".$this->tableName." set ";

        for($i=1;$i<count($columns);$i++){

          $column = $columns[$i];

          $value = $obj->{"get".ucfirst($columns[$i])}();

          if($value==null){

            $sql .= $column."=null,";

          }else if(preg_match("/^[0-9]*$/",$value)){

            $sql .= $column."=".$value.",";

          }else{

            $sql .= $column."='".$value."',";

          }

        }

        $sql = substr($sql,0,strlen($sql)-1);

        $sql .= " where ";

        $tempColumn = $columns[0];

        $tempValue = $obj->{"get".ucfirst($columns[0])}();

        if(preg_match("/^[0-9]*$/", $tempValue)){

          $sql .= $tempColumn."=".$tempValue;

        }else{

          $sql .= $tempColumn."='".$tempValue."'";

        }

        //执行操作

        mysqli_query($conn,$sql);

        $tag = true;

        mysqli_close($conn);

      }

      return $tag;

    }

  }

?>

<?php

  require_once("BasicDAO.php");

  require_once("../model/TbUser.php");

  class TbUserDAO extends BasicDAO{

    function TbUserDAO(){

      $this->modelName = 'TbUser';

      parent::init();

    }

  }

?>

$tbUserDAO = new TbUserDAO();

$tbUser = new TbUser();

$tbUser->setUserid("fetchingsoft@163.com");

$tbUser->setUsername("fetching");

$tbUserDAO->update($tbUser);

echo "执行成功！";

print_r($list);