QUERY BUILDER & RAW QUERIES IN LARAVEL

Query Builder & Raw SQL Queries

ELOQUENT ORM LARAVEL

RAW QUERIES IN LARAVEL

As we all know that business logic is often complicated, because when we try to extract the data from multiple tables then we have to face lots of problems by using eloquent ORM, Because of this, we often need to write our own SQL Queries called as RAW Queries.

In this blog, we are working with RAW QUERIES of Query Builder.

STEP 1

Configure Your “ENV” File With Your Database Details.

STEP 2

• Add this namespace by using below line,
• use Illuminate\Support\Facades\DB;
• We have to use DB class which is present in above namespace.
• Because DB class provides features of Query Builder / Raw Queries.

STEP 3

Create Database & Table Using PHPMyAdmin.

STEP 4

Create a method in your controller class and add your SQL query by using DB class.

Database Table Query

CREATE TABLE employees (
  id int primary key auto_increment,
  name varchar(191) NOT NULL,
  gender varchar(191) NOT NULL,
  age int(11) NOT NULL,
  designation varchar(191)  NOT NULL,
  created_at datetime NOT NULL
);

INSERT INTO `employees` (`id`, `name`, `gender`, `age`, `designation`, `created_at`) VALUES
(1, 'Asghar', 'Male', 23, 'Manager', '2019-02-22 12:45:23'),
(2, 'Nimra', 'Female', 21, 'Operator', '2021-05-12 08:22:11'),
(3, 'Farah', 'Female', 22, 'Incharge', '2015-09-17 09:45:56'),
(4, 'Zain', 'Male', 21, 'P-A', '2019-01-11 10:09:45'),
(5, 'Usman', 'Male', 24, 'Mobile App Developer', '2022-01-22 04:23:12'),
(6, 'Humaira', 'Female', 32, 'Owner', '2022-06-13 12:45:23'),
(12, 'Atif', 'Male', 24, 'Operator', '2020-05-13 11:22:33'),
(13, 'Muskan', 'Female', 24, 'Manager', '2018-01-26 11:56:23'),
(15, 'Adeel', 'Male', 26, 'IT Specialist', '2022-06-14 02:23:12'),
(16, 'Fatima', 'Female', 22, 'IT Incharge', '2021-02-13 01:23:12');

Complete Source Code

HomeController Source Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class HomeController extends Controller
{
    public function getData()
    {
        // $data = DB::select('select * from employees where id = 3');
        // $data = DB::select('select * from employees where id = ?',[2]);
        // name binding parameter :id
        // $data = DB::select('select * from employees where id = :id',['id'=>'5']);
        $data = DB::select('select * from employees');
        echo '<pre>';
        print_r($data);
    }
    public function insertData()
    {
        $data = DB::insert('insert into employees (name,gender,age,designation) values(?,?,?,?)',['Muskan','Female','24','Manager']);
        return $data;
    }
    public function updateData()
    {
        // $data = DB::update('update employees set name = ? where id = ?',['Adil','5']);
        $data = DB::update('update employees set name = ?, age = ? where id = ?',['Humaira','32','6']);
        return $data;
    }
    public function deleteData()
    {
        // $data = DB::update('update employees set name = ? where id = ?',['Adil','5']);
        $data = DB::delete('delete from employees where id = ?',['7']);
        return $data;
    }
}

ENV File Source Code

APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:mDH2tKBxhWp3DV6NhZE5r361pcyzBEPRg1EdBDF4n6I=
APP_DEBUG=true
APP_URL=http://RawQueries.test

LOG_CHANNEL=stack
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=prac2
DB_USERNAME=root
DB_PASSWORD=

BROADCAST_DRIVER=log
CACHE_DRIVER=file
FILESYSTEM_DRIVER=local
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

MEMCACHED_HOST=127.0.0.1

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=smtp
MAIL_HOST=mailhog
MAIL_PORT=1025
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_ADDRESS=null
MAIL_FROM_NAME="${APP_NAME}"

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=
AWS_USE_PATH_STYLE_ENDPOINT=false

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_APP_CLUSTER=mt1

MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"

Web.php inside routes folder – Source Code

<?php

use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});
Route::get('getData','App\Http\Controllers\HomeController@getData');
Route::get('insertData','App\Http\Controllers\HomeController@insertData');
Route::get('updateData','App\Http\Controllers\HomeController@updateData');
Route::get('deleteData','App\Http\Controllers\HomeController@deleteData');

Click Below Link to Download Notes & Source Code Of This Blog

https://www.mediafire.com/file/d0w3ngw0dpdj9bx/RAW+QUERIES.rar/file