PHP uses ORM for database operations

Source: Internet
Author: User
Tags mssql server
ORM Object Relational mappings, O (object) objects, are entities in a project, more precisely the data model, or persistent classes. R (Relation) relational data, M (Mapping) mapping, the process of mapping an object to relational data, and mapping relational data to an object. More intuitive to understand is that ORM is the idea of OOP, generating additions and deletions to change the SQL statement.

It's better to develop projects faster than pdo,orm rather than write SQL statements. Here are a few useful orm.

Medoo

The following are examples of version 1.0.2.

Environmental requirements

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

How to Install

Medoo supports composer installation and direct download.

To install using composer:

Composer require Catfan/medoocomposer update

Direct download:
Https://github.com/catfan/Medoo/archive/master.zip

Start using

Introduce Medoo and configure the database:

<?php      //using composer installation to introduce      //require ' vendor/autoload.php ';      Direct download of this introduced      require_once ' medoo.php ';       Initialize      $db = new Medoo ([                      ' database_type ' = ' mysql ',                      ' database_name ' = ' test ', '                      server ' = = ') localhost ',                      ' username ' = ' root ',                      ' password ' = ' 123456 ',                      ' charset ' = ' utf8 ',                    //optional: Port                ' Port ' = 3306,                 //Optional: Table prefix                ' prefix ' = ', '/                 /PDO Drive option 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)//Get all records    -table [string] table name    -Columns [string/array] field    -where (optional) [array] Query condition Get ($table, $columns, $where)//Get only one data select ($table, $join, $columns, $where)    -table [string] table name    -Join [AR Ray] Association query, if no can be ignored    -columns [string/array] field    -where (optional) [array] query criteria

Example:

$user = $db->select (' user ', ' * '); Returns all data $user = $db->get (' user ', ' * '); Returns a data $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)); Returns the self-increment ID

Note: If the data contains sub-arrays will be serialized by Serialize (), you can use Json_encode () as the JSON store.

Updated (update):

Update ($table, $data, $where)

Example:

$db->update (' user ', Array (' name ' = ' T5 '), array (' id ' = 23)); Returns the number of rows affected

Remove (delete):

Delete ($table, $where)

Example:

$db->update (' user ',  array (' id ' = 23) ');//Returns the number of rows affected

where

Aggregate queries

$db->has (' user ',  array (' id ' = ' 23 ');//Record whether there is $db->count (' user ',  array (' id[>] ' = 23));//Statistics $ Db->max (' user ', ' age ', Array (' gender ' + 1));//maximum Value $db->min (' User ', ' age ', Array (' gender ' = 2)); The minimum value is $db->avg (' user ',  ' age ', Array (' gender ' = + 2));//Mean $db->sum (' user ',  ' age ', Array (' gender ' = > 2)); Sum

All of the above methods support the second parameter is $join, which is the association 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) {                  //returns FALSE will ROLLBACK TRANSACTION return        false;    }});

Using Query

You can use SQL directly.

Query $data = $db->query ("SELECT * from User")->fetchall ();p rint_r ($data);//delete $db->query ("Delete from user where Name= ' T5 ');

Direct use of PDO

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 the methods of the PDO object.

1.pdo::begintransaction-Start a transaction
2.pdo::commit-Commit a transaction
3.pdo::__construct-creating a PDO instance that represents a database connection
4.pdo::errorcode-gets the SQLSTATE5 associated with the last operation of the database handle. pdo::errorinfo-getting error messages
6.pdo::exec-executes an SQL statement and returns the number of rows affected
7.pdo::getattribute-retrieving the properties of a database connection
*8.pdo::getavailabledrivers-returns an array of available drives (learn about it)
*9.pdo::intransaction-check whether within a transaction (understand)
10.pdo::lastinsertid-returns the ID or sequence value of the last inserted row
11.PDO::p repare-Create a preprocessing of SQL, return Pdostatement object
12.pdo::query-for executing query SQL statements, returning pdostatement objects
13.pdo::quote-adding single quotes to a SQL string
14.pdo::rollback-rolling back a transaction
15.pdo::setattribute-Setting properties

Example:

$stmt = $pdo->query (' select * from user Limit 2 '); Returns a Pdostatement object//$row = $stmt->fetch (); Gets the next row from the result set for the while loop $rows = $stmt->fetchall (); Get all Print_r ($rows);

PDO transaction:

$pdo->begintransaction ();//Turn on transaction processing    TRY{//PDO preprocessing and execute statement        ... $pdo->commit ();//COMMIT Transaction    }catch (pdoexception $e) {    $pdo->rollback ();//transaction rollback    //Related error handling    throw $e;}

Using the Debug

Debug () Print the final SQL statement

The SQL statement can be printed with the debug () method in front of the Select, GET, INSERT, UPDATE, and other methods, and the program will not continue to run:

$user = $db->debug ()->select (' User ', ' * '); Select ' Name ', ' age ' from ' user ' WHERE ' age ' <= 20

Error () returns an error message for the last operation

$db->select (' User3 ', ' * '); Var_dump ($db->error ());

Log () returns all SQL query statements without affecting 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, without affecting the query's normal execution

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

Related reading

A summary of some understandings on PHP PDO

The method of PDO in PHP to realize the deletion and modification of database

PHP connects MSSQL Server DB instance with PDO

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.