Laravel database

Laravel Databases

Database interaction in laravel is easy among all other server side scripting framework with variery of database backends either SQL, query builder or eloquent ORM.

Various DB supported by laravel are:

1. MySQL 5.6+
2. PostgreSQL 9.4+
3. SQLite 3.8.8+
4. SQL Server 2017+

Configuration of DB in Laravel

The directory having database connections are located inside config folder.

config/database.php

All DB connections are defined and default connections can be set. Laravel uses Laravel Homestead virtual machine for development on localmachine which can be modified according to local database.

SQLite Configuration:

A SQLite DB can be created with the command touch

database/database.sqlite

and can be configured by setting up by using database absolute path:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Multiple DB Connections:

Laravel supports multiple database connections and handle easily with connection() method on DB facade. Connections can be accessed in such a way as shown below:

$users = DB::connection('DIFF_DB')->select(...);

Make sure the name passed in connection method should correspond to connection listed in config/database.php file. PDO instance can also be used on the connection instance by syntax shown below by using getPdo() method:

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

Running RAW SQL Queries

After DB configuration you may simply run queries as used in php using DB facade which provides method for each type of query: select, insert, update, delete and other statements. Below shown example and screenshot of the select query and other queries will work in the same manner.
Make UserController.php file and paste the following code:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    public function index()
    {
        $users = DB::select('select * from users where id = ?', [1]);

        return view('user.index', ['users' => $users]);
    }
}

In web.php file add code metioned below:

Route::get('/database','UserController@index');

You must create a users DB in your phpmyadminpannel.

Above query the select() method consist of two parameters: select query and other is parameter binding that is to be bound with query. Instead of using ? in above query named binding can be used as shown below:

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

Screenshot of structure of the working is mentioned below:

Output :

Output :

Few other queries are mentioned below just replace them in controller and run the url to implement.

Insert Query:

DB::insert('insert into users (id, name) values (?, ?)', [2, 'Believ Master']);

Output :

Update Query:

$update = DB::update("update users set name = 'PHPTPOINT' where id = ?", [1]);

Output :

Delete Query:

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

Output :

Drop Query:

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