A good model of the organization ' s structure for the database



  • How is it right to structure php data files? To date, I am following the following structure: There is one large php class(database.class.php) that describes how the website components interact with the database. I mean, all the methods used to work with the database are collected in the classroom.

    class DataBase
    {
        private $mysqlSession; 
    
    public function __construct($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport)
    {
        $this->mysqlSession = new mysqli($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport);
    
        if($this->mysqlSession->connect_errno)
            exit("Could not connect to MySQL: (".$this->mysqlSession->connect_errno.") ".$this->mysqlSession->connect_error);
    
        if(!$this->mysqlSession->set_charset("utf8"))
            exit("Error loading the character set utf8: ".$this->mysqlSession->error);
    }
    
    function __destruct()
    {
        $this->mysqlSession->close();
    }
    
    function getModelsByIdMark($id)
    {
        if($stmt = $this->mysqlSession->prepare("SELECT * FROM `models` WHERE id_mark=? ORDER BY `id` ASC"))
        {
            $stmt->bind_param("i", $id);
            $stmt->execute();
            $result = $stmt->get_result();
            $stmt->close();
        }
        else
            exit("Failed to prepare a request "."(".$this->mysqlSession->errno .") ".$this->mysqlSession->error);
    
        return $result;
    }       
    
    function getMarks()
    {
        ...
    }       
    
    function getCitys()
    {
        ...
    }       
    
    function getCats()
    {
        ...
    }
    
    function getOrderById($id)
    {
        ...
    }   
    
    function createorder($name, $cityid, $email, $idmark, $idmodel, $idcat, $comment, $file, $phone = 0)
    {
        ...
    }
    
    function addcomment($name, $email, $comment, $ip)
    {
        ...
    }
    
    function log($name, $logmsg, $loglevel = 0)
    {
        ...
    }
    

    }

    In one of the forums, I read that another model could be used, that each site module could have its own class and record its methods of dealing with the database, but then the question arose is, if one page 2 of the module(s) could be used, then two links with the database would be opened.

    class Order
    {
    private $mysqlSession;
    ... // Other attr

    public function __construct($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport)
    {
        $this->mysqlSession = new mysqli($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport);
    
        if($this->mysqlSession->connect_errno)
            exit("Could not connect to MySQL: (".$this->mysqlSession->connect_errno.") ".$this->mysqlSession->connect_error);
    
        if(!$this->mysqlSession->set_charset("utf8"))
            exit("Error loading the character set utf8: ".$this->mysqlSession->error);
    }
    
    function __destruct()
    {
        $this->mysqlSession->close();
    }
    
    function getOrderById($id)
    {
        ...
    }   
    
    function createorder($name, $cityid, $email, $idmark, $idmodel, $idcat, $comment, $file, $phone = 0)
    {
        ...
    }
    

    }

    class Comment
    {
    private $mysqlSession;
    ... // Other attr

    public function __construct($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport)
    {
        $this->mysqlSession = new mysqli($mysqlip, $mysqluser, $mysqlpassword, $db, $mysqlport);
    
        if($this->mysqlSession->connect_errno)
            exit("Could not connect to MySQL: (".$this->mysqlSession->connect_errno.") ".$this->mysqlSession->connect_error);
    
        if(!$this->mysqlSession->set_charset("utf8"))
            exit("Error loading the character set utf8: ".$this->mysqlSession->error);
    }
    
    function __destruct()
    {
        $this->mysqlSession->close();
    }
    
    function deletecomment($id)
    {
        ...
    }   
    
    function addcomment($name, $email, $comment, $ip)
    {
        ...
    }
    

    }

    How would it be better to arrange for the storage of methods for handling the database?



  • Both options are for themselves.

    This code,

        if($stmt = $this->mysqlSession->prepare("SELECT * FROM `models` WHERE id_mark=? ORDER BY `id` ASC"))
        {
            $stmt->bind_param("i", $id);
            $stmt->execute();
            $result = $stmt->get_result();
            $stmt->close();
        }
        else
            exit("Failed to prepare a request "."(".$this->mysqlSession->errno .") ".$this->mysqlSession->error);
    

    -- Very good. Not good.
    The programmer will be terrified.

    Known parts on one line

    • "SELECT * FROMmodelsWHERE id_mark=? ORDER BYidASC" and $id

    Everything else is an unnecessary waste, repeatable at Each Treatment of OBD. The method getModelsByIdMark() should look like:

    function getModelsByIdMark($id)
    {
        $sql = "SELECT * FROM `models` WHERE id_mark=? ORDER BY `id` ASC";
        return $this->db->method($sql, [$id]);
    } 
    

    to return data in the right format, and not in mysqli_result, which will then have to be wandered. If one line is expected, one size or object. If many lines are expected, a list consisting of one-dimensional masses or objects.

    Everything else has to be programmed.

    Because that's the point of programming - to automate routine recurring operations.

    I mean, the right structure will be

    • Class for OBD operations. Mysqli doesn't fit this class.
    • Special service for the transfer of the only OBD facility to other classes, so that the connection to the OBD is not duplicated. It could be a direct transfer, or some fashion containers or despised singlton.
    • Separate classes which contain all logic for the operation of the OBD for each module.

    Congratulations, you invented the model, in the sense that it is understood by all modern Framworths! Because that's how they work with the OBD.

    In the most primitive case, a PDO with direct transmission of the instans will be suitable for the OBD class. Then your models will look like:

    class Comment
    {
        private $db; 
        public function __construct($db)
        {
            $this->db = $db;
    
    function delete($id)
    {
        $this->db->prepare("SELECT FROM comments WHERE id=?")->execute([$id]);
    }   
    
    function add($name, $email, $comment, $ip)
    {
        $sql = "INSERT INTO comments VALUES (NULL,?,?,?,?)";
        $this->db->prepare($sql)->execute([$name, $email, $comment, $ip]);
    }
    

    }

    While it is better to make normal MOD to avoid writing a primitive like addcomment manually - methods add and delete will be inherited from a parent class, and only things like getModelsByIdMark will need to be written with hands.

    If there is a biller crust, such a simple request can be recorded immediately in the counteraller as

    $models = Model::where('id_mark',$id);

    I don't want to write to the model either.

    Well, we need to learn modern technology, not to swing with mysqli_query, as some people say.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2