Home > Article > Backend Development > PHP uses ORM for database operations

PHP uses ORM for database operations

*文Original: 2017-12-21 15:56:342771browse

ORM Object Relational Mapping, O (Object) object, is the entity in the project, more precisely, it is the data Model, which can also be said to be the persistence class. R (Relation) relational data, M (Mapping) mapping, the process of mapping objects to relational data, and mapping relational data to objects. A more intuitive understanding is that ORM uses OOP thinking to generate add, delete, modify and query SQL statements.

Compared with PDO, ORM is more suitable for rapid development projects without writing SQL statements. Here are some useful ORMs.

Medoo

The following uses version 1.0.2 as an example.

Environment requirements

PHP 5.1+, PHP 5.4+ is recommended and supports PDO.
At least one of MySQL, MSSQL, and SQLite is installed.

How to install

Medoo supports Composer installation and direct download.

Install using Composer:

composer require catfan/Medoo
composer update

Download directly:
https://github.com/catfan/Medoo/archive/master.zip

Get started

Introduce Medoo and configure the database:

 'mysql',    
                  'database_name' => 'test',    
                  'server' => 'localhost',    
                  'username' => 'root',    
                  'password' => '123456',    
                  'charset' => 'utf8',    
                //可选：端口
                'port' => 3306, 
                //可选：表前缀
                'prefix' => '', 
                // PDO驱动选项 http://www.php.net/manual/en/pdo.setattribute.php
                'option' => [ PDO::ATTR_CASE => PDO::CASE_NATURAL ]
      ]);

If it is SQLite:

$database = new medoo([    'database_type' => 'sqlite',    'database_file' => 'my/database/path/database.db']);

CURD

Query (Read):

select($table, $columns, $where) //获取所有记录
    - table [string] 表名
    - columns [string/array] 字段
    - where (可选) [array] 查询条件get($table, $columns, $where) //仅获取一条数据select($table, $join, $columns, $where)
    - table [string] 表名
    - join [array] 关联查询，如果没有可以忽略
    - columns [string/array] 字段
    - where (可选) [array] 查询条件

Example:

$user = $db->select('user', '*'); 
//返回所有数据
$user = $db->get('user', '*'); 
//返回一条数据
$user = $db->select('user','*', array('name ' => 'joy'));
$user = $db->select('user','name', array('age[>] ' => 20)); 
$user = $db->select('user',['name','age'], array('age[<=] ' => 20));

New (Create):

insert($table, $data)

Example:

$db->insert('user', array('name'=> 't3', 'age'=>22)); //返回自增id

Note: If the data contains subarrays, it will Serialized by serialize(), you can use json_encode() to store it as JSON.

Update:

update($table, $data, $where)

Example:

$db->update('user', array('name'=> 't5'), array('id'=> 23)); //返回受影响的行数

Delete:

delete($table, $where)

Example:

$db->update('user',  array('id'=> 23)); //返回受影响的行数

where

Aggregation query

$db->has('user',  array('id'=> 23)); //记录是否存在
$db->count('user',  array('id[>]'=> 23));//统计
$db->max('user', 'age', array('gender'=> 1));//最大值
$db->min('user', 'age', array('gender'=> 2)); //最小值
$db->avg('user',  'age', array('gender'=> 2)); //平均值
$db->sum('user',  'age', array('gender'=> 2)); //求和

Above All methods support the second parameter $join, which is a related query.

Transaction mechanism

$db->action(function($db) {    
      try{
        $db->insert("account", [ "name" => "foo", "email" => "bar@abc.com" ]);
        $db->delete("account", [ "user_id" => 2312 ]);
    }catch(Exception $e){        
          // 返回false就会回滚事务
        return false;
    }
});

You can use SQL directly using query

//查询
$data = $db->query("SELECT * FROM user")->fetchAll();
print_r($data);
//删除
$db->query("DELETE FROM user where name='t5' ");

Use PDO directly

Medoo is based on PDO, so you can call the PDO instance directly.

Get the PDO instance:

$pdo = $db->pdo;

Next, you can use all methods of the PDO object.

1.PDO::beginTransaction — Start a transaction
2.PDO::commit — Commit a transaction
3.PDO::__construct — Create a PDO instance representing a database connection
4.PDO::errorCode — Get the SQLSTATE related to the last operation of the database handle. 5.PDO::errorInfo — Get the error information
6.PDO::exec — Execute a SQL statement and return the number of affected rows
7.PDO::getAttribute — Retrieve the attributes of a database connection
*8.PDO::getAvailableDrivers — Return an array of available drivers (just understand)
*9.PDO::inTransaction — Check whether Within a transaction (just understand it)
10.PDO::lastInsertId — Returns the ID or sequence value of the last inserted row
11.PDO::prepare —Creates SQL preprocessing and returns a PDOStatement object
12.PDO::query — used to execute query SQL statements and return PDOStatement objects
13.PDO::quote — add single quotes to sql strings
14.PDO::rollBack — roll back a transaction
15.PDO::setAttribute — Set attributes

Example:

$stmt = $pdo->query('select * from user limit 2'); //返回一个PDOStatement对象
//$row = $stmt->fetch(); //从结果集中获取下一行，用于while循环
$rows = $stmt->fetchAll(); //获取所有
print_r($rows);

pdo transaction:

$pdo->beginTransaction();//开启事务处理
    try{//PDO预处理以及执行语句...
        $pdo->commit();//提交事务
    }catch(PDOException $e){
    $pdo->rollBack();//事务回滚
    //相关错误处理
    throw $e;
}

Use DEBUG

debug() print The final SQL statement

Add the debug() method in front of the select, get, insert, update and other methods to print the SQL statement, and the program will not continue to run:

$user = $db->debug()->select('user', '*'); 
//SELECT "name","age" FROM "user" WHERE "age" <= 20

error() returns to the end Error information of an operation

$db->select('user3', '*'); 
var_dump($db->error());

log() Returns all SQL query statements and does not affect the normal execution of the query

$db->select('user', '*'); 
var_dump($db->log());

last_query() is similar to log(), but only returns the last SQL query statement, does not affect the normal execution of the query

$db->select('user', '*'); 
var_dump($db->last_query());

PHP uses ORM for database operations

Related articles