Retrieving data from two tables and to show the data on same page using Laravel

Question

Greetings from the core of my heart! I am using Laravel v-5.6. I am trying to retrieve data from two tables and to show them on same page. I am using two controllers and two models. And the tables has no relation. Note: subcondetails is the view where I want to show the data

1st Controller Name: PostController Using it to retrieve data from DB. Table name is subcontractors Code:

public function index()
{
    $subcontractors = DB::select('select * from subcontractors');
    return view('subcondetails', ['subcontractors' => $subcontractors]);
}

2nd Controller Name: SubemController Using it to retrieve data from DB. Table name is subemployees Code:

public function index()
{
    $subemployees = DB::select('select * from subemployees');
    return view('subcondetails', ['subemployees' => $subemployees]);
}

Now to show the data on same page the following code works fine for first controller

    <thead>
        <tr>
            <th>ID</th>
            <th>COMPANY NAME</th>
    </tr>
    </thead>
    <tbody>
        @foreach($subcontractors as $subcontractor)
            <tr>
                <td>{{ $subcontractor->id }}</td>
                <td>{{ $subcontractor->companyname }}</td>
            </tr>
        @endforeach

But on the same page when I try to load table it gives an error Undefined Variable 'subemployees' Code is as:

        <thead>
          <tr>
              <th>ID</th>
              <th>NAME</th>
        </tr>
      </thead>
    
        <tbody>
          @foreach($subemployees as $subemployee)
              <tr>
                  <td>{{ $subemployee->id }}</td>
                  <td></td>
              </tr>
              @endforeach

My web (routes) file code is as:

    Route::get('/', 'PostController@index');
    Route::resource('posts', 'PostController');

I created second controller 'SubempController' using this code

    php artisan make:controller SubempController --resource

My code from migrations for first table is as:

Schema::create('subcontractors', function (Blueprint $table) {
    $table->increments('id');
    $table->string('branches');
    $table->string('companyname');
    $table->string('companyaddress');
    $table->string('contactperson');
    $table->integer('contactnumber');
    $table->string('contactdepartment');
    $table->string('emailaddress');
    $table->string('invoiceterms');
    $table->text('paymentterms');
    $table->integer('vatregistration')->nullable();
    $table->integer('payrate');
    $table->timestamps();
});

2nd Table:

Schema::create('subemployees', function (Blueprint $table) {
    $table->increments('id');
    $table->string('staff_name');
    $table->integer('contact_number');
    $table->integer('sia_number')->nullable();
    $table->string('sia_type')->nullable();
    $table->date('my_date')->nullable();
    $table->date('my_date2');
    $table->integer('ni_number');
    $table->string('training_name');
    $table->integer('certificate_number');
    $table->string('address1');
    $table->timestamps();
});

I would like to have a solution for it. If you require any information please do let me know.

Answer 1

The problem is that your route is using the the index method at thePostController. It returns the view subcondetails, passing the subcontractors variable, but not the subemployees.

You must query both resources using the same controller:

public function index()
{
    $subemployees = DB::select('select * from subemployees');
    $subcontractors = DB::select('select * from subcontractors');

    return view('subcondetails', [
        'subcontractors' => $subcontractors,
        'subemployees' => $subemployees,
    ]);
}

You can use your Eloquent model to execute the query. The plain SQL may be incompatible if you would like to change your database in the future, and should be avoided as much as possible.

public function index()
{
    $subemployees = Subemployee::all(); //Change to your model name
    $subcontractors = Subcontractor::all(); //Change to your model name

    return view('subcondetails', [
        'subcontractors' => $subcontractors,
        'subemployees' => $subemployees,
    ]);
}