How to write your own database package (1)

Write it at the front

1. I am still a rookie, please point out any mistakes by lightly commenting

2. Due to shallow work experience, database connection cannot be fully covered. More complex queries may have unexpected bugs

3. Therefore, this series only provides ideas, which are mature and stable. Please search for the database encapsulation by yourself

4. ##The original intention of writing this series is to inspire others. At the back end of each section, I will ask some personal questions (or not ), I hope it can lead to some masters to answer my questions

5. The PHP version used is 7.0+. This series is not backward compatible. Children who are still in version 5.6, please come and play.

6. The database encapsulation refers to the source code of laravel. If there is any similarity, it is not a coincidence

Final effect

Suppose we have a The table, named 'Actor', can be directly called as follows after simple settings.

$a = Actor::select('first_name', 'last_name')
        ->where('first_name', 'NICK')
        ->where('last_name', 'WAHLBERG')
        ->first()
var_dump($a);

Return format

object(Actor)[11]
  public 'first_name' => string 'NICK' (length=4)
  public 'last_name' => string 'WAHLBERG' (length=8)

The returned data is still operable (up

date/ delete)

$a->update(...);
$a->delete();

Common database connection writing methods

Everyone who has a certain understanding of php knows that compared with the eliminated

mysql or the replaced mysqli , pdo can avoid SQL injection attacks, is more safe, and is object-oriented, so the following uses pdo as an example

<?php
$driver=&#39;mysql&#39;; //数据库类型

$host=&#39;localhost&#39;; //数据库主机名

$db = &#39;sakila&#39;; //数据库名称

$username=&#39;root&#39;; //数据库连接用户名

$password=&#39;&#39;; //数据库密码

$dsn="$driver:host=$host;dbname=$db";

try {    
    $pdo= new PDO($dsn, $username, $password); //初始化一个PDO对象   

    $sql = "select * from actor";

    $res = $pdo->query($sql); // 从actor中获取所有数据

    foreach($res as $row) echo $row['first_name']."<br>";

} catch (PDOException $e) {    
    die($e->getMessage());
}

The above example is very concise. When a project gradually When it grows and becomes more complex, we need an encapsulation class to reduce the duplication of code, just like some auxiliary

functions that we usually write by ourselves, but the encapsulation class is just a little more advanced.

Basic idea

So, how should we write it?

First of all, when we need to simplify some code, we usually write some auxiliary functions by ourselves. For example, I personally have a bad habit and don’t like to use the
debugging function of the editor ( Voice-over: I use sublime text 3), so when debugging, I write it like this at the beginning.

$a = "is bug";
die(var_dump($a)); // 输出变量并且停止运行之后的代码
/***从xdebug得知bug出现在这一行，所以查看上一行的逻辑与数据 ***/

die(var_dump()) has a slightly higher usage rate, so I wrote them as an auxiliary function.

function dd($var) {
    die(var_dump($var));
}

For the same reason, when reading the database, the appearance rate of select is extremely high, so simply wrap it up

function select($pdo, $table, $require, $where = []) {
    $w = [];

    // 将搜索条件转化为数据库命令能接受的格式
    foreach ($where as $key => &$val) 
        $w[] = "$key = '$val'";

    // 有时候并不需要条件，仅仅将所有数据取出，因此$where默认为空
    if(!empty($w)) $w = "where ".implode(', ', $w);
    else $w = "";

    // 生成sql query
    $sql = "select $require from $table $w";

    // 将已生成的query带入pdo实例
    $res = $pdo->query($sql);

    // 返回结果
    return $res->fetchAll();
}

Example

select($pdo, 'actor', '*', ['first_name'=>'PENELOPE'])
/** 
  * 生成sql query => select * from actor where first_name = 'PENELOPE'
**/

To summarize, database encapsulation is simple The description is to bring parameters into the auxiliary function and let it automatically generate SQL commands.

Database encapsulated

architecture

is mainly divided into four files

##Connector.php - responsible for communication with the database, request And return the database data
• Model

  .php - Entry file, responsible for setting up the table, accepting the request, and returning the response

Grammar.php - Converts requests stored by Builder into SQL statements
Builder.php - Core file, stores requests for Model, calls Grammar to return SQL The statement is sent to the Connector together with the parameter variables to obtain the database data, and then returned to the Model
The architecture may not be explained well, but it doesn’t matter. I will slowly explain it one by one next. In-depth explanation

It’s roughly like this. If you can, give it a like or discuss it in the comment area below. Only feedback can push a lazy cancer patient to move forward.

The above is the detailed content of How to write your own database package (1). For more information, please follow other related articles on the PHP Chinese website!