Laravel5.1 Learning Notes 15 Getting Started with database 1 databases

• Introduction
• Running Raw SQL Queries
  • Listening for Query Events
• Database transactions
• Using multiple Database Connections

Introduction

Laravel makes connecting with databases and running queries extremely simple across a variety of database back-ends using Either raw SQL, the fluent Query Builder, and the eloquent ORM. Currently, Laravel supports four database systems:

• Mysql
• Postgres
• Sqlite
• SQL Server

Configuration

Laravel makes connecting with databases and running queries extremely simple. The database configuration for your application are located at config/database.php . In the This file define all of your database connections, as well as specify which connection should is used by defaul T. Examples for all of the supported database systems is provided in this file.

By default, Laravel's sample environment configuration is ready for use with Laravel Homestead, which is a convenient Virtu Al machine for doing Laravel development in your local machine. Of course, you is free to modify this configuration as needed for your local database.

Read/write Connections

Sometimes wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE St Atements. Laravel makes this a breeze, and the proper connections would always being used whether you is using raw queries, the query B Uilder, or the eloquent ORM.

To see how read/write connections should is configured, let's look at this example:

‘mysql‘ => [    ‘read‘ => [        ‘host‘ => ‘192.168.1.1‘,    ],    ‘write‘ => [        ‘host‘ => ‘196.168.1.2‘    ],    ‘driver‘    => ‘mysql‘,    ‘database‘  => ‘database‘,    ‘username‘  => ‘root‘,    ‘password‘  => ‘‘,    ‘charset‘   => ‘utf8‘,    ‘collation‘ => ‘utf8_unicode_ci‘,    ‘prefix‘    => ‘‘,],

Note that both keys has been added to the configuration array: read and write . Both of these keys has an array of values containing a single key: host . The rest of the database options for the and connections'll is merged from the read write main mysql array.

So, we are need to place items in the read and write arrays if we wish to override the values in the main array. So, in this case, 'll be 192.168.1.1 used as the "read" connection and while'll be used as the 192.168.1.2 "write" connection. The database credentials, prefix, character set, and all other options in the main mysql array would be shared across both Connections.

Running Raw SQL Queries

Once you has configured your database connection, you may run queries using the DB facade. The DB facade provides methods for each type of query:,,, and select update insert statement .

Running A Select Query

To run a basic query, we can use the select method on the DB facade:

<?phpnamespace App\Http\Controllers;use DB;use App\Http\Controllers\Controller;class UserController extends Controller{    /**     * Show a list of all of the application‘s users.     *     * @return Response     */    public function index()    {        $users = DB::select(‘select * from users where active = ?‘, [1]);        return view(‘user.index‘, [‘users‘ => $users]);    }}

The first argument passed select to the method are the raw SQL query, while the second argument are any parameter bindings th At need to is bound to the query. Typically, these is the values of the where clause constraints. Parameter binding provides protection against SQL injection.

The method would always return an of select array results. Each result within the array would be a PHP StdClass object and allowing you to access the values of the results:

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

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

$results = DB::select(‘select * from users where id = :id‘, [‘id‘ => 1]);

Running an Insert Statement

insertto execute a statement, you could use the method on the insert DB facade. Like select , this method takes the raw SQL query as its first argument, and bindings as the second argument:

DB::insert(‘insert into users (id, name) values (?, ?)‘, [1, ‘Dayle‘]);

Running an Update Statement

The update method should is used to update existing records in the database. The number of rows affected by the statement would be returned by the method:

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

Running A Delete Statement

The delete method should is used to delete records from the database. Like update , the number of rows deleted would be returned:

$deleted = DB::delete(‘delete from users‘);

Running A General Statement

Some database statements should not return any value. For these types of operations, the the method on the statement DB facade:

DB::statement(‘drop table users‘);

Listening for Query Events

If you would a-to-receive each SQL query executed by your application, you could use the listen method. This method was useful for logging queries or debugging. You could register your query listener in a service provider:

<?phpnamespace App\Providers;use DB;use Illuminate\Support\ServiceProvider;class AppServiceProvider extends ServiceProvider{    /**     * Bootstrap any application services.     *     * @return void     */    public function boot()    {        DB::listen(function($sql, $bindings, $time) {            //        });    }    /**     * Register the service provider.     *     * @return void     */    public function register()    {        //    }}

Database transactions

To run a set of operations within a database transaction, the method on the transaction DB facade. If an exception is thrown within the transaction Closure , the transaction would automatically be rolled back. If Closure The executes successfully, the transaction would automatically be committed. You don ' t need to worry about manually rolling back or committing while using the transaction method:

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

Manually Using transactions

If you would like to begin a transaction manually and has complete control over rollbacks and commits, you could use the method on the DB facade:

DB::beginTransaction();

You can rollback the transaction via the rollBack method:

DB::rollBack();

Lastly, you can commit a transaction via the commit method:

DB::commit();

Note: Using the DB facade ' s transaction methods also controls transactions for the Query builder Andeloquent ORM.

Using multiple Database Connections

When using multiple-connections, you could access each connection via the the method on the connection DB facade. The passed to the method should correspond to one of the name connection connections listed in your config/database.php configuration file:

$users = DB::connection(‘foo‘)->select(...);

Also access the raw, underlying PDO instance using the method on getPdo a connection instance:

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

Laravel5.1 Learning Notes 15 Getting Started with database 1 databases