বাংলায় PHP LARAVEL FRAMEWORK পর্ব-১১: Database selects, joins, Unions with Query Builder

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
        $students = DB::table('students')->select('name', 'email as student_email')->get();
        $table="<table border='1' width='300'";
        $table.="<tr><th>Name</th><th>Email</th></tr>";
        foreach($students as $student){
 
        $table.="<tr><td>$student->name</td><td>$student->student_email</td></tr>";
                 
        }
        $table.="</table>";
        echo $table;
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
     
        $students = DB::table('students')->distinct()->get(["name"]);
        foreach($students as $student){
            echo $student->name,"<br>";
        }
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
     
        $query = DB::table('students')->select('name');
        $students = $query->addSelect('email')->get();
 
        $table="<table border='1' width='300'";
        $table.="<tr><th>Name</th><th>Email</th></tr>";
        foreach($students as $student){
 
        $table.="<tr><td>$student->name</td><td>$student->email</td></tr>";
                 
        }
        $table.="</table>";
        echo $table;
    }
}

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jan 07, 2018 at 06:24 AM
-- Server version: 10.1.24-MariaDB
-- PHP Version: 7.1.6
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
 
--
-- Database: `laravel`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `contacts`
--
 
CREATE TABLE `contacts` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `contacts`
--
 
INSERT INTO `contacts` (`id`, `student_id`, `email`, `phone`) VALUES
(1, 1, 'sa@mail.com', '01788'),
(2, 2, 'cs@mail.com', '8339922'),
(3, 3, 'ss@mail.com', '01678'),
(4, 4, 'ps@mail.com', '099228'),
(5, 5, 'as@mail.com', '01578899');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `courses`
--
 
CREATE TABLE `courses` (
  `id` smallint(5) UNSIGNED NOT NULL,
  `course_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `courses`
--
 
INSERT INTO `courses` (`id`, `course_name`) VALUES
(1, 'PHP & MySQL for Beginner'),
(2, 'ZCPE (PHP Advance Course)'),
(3, 'Laravel 5.5 Beginning to Advance'),
(4, 'JavaScript and Jquery Advance');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `course_choice`
--
 
CREATE TABLE `course_choice` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `course_choice`
--
 
INSERT INTO `course_choice` (`id`, `student_id`, `course_id`) VALUES
(1, 1, 1),
(2, 2, 3),
(3, 2, 2),
(4, 3, 1),
(5, 4, 2);
 
-- --------------------------------------------------------
 
--
-- Table structure for table `students`
--
 
CREATE TABLE `students` (
  `id` smallint(5) UNSIGNED NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `students`
--
 
INSERT INTO `students` (`id`, `name`) VALUES
(1, 'Novel'),
(2, 'Sahab'),
(3, 'Sohag'),
(4, 'Khan'),
(5, 'kopil'),
(6, 'Russel'),
(7, 'siddik');
 
--
-- Indexes for dumped tables
--
 
--
-- Indexes for table `contacts`
--
ALTER TABLE `contacts`
  ADD PRIMARY KEY (`id`);
 
--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`);
 
--
-- Indexes for table `course_choice`
--
ALTER TABLE `course_choice`
  ADD PRIMARY KEY (`id`);
 
--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`);
 
--
-- AUTO_INCREMENT for dumped tables
--
 
--
-- AUTO_INCREMENT for table `contacts`
--
ALTER TABLE `contacts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `course_choice`
--
ALTER TABLE `course_choice`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;COMMIT;

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
     
        $students = DB::table('students')
            ->join('contacts', 'students.id', '=', 'contacts.student_id')
            ->join('course_choice', 'students.id', '=', 'course_choice.student_id')
            ->join('courses', 'students.id', '=', 'course_choice.student_id')
            ->select('students.id','students.name','courses.course_name','contacts.phone','contacts.email')
            ->get();
 
            echo "<pre>";
            print_r($students);
            echo "</pre>";
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
     
        $students = DB::table('students')
            ->leftJoin('contacts', 'students.id', '=', 'contacts.student_id')
            ->select('students.id','students.name','contacts.phone','contacts.email')
            ->get();
 
            echo "<pre>";
            print_r($students);
            echo "</pre>";
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
     
        $students = DB::table('students')
            ->crossJoin('courses')
            ->get();
 
            echo "<pre>";
            print_r($students);
            echo "</pre>";
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
         
        $students=DB::table('students')
        ->join('contacts', function ($join) {
            $join->on('students.id', '=', 'contacts.student_id')
                 ->where('contacts.student_id', '>', 3);
        })
        ->get();
 
            echo "<pre>";
            print_r($students);
            echo "</pre>";
    }
}

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
 
class getqueryController extends Controller
{
    public function index(){
         
        $first = DB::table('contacts')
            ->whereNull('phone');
 
        $students = DB::table('contacts')
            ->whereNull('email')
            ->union($first)
            ->get();
 
            echo "<pre>";
            print_r($students);
            echo "</pre>";
    }
}