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
No responses yet