Working with Database in Laravel: Getting Started

Hey welcome guys and today i’m going to show you how you can work with laravel database using simple SQL statement.

Laravel makes interacting with databases extremely simple across a variety of database backends using either Raw SQL , the fluent Query builder, and the Eloquent ORM.

Currently, Laravel supports four databases:

  • MySQL
  • PostgreSQL
  • SQLite
  • SQL Server

How you configure your DB in laravel?

Before you start writing query, first you have to configure the DB with application.
So there is 2 different way to configure DB in laravel.

  • The database configuration for your application is located at project’s .env file in root folder, you can add your DB_NAME, DB_USERNAME, PASWORD in that file and save your details there.
  • And another one is configure at config/database.php. In this file you may define all of your database connections, as well as specify which connection should be used by default.

How to use multiple DB in laravel application:

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

$users = DB::connection('foo')->select('select * from users'); // Fetch all users from table

Running raw SQL query

If you want to use your simple Structure Query Language (SQL) query in laravel instead of eloquent and query builder then it’s very easy to use that in laravel.

you can read next line for that how you can use that simple Select, Insert, Update and Delete queries are used in Laravel application.

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

Running A Select Query

To run a basic query, you may use the select method on the DB facade:

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /* Show a list of all of the users in application. */
    public function index(){
        $users = DB::select('select * from users where email = ?', [1]);
        return view('user.index', ['users' => $users]);
    }
}

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

The select method will always return an array of results. Each result within the array will be a PHP stdClass object, allowing you to access

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

Running An Insert Statement

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

DB::insert('insert into users (id, name) values (?, ?)', [7, 'Ecodeblog']);

Running An Update Statement

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

$affected = DB::update('update users set votes = 100000 where name = ?', ['Narendra Modi']);

Running A Delete Statement

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

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

// $deleted return affected number of rows count.

Running A General Statement

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

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

So like these all select(), insert(), update() and delete() statements in Raw query you can written your simple SQL statement and fetch the rows from DB and do many more operations in DB.

Next time I’ll come with the most elegant laravel query writing method:
1. Query Builder
2. Eloquent ORM

I hope this will helps you while you want to use SQL statement in laravel.

Share

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *