Laravel 5.2 database installation configuration and simple example

1. Introduction

Laravel makes it easy to connect to multiple databases and query databases, whether using native SQL, query builder, or Eloquent ORM. Laravel currently supports four types of Database Systems:

MySQL
Postgres
SQLite
SQL Server
Configuration

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

By default, the environment configuration in the Laravel sample environment has been configured for Laravel Homestead. Of course, you can modify the configuration for the local database as needed.

Read/write connection

Sometimes you want to use one database connection for query and the other database connection for insertion, update, and deletion. Laravel makes it easy, whether you use native SQL or the query builder, or Eloquent ORM, suitable connections will always be used.

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

'Mysql' => [
'Read' => [
'Host' => '2017. 168.1.1 ',
],
'Write' => [
'Host' => '2017. 168.1.2'
],
'Driver '=> 'mysql ',
'Database' => 'database ',
'Username' => 'root ',
'Password' => '',
'Charset' => 'utf8 ',
'Colation' => 'utf8 _ unicode_ci ',
'Prefix' => '',
],

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

To overwrite the configurations in the main array, you only need to put the corresponding configuration items in the read and write arrays. In this example, 192.168.1.1 is used as a read connection, while 192.168.1.2 is used as a write connection. The creden (username/password), prefix, character set, and other configurations of the two databases will share the settings in the mysql array.

2. Run native SQL query

After the database connection is configured, you can use the DB facade to run the query. The DB facade provides corresponding methods for each query: select, update, insert, delete, and statement.

Run Select query

To run a basic query, you can use the select method of the DB facade:

<? Php

Namespace App \ Http \ Controllers;

Use DB;
Use App \ Http \ Controllers \ Controller;

Class UserController extends Controller {
/**
* Display the user list
     *
* @ Return Response
*/
Public function index ()
    {
$ Users = DB: select ('select * from users where active =? ', [1]);
Return view ('user. Index', ['users' => $ users]);
    }
}

The first parameter passed to the select method is a native SQL statement. The second parameter must be bound to the query parameter. Generally, these are values in the where clause constraints. Parameter binding can prevent SQL injection attacks.

The select method returns the result set as an array. 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;
}

Use Name Binding

Besides? Besides parameter binding, placeholders can also be used to perform queries:

$ Results = DB: select ('select * from users where id =: ID', ['id' => 1]);
Run the insert statement

Use the insert method of the DB facade to execute the insert statement. Like select, the native SQL statement is used as the first parameter and the binding is used as the second parameter:

DB: insert ('Insert into users (id, name) values (?, ?) ', [1, 'day']);

Run the update statement

The update method is used to update existing records in the database. This method returns the number of rows affected by the update statement:

$ Affected = DB: update ('update users set votes = 100 where name =? ', ['John']);

Run the delete statement

The delete method is used to delete existing records in the database. Like update, this statement returns the number of deleted rows:

$ Deleted = DB: delete ('delete from users ');

Run a general statement

Some database statements do not return any value. For such operations, you can use the statement method of the DB facade:

DB: statement ('drop table users ');

Listen to query events

If you want to obtain the execution of each SQL statement in the application, you can use the listen method, which is useful for query logs and debugging. You can register the query listener in the service provider:

<? Php

Namespace App \ Providers;

Use DB;
Use Illuminate \ Support \ ServiceProvider;

Class AppServiceProvider extends ServiceProvider {
/**
* Start all application services
     *
* @ Return void
*/
Public function boot ()
    {
DB: listen (function ($ query ){
// $ Query-> SQL
// $ Query-> bindings
// $ Query-> time
});
    }

/**
* Register a service provider
     *
* @ Return void
*/
Public function register ()
    {
//
    }
}

3. Database transactions

To run a series of operations in a database transaction, you can use the transaction method of the DB facade. If an exception is thrown in the transaction closure, the transaction will be automatically rolled back. If the closure is successfully executed, the transaction is automatically committed. When using the transaction method, you do not need to worry about manual rollback or submission:

DB: transaction (function (){
DB: table ('users')-> update (['votes '=> 1]);
DB: table ('posts')-> delete ();
});

Manual use of transactions

If you want to manually start the transaction to have full control over rollback and commit, you can use the beginTransaction method on the DB facade:

DB: beginTransaction ();

You can use the rollBack method to roll back a transaction:

DB: rollBack ();

Finally, you can submit the transaction using the commit method:

DB: commit ();

Note: The transaction method using the DB facade can also be used to control the Transactions of the query builder and the Eloquent ORM.
4. Use multiple databases to connect

When multiple databases are connected, you can use the connection method of the DB facade to access each connection. The connection name passed to the connection method corresponds to the connection in the config/database. php configuration file:

$ Users = DB: connection ('Foo')-> select (...);

You can also connect to the underlying native PDO instance of the getPdo method on the instance:

$ Pdo = DB: connection ()-> getPdo ();