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 File





Below 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");













Comments

Post a Comment

Popular posts from this blog

Eloquent Many to Many Relationship Tutorial in Laravel 11

Image
  <?php namespace App\Http\Controllers ; use App\Models\ User ; use Illuminate\Http\ Request ; class UserController extends Controller {     /**      * Display a listing of the resource.      */     public function index ()     {         // $users = User::find(2);         // return $users;         // $users = User::with("roles")->get();         // return $users;         // $users = User::find(1);         // return $users->roles;         // $users = User::find(1);         // foreach($users->roles as $data) {         //     echo $data->role_name . "<br>";         // }         // To show All users data as shown below :         $users = User :: get ();...
Read more

Eloquent with JSON Data Columns Tutorial in Laravel 11

Image
  <?php namespace App\Http\Controllers ; use App\Models\ Test ; use Illuminate\Http\ Request ; class TestController extends Controller {     /**      * Display a listing of the resource.      */     public function index ()     {         // Read first record from database         // $test = Test::find(1);         // return $test->meta_data;         // Read all records from database         // $test = Test::get();         // return $test;         // Order all records based on name         // $test = Test::orderBy("meta_data->name")->get();         // return $test;         // Read only "name" value of any particular record from database         // $test = Test::find(1);     ...
Read more

Blade Template Tutorial Three Template Inheritance in Laravel 11

Image
  <! 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 > Master Layout - @yield ( "title" , "Website" ) </ title >     {{-- Below "asset('css/style.css')" is for declaring Absolute Path and "asset() is helper function for Absolute Path" --}}     < link rel = "stylesheet" href = " {{ asset ('css/style.css') }} " > </ head > < body >     < div id = "wrapper" >         < header >             < h1 > Hello World </ h1 >         </ header >         < nav >             < a href = "/welcomeT...
Read more