How to select all column name from a table in laravel?

Question

I have tried to get all column names from a table Teller

Function:

public function getTableColumns($tables)
{
    return DB::select(DB::raw('SELECT 
                                    COLUMN_NAME, 
                                    DATA_TYPE, 
                                    COLUMN_DEFAULT
                                FROM 
                                    INFORMATION_SCHEMA.COLUMNS
                                WHERE 
                                    table_name = `Teller`'));
}

Parvez Rahaman · Accepted Answer · 2019-12-06 12:52:07Z

113

You can get all columns name by simply doing that...

use Illuminate\Support\Facades\Schema;

use Illuminate\Support\Facades\DB;

public function getTableColumns($table)
{
    return DB::getSchemaBuilder()->getColumnListing($table);

    // OR

    return Schema::getColumnListing($table);

}

edited Dec 6, 2019 at 12:52

answered May 11, 2016 at 9:06

Parvez Rahaman

4,3973 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

The problem here when doing joins is that you need to prefix the column names with the table name like: $tableName =...; $columns = Schema::getColumnListing($tableName); $columns = array_map(function ($elem) use ($tableName) {return $tableName . '.' . $elem;}, $columns);

Mamun Sabuj Over a year ago

Excellent! It's working

Tom · Accepted Answer · 2020-03-10 10:46:56Z

26

Get Table Name From Model

$product = new Product;
$table = $product->getTable();
print_r($table);

Get Table Column Name From Model

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{   
    public function getTableColumns() {
        return $this->getConnection()->getSchemaBuilder()->getColumnListing($this->getTable());
    }
}

Now you will get all columns of "products" table and if you need it in controller then you can get it by following way :

 $product = new Product;
 $columns = $product->getTableColumns();
 print_r($columns);

edited Mar 10, 2020 at 10:46

Tom

4,7925 gold badges26 silver badges53 bronze badges

answered Jun 7, 2018 at 7:36

user9888683

Comments

Muhammad Sheraz · Accepted Answer · 2019-04-29 07:30:01Z

8

Just in-case if you have multiple databases connections, try following:

Add in the top of your php script

use Illuminate\Support\Facades\Schema;

Retrieve anywhere in your code

With Database Connection

$columns = Schema::Connection('business')->getColumnListing('users'); // 'business' is your database connection

        echo "<pre>";
        print_r($columns);
        exit();

Without Database Connection

$columns = Schema::getColumnListing('users');

    echo "<pre>";
    print_r($columns);
    exit();

answered Apr 29, 2019 at 7:30

Muhammad Sheraz

7339 silver badges14 bronze badges

Comments

Nikolai Shevchenko · Accepted Answer · 2019-09-11 18:27:21Z

3

You only need extract the keys from the query response

array_keys(json_decode(json_encode($table[0]), true))

edited Sep 11, 2019 at 18:27

Nikolai Shevchenko

7,6278 gold badges36 silver badges47 bronze badges

answered Sep 11, 2019 at 18:02

Eduardo Wallace

595 bronze badges

Comments

ftrotter · Accepted Answer · 2019-10-17 03:34:01Z

As of Laravel 6.x this works:

                    $db = DB::connection()->getPdo();
                    $rs = $db->query('SELECT * FROM Teller LIMIT 0');
                    for ($i = 0; $i < $rs->columnCount(); $i++) {
                            $col = $rs->getColumnMeta($i);
                            $columns[] = $col['name'];
                    }
                    print_r($columns);

The hint here is just to go around eloquent (which should just provide a simple way to do this, but clearly does not) and grab the PDO object and then use the answer from the same question for straight PDO access

This will also work when there is no database selected by replacing 'Teller' with databasename.Teller

HTH,

-ft

PhilWilliammee · Accepted Answer · 2021-02-06 13:32:06Z

3

Another way from a model using collections.

collect(User::first())->keys();

answered Feb 6, 2021 at 13:32

PhilWilliammee

5816 silver badges10 bronze badges

Comments

DAVID AJAYI · Accepted Answer · 2021-05-04 08:00:03Z

2

@Eduardo Wallace method has it simplest; just return the first value from the table, strip off the values leaving the keys

$T1 = table_name::first();
$table_columns = array_keys(json_decode($T1, true));

If you don't need all the columns, strip away unneeded columns, leaving only the ones needed:

$needed_columns = array_diff($table_columns, ['unneeded_1', 'unneeded_2']);

You can argue the method used, simplest to me anyways!

answered May 4, 2021 at 8:00

DAVID AJAYI

2,20424 silver badges15 bronze badges

Comments

Lorenzo Toso · Accepted Answer · 2021-05-05 08:27:11Z

1

$db = [];
$tables =  DB::select('SHOW TABLES');

    foreach ($tables as $key => $table) {
        $name = $table->Tables_in_app;
        $app[$name] =  DB::getSchemaBuilder()->getColumnListing($name);
    }

return $db;

answered May 5, 2021 at 8:27

Lorenzo Toso

112 bronze badges

Comments

toking · Accepted Answer · 2022-01-04 03:01:38Z

0

You can get an idea with this script

    $columns = array();
    foreach(\DB::select("SHOW COLUMNS FROM $table") as $column)
    {
       //print_r($column);
        $columns[$column->Field] = '';
    }
  
    return $columns;

answered Jan 4, 2022 at 3:01

toking

14 bronze badges

Comments

CodeToLife · Accepted Answer · 2022-12-17 09:51:32Z

0

in 12.17.22 it's
extremely simple

$columns=Schema::getColumnListing('my_table_name');
//e.g. your table's name is 'my_table_name' or 'Teller' or   'posts'
// and then
$fetchedSet= Post::select($columns)->where('sender_id','>',100)->get();

And you are done

answered Dec 17, 2022 at 9:51

CodeToLife

4,2592 gold badges50 silver badges34 bronze badges

Comments

Swatantra Kumar · Accepted Answer · 2023-06-13 08:56:44Z

0

$columns = DB::select("SHOW COLUMNS FROM table_name");

//This code will return column array as table defined in database.
return array_column($columns, 'Field');

//This code will return column array asc order of column name.
return Schema::getColumnListing('table_name');

edited Jun 13, 2023 at 8:56

Swatantra Kumar

1,3286 gold badges25 silver badges33 bronze badges

answered Jun 11, 2023 at 7:37

MD HARUNUR RASHID

12 bronze badges

3 Comments

Super Kai - Kazuya Ito Over a year ago

You should add explanation.

MD HARUNUR RASHID Over a year ago

You can get all columns name as you defined in database one after one by simply doing first block code.

MD HARUNUR RASHID Over a year ago

You can get all columns array in asc order by doing 2nd return block code

e sadeghi · Accepted Answer · 2023-07-15 08:05:48Z

0

this is work for me

use Illuminate\Support\Facades\DB;

getTableColumns((new Model())->getTable());    

function getTableColumns($table){
   return DB::getSchemaBuilder()->getColumnListing($table);
}

edited Jul 15, 2023 at 8:05

answered Jul 15, 2023 at 7:42

e sadeghi

431 silver badge4 bronze badges

1 Comment

Yogendra Over a year ago

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review

codedge · Accepted Answer · 2016-05-11 08:51:47Z

-3

You could simply write:

public function getTableColumns($tables)
{
    return DB::select(
        DB::raw('SELECT * FROM `Teller`')
    );
}

If you have a Teller model you can also use Teller::all();

Update

To get all column name you can run SHOW FIELDS Teller

edited May 11, 2016 at 8:51

answered May 11, 2016 at 8:47

codedge

5,1892 gold badges29 silver badges44 bronze badges

2 Comments

DMS-KH Over a year ago

Get Column name not get value in each column

codedge Over a year ago

I updated my answer. But there is no Laravel/Eloquent call for that.

KAPIL KUMAR · Accepted Answer · 2016-05-11 09:07:56Z

-4

You can use this

DB::table('table_name')->get();

answered May 11, 2016 at 9:07

KAPIL KUMAR

91 bronze badge

1 Comment

ftrotter Over a year ago

This is for getting data. The question was for getting column names, which should work even if no data is there.

Collectives™ on Stack Overflow

How to select all column name from a table in laravel?

14 Answers 14

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related