[Laravel 5.2 Document] Database--Start

1. Introduction

Laravel makes it easy to connect multiple databases and query the database, whether using native SQL, Query Builder, or eloquent ORM. Currently, Laravel supports four types of database systems:

• Mysql
• Postgres
• Sqlite
• SQL Server

Configuration

Laravel makes it easy to connect to a database and run queries. The database configuration for the app is located in config/database.php. In this file you can define all the database connections and specify which connection is the default connection. This file provides a sample configuration of all supported database systems.

By default, the Laravel sample environment configuration has been set for Laravel homestead, and you can, of course, modify the configuration for the local database as needed.

Read/write Connection

Sometimes you want to use a database connection to do the query, another database connection to do the insertion, update and delete, Laravel makes it easy, whether you use native SQL, query Builder, or eloquent ORM, the appropriate connection will always be used.

To know how to configure a read/write connection, let's take a look at the following example:

' MySQL ' = ['    read ' and ' = '        host ' = ' 192.168.1.1 ',    ],    ' write ' = [        ' host ' and ' = ' 196.168.1.2 '    ],    ' driver ' = '    mysql ',    ' database ' = '  database ',    ' username '  = > ' root ',    ' password '  = ', '    charset '   = ' utf8 ',    ' collation ' = ' utf8_unicode_ ' Ci ',    ' prefix '    = ', ',],

Note that we have added two new keys to the configuration array: Read and write, both of which correspond to an array containing a single key "host", and other database configuration options for read/write connections that share the primary array configuration of MySQL.

If we want to overwrite the configuration in the master array, simply place the corresponding configuration item in the Read and write arrays. In this example, 192.168.1.1 will be used as a "read" connection, and 192.168.1.2 will be used as a "write" connection. The credentials (user name/password), prefix, character set, and other configuration of the two database connections will share the settings in the MySQL array.

2. Run native SQL queries

Once you have configured the database connection, you can use the DB façade to run the query. The DB façade provides the appropriate method for each query: Select, UPDATE, INSERT, delete, and statement.

Run a Select query

To run a most basic query, you can use the DB façade's Select method:

 
  $users]);}    }

The first argument passed to the Select method is a native SQL statement, and the second parameter binds to the parameter binding of the query, which is typically the value in the WHERE clause constraint. Parameter bindings can avoid SQL injection attacks.

The Select method returns the result set as an array, and each result in the array is a PHP Stdclass object, allowing you to access the result value as follows:

foreach ($users as $user) {    echo $user->name;}

Using named Bindings

In addition to using the? Placeholder to represent parameter bindings, you can also use a named binding to execute a query:

$results = Db::select (' select * from users WHERE id =: id ', [' id ' = 1]);

Run the INSERT statement

Executes the INSERT statement using the Insert method of the DB façade. As with select, the method takes the native SQL statement as the first parameter and binds the binding as the second parameter:

Db::insert (' INSERT into users (ID, name) VALUES (?,?) ', [1, ' Dayle ']);

Run the UPDATE statement

The Update method is used to update records that already exist in the database, which returns the number of rows affected by the updated statement:

$affected = db::update (' update users set votes = + WHERE name =? ', [' John ']);

Run the DELETE statement

The Delete method deletes a record that already exists in the database, and, like update, returns the number of rows that were deleted:

$deleted = DB::d elete (' Delete from users ');

Run a common statement

Some database statements do not return any values, and for this type of operation, you can use the statement method of the DB façade:

Db::statement (' drop table users ');

Listening for query events

If you want to get the execution of every SQL statement in your app, you can use the Listen method, which is useful for query logging and debugging, and you can register a query listener in a service provider:

     
3. Database Transactions
    
To run a sequence of operations in a database transaction, you can use the transaction method of the DB façade, and the transaction will be automatically rolled back if an exception is thrown in the transaction closure. If the closure execution succeeds, the transaction is automatically committed. There is no need to worry about manual rollback or submission when using the transaction method:
    
Db::transaction (function () {    db::table (' users ')->update ([' votes ' = 1]);    Db::table (' posts ')->delete ();});
    
Using Transactions manually
    
If you want to manually start a transaction to have a complete control over rollback and commit, you can use the BeginTransaction method of the DB façade:
    
Db::begintransaction ();
    
You can roll back a transaction by using the Rollback method:
    
Db::rollback ();
    
Finally, you can commit the transaction through the Commit method:
    
Db::commit ();
    
         
Note: Transaction methods that use the DB façade can also be used to control the query builder and eloquent ORM transactions.
    
    
4. Use multiple database connections
    
When using multiple database connections, each connection can be accessed using the connection method of the DB façade. The connection name passed to the connection method corresponds to the corresponding connection in the configuration file config/database.php:
    
$users = db::connection (' foo ')->select (...);
    
You can also use the Getpdo method on the connection instance to the underlying native PDO instance:
    
$pdo = Db::connection ()->getpdo ();
