How to select all the column names from a table in Laravel?

Here are different ways to get the column names from a table in Laravel.

Assume we have created a table named Students in MySQL database using the following query −

CREATE TABLE students(
   id            INTEGER       NOT NULL    PRIMARY KEY,
   name          VARCHAR(10)   NOT NULL,
   email         VARCHAR(15)   NOT NULL,
   created_at    VARCHAR(27)   NOT NULL,
   updated_at    VARCHAR(27)   NOT NULL,
   address       VARCHAR(3)    NOT NULL
);

You can get the complete details of it using the DESC command −

mysql> desc students; 
+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int         | NO   | PRI | NULL    |       | 
| name       | varchar(15) | NO   |     | NULL    |       | 
| email      | varchar(20) | NO   |     | NULL    |       | 
| created_at | varchar(27) | YES  |     | NULL    |       | 
| updated_at | varchar(27) | YES  |     | NULL    |       | 
| address    | varchar(30) | NO   |     | NULL    |       | 
| age        | int         | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
7 rows in set (0.08 sec)

Using the Schema class

Using schema class, you can create, and update tables in Laravel. To work with the schema class, add the following class to your controller.

use Illuminate\Support\Facades\Schema;

Example

Here is a working example using schema class −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Schema;

class StudentController extends Controller {
   public function index() {
      $columns = Schema::getColumnListing('students');
      print_r($columns);
   }
}

Output

The output of the above code is as follows.

Array(
   [0] => id
   [1] => name
   [2] => email
   [3] => created_at
   [4] => updated_at
   [5] => address
)

Using the Model class

The Model class in Laravel represents the table in the database. For example, if you have table students, the model name will be student, for users, it will be user, for employees, it will be “employee”. The table name has to be plural and the model name has to be singular. This is a pattern followed, but that does not stop you from using the naming convention of your choice for table name and model name.

Create model for student as shown below -

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Student extends Model {
   use HasFactory;
   protected $fillable = ['name','email','address'];
}

Now use the model inside your controller to get the column names −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = Student::first();
      $table_columns = array_keys(json_decode($student, true));
      print_r($table_columns);
   }
}

Output

The output of the above code is.

Array(
   [0] => id
   [1] => name
   [2] => email
   [3] => created_at
   [4] => updated_at
   [5] => address
)

Example

You can also make use of the DB facade class as shown inside your controller −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;

class StudentController extends Controller {
   public function index() {
      $table = DB::table('students')->get();
      $cols = array_keys(json_decode(json_encode($table[0]), true));
      print_r($cols);
   }
}

Output

The output of the above code is −

Array ( 
   [0] => id 
   [1] => name 
   [2] => email 
   [3] => created_at 
   [4] => updated_at 
   [5] => address
)

Example

Another way to get columns using DB facade is −

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;

class StudentController extends Controller {
   public function index() {
      $columns_names = [];
      $tableDet = DB::select("SHOW COLUMNS FROM students");
      foreach($tableDet as $column) {
         $columns_names[$column->Field] = '';
      }
      print_r($columns_names);
   }
}

Output

The output of the above code is.

Array ( 
   [id] => 
   [name] => 
   [email] => 
   [created_at] => 
   [updated_at] => 
   [address] => 
)