Query Builder - Insert Update Delete 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]);
}
public function addUser()
{
// insertOrIgnore() method will prevent to insert duplicate entry
// insertOrIgnore() method will first check Unique column(Record OR Value) whether the particular
// record is exists in database OR Not, if exists then it will prevent and
// show given message and if not exists then it will insert new record in database
// $user = DB::table("users")->insertOrIgnore([
// [
// "name" => "Ravi Kumar",
// "email" => "ravikumar@gmail.com",
// "age" => 12,
// "city" => "Chennai",
// ],
// [
// "name" => "Shyam Kumar",
// "email" => "shyamkumar@gmail.com",
// "age" => 18,
// "city" => "Hyderabad",
// ]
// ]);
// upsert() method first check Unique Column and its Value. If that Unique Column value
// exists in database then it will update the record in database and if that Unique Column
// value not exists in database then it will insert new record in database
// $user = DB::table("users")->upsert(
// [
// "name" => "Ravi Kumar",
// "email" => "abc@gmail.com",
// "age" => 10,
// "city" => "Goa"
// ],
// ["email"]
// );
// upsert() method first check Unique Column and its value. If Unique Column value exists in database
// and if we pass third parameter(in upsert() method as shown below) of Column name then it will only
// update that particular Column value. It will not update any other column value as shown below :
// $user = DB::table("users")->upsert(
// [
// "name" => "Ravi Kumar",
// "email" => "abc@gmail.com",
// "age" => 11,
// "city" => "Mumbai"
// ],
// ["email"],
// ["city"]
// );
// if ($user) {
// echo "<h1>Data Successfully Added.</h1>";
// } else {
// echo "<h1>Data not Added.</h1>";
// }
// insertGetId() method will return id of newly inserted record in database
// insertGetId() method will work only when there is Auto_incremented and "id" named
// Column is present in database. If we rename column from "id" to "anything" then
// insertGetId() method will not work
// $user = DB::table("users")->insertGetId(
// [
// "name" => "Anil Kapoor",
// "email" => "anilkapoor@gmail.com",
// "age" => 50,
// "city" => "Mumbai"
// ]
// );
// return $user;
}
public function updateUser()
{
// $user = DB::table("users")->where("id", 12)->update(
// [
// "city" => "Hyderabad"
// ]
// );
// $user = DB::table("users")->where("id", 15)->update(
// [
// "city" => "Delhi",
// "age" => 13
// ]
// );
// updateOrInsert() method will first check whether given value is present in database or not,
// if present then it will update the record according we have passed the value in second
// Array in updateOrInsert() method and if record is not present then it will insert new record
// $user = DB::table("users")->updateOrInsert(
// [
// "name" => "Darshan Patel",
// "email" => "darshanpatel@gmail.com"
// ],
// [
// "age" => 36
// ]
// );
// Here below updateOrInsert() method will insert new record in database because record is not present in database
// $user = DB::table("users")->updateOrInsert(
// [
// "name" => "XYZ",
// "email" => "xyz@gmail.com",
// "city" => "Delhi"
// ],
// [
// "age" => 5
// ]
// );
// if ($user) {
// echo "<h1>Data Updated Successfully</h1>";
// } else {
// echo "<h1>Data Not Updated.</h1>";
// }
// increment() method will increment the value of given Column in increment() method
// increment() method will work only with integer data type Column
// $user = DB::table("users")->where("id", 27)->increment("age");
// Here below index 27, age will increment 5 in (current value + 5)
// $user = DB::table("users")->where("id", 27)->increment("age", 5);
// decrement() method will decrement the value of given Column in decrement() method
// decrement() method will work only with integer data type Column
// $user = DB::table("users")->where("id", 27)->decrement("age");
// Here below index 27, age will decrement 3 in (current value - 3)
// $user = DB::table("users")->where("id", 27)->decrement("age", 3);
// If we want to change another column value along with increment and decrement as shown below :
// $user = DB::table("users")->where("id", 27)->decrement("age", 2, ["city" => "Gandhinagar"]);
// If we want to increment OR decrement all integer column values simultaneously as shown below :
// $user = DB::table("users")->where("id", 27)->incrementEach(
// [
// "age" => 2,
// "votes" => 1 //Suppose we have another integer data type column named "votes"
// ]
// );
// $user = DB::table("users")->where("id", 27)->decrementEach(
// [
// "age" => 2,
// "votes" => 1 //Suppose we have another integer data type column named "votes"
// ]
// );
// if($user) {
// echo "<h1>Data Modified Successfully.</h1>";
// } else {
// echo "<h1>Data not Modified.</h1>";
// }
}
// public function deleteUser()
// {
// $user = DB::table("users")->where("id", 33)->delete();
// if ($user) {
// echo "<h1>Data Deleted Successfully.</h1>";
// } else {
// echo "<h1>Data Not Deleted.</h1>";
// }
// }
// Here below, user can delete record by passing id value in URL
public function deleteUser(string $id)
{
$user = DB::table("users")->where("id", $id)->delete();
if ($user) {
return redirect()->route("home.user");
} else {
echo "<h1>Data Not Deleted.</h1>";
}
}
}
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>
<th>Delete</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>
<td><a href="{{ route('delete.user', $user->id) }}" class="btn btn-danger btn-sm">Delete</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"])->name("home.user");
Route::get("/user/{id}", [UserController::class, "singleUser"])->name("view.user");
Route::get("/add", [UserController::class, "addUser"]);
Route::get("/update", [UserController::class, "updateUser"]);
Route::get("/delete/{id}", [UserController::class, "deleteUser"])->name("delete.user");
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
.png)
Comments
Post a Comment