Laravel Get Data From Multiple Tables

Laravel Get Data From Multiple Tables

Laravel get/fetch data from multiple tables; In this tutorial, you will learn how to get/fetch data from multiple tables and display it in laravel apps.

How to Fetch/Get Data From Multiple Tables in Laravel

Use the following steps to get/fetch data from multiple tables and display it in laravel apps.

  • Step 1 – Download Laravel Application
  • Step 2 – Configure Database with App
  • Step 3 – Create Model Class File
  • Step 4 – Create Routes
  • Step 5 – Create Controller By Artisan Command
  • Step 6 – Create Blade View File
  • Step 7 – Run Development Server

Step 1 – Download Laravel Application

First of all download or install laravel new setup. So, open terminal and type the following command to install new laravel app into your machine:

composer create-project --prefer-dist laravel/laravel blog

Step 2 – Configure Database with App

In this step, set up a database with your downloaded/installed laravel app. So, you need to find .env file and setup database details as follows:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database-name
DB_USERNAME=database-user-name
DB_PASSWORD=database-password

After that, execute the following sql queries to create tables in database; is as follows:

--
-- Table structure for table `city`
--

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `city`
--

INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'New York city'),
(2, 1, 'Buffalo'),
(3, 1, 'Albany'),
(4, 2, 'Birmingham'),
(5, 2, 'Montgomery'),
(6, 2, 'Huntsville'),
(7, 3, 'Los Angeles'),
(8, 3, 'San Francisco'),
(9, 3, 'San Diego'),
(10, 4, 'Toronto'),
(11, 4, 'Ottawa'),
(12, 5, 'Vancouver'),
(13, 5, 'Victoria'),
(14, 6, 'Sydney'),
(15, 6, 'Newcastle'),
(16, 7, 'City of Brisbane'),
(17, 7, 'Gold Coast'),
(18, 8, 'Bangalore'),
(19, 8, 'Mangalore'),
(20, 9, 'Hydrabad'),
(21, 9, 'Warangal');

-- --------------------------------------------------------

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'USA'),
(2, 'Canada'),
(3, 'Australia'),
(4, 'India');

-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'New York'),
(2, 1, 'Alabama'),
(3, 1, 'California'),
(4, 2, 'Ontario'),
(5, 2, 'British Columbia'),
(6, 3, 'New South Wales'),
(7, 3, 'Queensland'),
(8, 4, 'Karnataka'),
(9, 4, 'Telangana');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `city`
--
ALTER TABLE `city`
  ADD PRIMARY KEY (`city_id`);

--
-- Indexes for table `country`
--
ALTER TABLE `country`
  ADD PRIMARY KEY (`country_id`);

--
-- Indexes for table `state`
--
ALTER TABLE `state`
  ADD PRIMARY KEY (`state_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `city`
--
ALTER TABLE `city`
  MODIFY `city_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `country`
--
ALTER TABLE `country`
  MODIFY `country_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `state`
--
ALTER TABLE `state`
  MODIFY `state_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

Step 3 – Create Model Class File

In this step, open again your command prompt. And run the following command on it. To create model and migration file for form:

php artisan make:model Country

After that, open the create_contacts_table.php file inside FormValidation/database/migrations/ directory. And the update the function up() with the following code:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Country extends Model
{
    use HasFactory;

    protected $table = 'country';

    protected $fillable = [
    	'country_name'
    ];
}

Then, open again command prompt and run the following command to create tables in database:

php artisan migrate

Step 4 – Create Routes

In this step, open web.php file from the routes directory. And update the following routes into web.php file:

use Illuminate\Support\Facades\Route;

use App\Http\Controllers\JointableController;

Route::get('/', function () {
    return view('welcome');
});

Route::get('join_multiple_table', [JoinMultipleTableController::class, 'index']);

Step 5 – Create Controller By Artisan Command

In this step, run the following command on the command prompt to create a controller file:

php artisan make:controller JoinMultipleTableController

After that, go to app/http/controllers and open AjaxContactController.php file. And update the following code into it:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Models\Country;

class JoinMultipleTableController extends Controller
{
    function index()
    {
    	$data = Country::join('state', 'state.country_id', '=', 'country.country_id')
              		->join('city', 'city.state_id', '=', 'state.state_id')
              		->get(['country.country_name', 'state.state_name', 'city.city_name']);

        return view('join_multiple_table', compact('data'));
    }
}

?>

Step 6 – Create Blade View File

Now, Go to resources/views and create join_multiple_table.blade.php. And update the following code into join_multiple_table.blade.php file:

<html>
    <head>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>How to Join Multiple Table in Laravel using Eloquent Model</title>
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">    
            <br />
            <h1 class="text-center text-primary">How to Join Multiple Table in Laravel 8 using Eloquent Model</h1>
            <br />
            <div class="table-responsive">
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>Country</th>
                            <th>State</th>
                            <th>City</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach($data as $row)
                            <tr>
                                <td>{{ $row->country_name }}</td>
                                <td>{{ $row->state_name }}</td>
                                <td>{{ $row->city_name }}</td>
                            </tr>
                        @endforeach
                    </tbody>
                </table>
            </div>
        </div>
    </body>
</html>

Step 7 – Run Development Server

Last step, open command prompt and run the following command to start developement server:

php artisan serve

Then open your browser and hit the following url on it:

http://127.0.0.1:8000/join_multiple_table

Recommended Laravel Tutorials

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *