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
