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

In Laravel, you can retrieve column names from a database table using several methods. Laravel provides multiple approaches through the Schema facade, Eloquent models, and DB facade to access table structure information.

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(30)   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    |       | 
+------------+-------------+------+-----+---------+-------+

Using the Schema Facade

The Schema facade provides the getColumnListing() method to retrieve all column names from a table. This is the most straightforward approach

<?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);
   }
}
?>

The output of the above code is

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

Using Eloquent Model

You can also retrieve column names through an Eloquent model by fetching a record and extracting its attributes. First, create the Student model

<?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);
   }
}
?>

The output of the above code is

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

Using DB Facade with Query Builder

You can use the DB facade to fetch records and extract column names from the result

<?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);
   }
}
?>

The output of the above code is

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

Using SHOW COLUMNS Query

Another approach is using the SHOW COLUMNS SQL command through DB facade

<?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);
   }
}
?>

The output of the above code is

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

Conclusion

The Schema facade's getColumnListing() method is the most efficient and Laravelnative approach for retrieving column names. Use Eloquent models when you need additional table data, and DB facade methods for more complex queries or when working with raw SQL.