Query Builder tutorial in Laravel 11
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string("name", 30);
$table->string("email", 191)->unique();
$table->integer("age");
$table->string("city");
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('users');
}
};
Above File is database/migrations/create_users_table.php FileBelow File is App\Http\Controllers\UserController.php File
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
public function showUsers()
{
// $users = DB::table("users")->get();
// $users = DB::table("users")->where("id", 4)->get();
// $users = DB::table("users")->select("name","email as User_email")->get();
// $users = DB::table("users")->select("city")->distinct()->get();
// $users = DB::table("users")->pluck("name"); //pluck() method returns Array
// $users = DB::table("users")->pluck("name", "email"); //here "email" will make "key" and "name" will make its "value"
// $users = DB::table("users")->where("city", "Mumbai")->get();
// $users = DB::table("users")->where("city", "Goa")->where("age",">",20)->get();
// $users = DB::table("users")->where("city", "Goa")->where("age","!=",20)->get();
// $users = DB::table("users")->where("name", "like", "S%")->get();
// $users = DB::table("users")->where([["city","=","Mumbai"],["age",">",20]])->get();
// $users = DB::table("users")->where("city","=","Mumbai")->orWhere("age","<","18")->get();
// $users = DB::table("users")->whereBetween("id", [3, 7])->get();
// $users = DB::table("users")->whereBetween("age", [18, 23])->get();
// $users = DB::table("users")->whereNotBetween("age", [18, 32])->get();
// $users = DB::table("users")->whereIn("id", [1, 3, 6, 7])->get();
// $users = DB::table("users")->whereIn("city", ["Goa", "Mumbai"])->get();
// $users = DB::table("users")->whereNotIn("city", ["Goa", "Mumbai", "Ahmedabad"])->get();
// $users = DB::table("users")->whereNull("email")->get();
// $users = DB::table("users")->whereNotNull("email")->get();
// $users = DB::table("users")->whereDate("created_at", "2024-11-12")->get();
// $users = DB::table("users")->whereMonth("created_at", "11")->get();
// $users = DB::table("users")->whereMonth("created_at", "12")->get();
// $users = DB::table("users")->whereDay("created_at", "12")->get();
// $users = DB::table("users")->whereYear("created_at", "2024")->get();
// $users = DB::table("users")->whereTime("created_at", "12:17:26")->get();
// $users = DB::table("users")->orderBy("name")->get();
// $users = DB::table("users")->orderBy("age")->get();
// $users = DB::table("users")->orderBy("age", "desc")->get();
// $users = DB::table("users")->orderBy("name", "asc")->orderBy("age", "desc")->get();
// $users = DB::table("users")->first(); //first() method returns single Array
// $users = DB::table("users")->latest()->first();
// $users = DB::table("users")->oldest()->first();
// $users = DB::table("users")->inRandomOrder()->first();
// $users = DB::table("users")->limit(3)->get();
// $users = DB::table("users")->limit(3)->offset(6)->get();
// $users = DB::table("users")->take(3)->skip(6)->get();
// $users = DB::table("users")->count();
// min() and max() works with only integer or float data type of column
// $users = DB::table("users")->max("age");
// $users = DB::table("users")->min("age");
// $users = DB::table("users")->avg("age");
$users = DB::table("users")->sum("age");
return $users;
// return view("allusers", ["data" => $users]);
// dd($users); //To test the data, dd() method (of Laravel) is used
// dump($users); //To test the data, dump() method (of Laravel) is used
// Difference between dd() and dump() is that in case of dd(), the code after dd() will not execute
// and in case of dump(), the code after dump() will execute
// foreach($users as $data) {
// echo $data->name . "<br>";
// }
// return view("allusers", ["data" => $users]);
}
public function singleUser(string $id)
{
$user = DB::table("users")->where("id", $id)->get();
return view("user", ["data" => $user]);
}
}
Below File is resources\views\allusers.blade.php File
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>All Users</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-6">
<h1>All Users List</h1>
<table class="table table-bordered table-striped">
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Age</th>
<th>City</th>
<th>View</th>
</tr>
@foreach($data as $id => $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->name }}</td>
<td>{{ $user->email }}</td>
<td>{{ $user->age }}</td>
<td>{{ $user->city }}</td>
<td><a href="{{ route('view.user', $user->id) }}" class="btn btn-primary btn-sm">View</a></td>
</tr>
@endforeach
</table>
</div>
</div>
</div>
</body>
</html>
Below File is resources\views\user.blade.php File
<h1>User Detail</h1>
@foreach($data as $id => $user)
<h3>Name : {{ $user->name }}</h3>
<h3>Email : {{ $user->email }}</h3>
<h3>Age : {{ $user->age }}</h3>
<h3>City : {{ $user->city }}</h3>
@endforeach
Below File is routes\web.php File
<?php
use App\Http\Controllers\UserController;
use Illuminate\Support\Facades\Route;
Route::get('/', function () {
return view('welcome');
});
Route::get("/showAllUsers", [UserController::class, "showUsers"]);
Route::get("/user/{id}", [UserController::class, "singleUser"])->name("view.user");
Below File is database\json\users.json File
[
{
"name": "Ajaypal Sharma",
"email": "ajaypalsharma@gmail.com",
"age": 20,
"city": "Delhi"
},
{
"name": "Vinod Patel",
"email": "vinodpatel@gmail.com",
"age": 21,
"city": "Mumbai"
},
{
"name": "Santosh Patel",
"email": "santoshpatel@gmail.com",
"age": 18,
"city": "Goa"
},
{
"name": "Haresh Patel",
"email": "hareshpatel@gmail.com",
"age": 17,
"city": "Delhi"
},
{
"name": "Lalit Patel",
"email": "lalitpatel@gmail.com",
"age": 17,
"city": "Chandigarh"
},
{
"name": "Amir Khan",
"email": "amirkhan@gmail.com",
"age": 19,
"city": "Chennai"
},
{
"name": "Hrithik Roshan",
"email": "hrithik@gmail.com",
"age": 20,
"city": "Mumbai"
},
{
"name": "Akash Kumar",
"email": "akash@gmail.com",
"age": 21,
"city": "Goa"
},
{
"name": "Akshay Kumar",
"email": "akshay@gmail.com",
"age": 30,
"city": "Mumbai"
},
{
"name": "Shivang Patel",
"email": "shivang@gmail.com",
"age": 25,
"city": "Ahmedabad"
},
{
"name": "Shujal Dave",
"email": "sujaldave@gmail.com",
"age": 27,
"city": "Jaipur"
},
{
"name": "Harshida Patel",
"email": "harshida@gmail.com",
"age": 44,
"city": "Ahmedabad"
},
{
"name": "Mehul Patel",
"email": "mehulpatel@gmail.com",
"age": 50,
"city": "Pune"
},
{
"name": "Hardik Patel",
"email": "hardikpatel@gmail.com",
"age": 33,
"city": "Ahmedabad"
},
{
"name": "Pritam Patel",
"email": "pritampatel@gmail.com",
"age": 39,
"city": "Ahmedabad"
},
{
"name": "Manish Patel",
"email": "manishpatel@gmail.com",
"age": 36,
"city": "Ahmedabad"
},
{
"name": "Chanda Patel",
"email": "chandapatel@gmail.com",
"age": 60,
"city": "Pune"
},
{
"name": "Darshan Patel",
"email": "darshanpatel@gmail.com",
"age": 35,
"city": "Ahmedabad"
},
{
"name": "Hardi Patel",
"email": "hardipatel@gmail.com",
"age": 32,
"city": "Bangalore"
},
{
"name": "Chandresh Patel",
"email": "chandreshpatel@gmail.com",
"age": 35,
"city": "Ahmedabad"
},
{
"name": "Zensi Korat",
"email": "zensikorat@gmail.com",
"age": 15,
"city": "Goa"
},
{
"name": "Shubhash Mudaliyar",
"email": "shubhashmudaliyar@gmail.com",
"age": 24,
"city": "Chennai"
},
{
"name": "Yatrik Patel",
"email": "yatrikpatel@gmail.com",
"age": 24,
"city": "Kalol"
},
{
"name": "Kirtan Patel",
"email": "kirtanpatel@gmail.com",
"age": 19,
"city": "Kalol"
},
{
"name": "Aadity Patel",
"email": "aaditypatel@gmail.com",
"age": 28,
"city": "Ahmedabad"
},
{
"name": "Charmi Patel",
"email": "charmipatel@gmail.com",
"age": 24,
"city": "Kalol"
},
{
"name": "Om Patel",
"email": "ompatel@gmail.com",
"age": 25,
"city": "Kalol"
},
{
"name": "Jinal Patel",
"email": "jinalpatel@gmail.com",
"age": 27,
"city": "Kalol"
}
]
Below File is app\Models\users.php File
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class users extends Model
{
use HasFactory;
protected $table = "users";
protected $primarykey = "id";
protected $fillable = [
"name",
"email",
"age",
"city"
];
}
Below File is database\seeders\UserSeeder.php File
<?php
namespace Database\Seeders;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\File;
use App\Models\users;
class UserSeeder extends Seeder
{
/**
* Run the database seeds.
*/
public function run(): void
{
$json = File::get(path: "database/json/users.json");
$users = collect(json_decode($json));
$users->each(function ($user) {
users::create([
"name" => $user->name,
"email" => $user->email,
"age" => $user->age,
"city" => $user->city
]);
// print_r($user->name);
});
}
}
Below File is database\seeders\DatabaseSeeder.php File
<?php
namespace Database\Seeders;
use App\Models\User;
// use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*/
public function run(): void
{
// User::factory(10)->create();
// User::factory()->create([
// 'name' => 'Test User',
// 'email' => 'test@example.com',
// ]);
$this->call([
UserSeeder::class
]);
}
}
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
Comments
Post a Comment