• 5
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

How to fetch the tables list in database in Laravel 5.1

I need to list out the tables in database, I found the query for

SHOW TABLES LIKE  'merTrans%'

get the tables but how could I use the foreach to get the table names in Laravel 5.1?

To list out the tables in database you can do

$tables = DB::select('SHOW TABLES');
foreach($tables as $table)
{
      echo $table->Tables_in_db_name;
}

You'll have to change the db_name to the name of your database.

EDIT : FOR LIKE CASES

foreach ($tables as $table) {
    foreach ($table as $key => $value)
        echo $value;
}
  • 52
Reply Report
    • without like condition it's working fine. But if i add the "LIKE 'merTrans%' " Undefined property: stdClass::$Tables_in_db_name.
      • 1
    • Note, this answer will only work for MySQL. OP is using MySQL based on the syntax, but this will not work, for instance, in an Sqlite3 or Postgresql database.

I've been using this:

$tables = DB::connection()->getDoctrineSchemaManager()->listTableNames();

It requires doctrine/dbal as a dependency. But some migration features already need DBAL to work.

  • 43
Reply Report

To get a quick array containing all databases you can use the following piece of code:

// Iterate over the results of SHOW TABLES
// strip off all the objects and keys.
$tables = array_map('reset', \DB::select('SHOW TABLES'));

To me this seems to be the most elegant solution.

  • 25
Reply Report

For Postgres Users:

SHOW TABLES is not supported so you have to do something a bit more hackey.

$tables = DB::select("SELECT table_schema,table_name, table_catalog FROM information_schema.tables WHERE table_catalog = 'YOUR TABLE CATALOG HERE' AND table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_name;")

Make sure you fill out table_catalog (which I guess is equatable to database). You may have to tweak your results a bit.

  • 10
Reply Report
$tables = \DB::select("SHOW TABLES LIKE 'merTrans%'");
foreach ($tables as $table) {
  echo head($table);
}
  • 5
Reply Report
      • 1
    • Please edit your answer to include some explanation. Code-only answers do very little to educate future SO readers. Your answer is in the moderation queue for being low-quality.
      • 2
    • It's a bit of a hack since Laravel's head helper was supposed to work with arrays and not objects, but it work because it basically calls reset on the parameter called to it...

Because I don't have the reputation to add a comment, yet, I'm posting this as an answer.

A recommendation for Bharat's LIKE CASES

foreach ($tables as $table) {
    echo array_shift(array_values($table));
}

if you don't like having a double foreach, but make sure you var_dump $table,

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

to see exactly what you are dealing with.

  • 2
Reply Report
 protected function getNamesTablesDB(){

        $database = Config::get('database.connections.mysql.database');

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

        $combine = "Tables_in_".$database;

        $collection = new \Illuminate\Database\Eloquent\Collection;

        foreach($tables as $table){
            $collection->put($table->$combine, $table->$combine);
        }

        return $collection; //or compact('collection'); //for combo select
    }
  • 1
Reply Report

Another solution will be, you do not need to use DB name. 'current' will take first column only.

function getTables()
{
    $tables = DB::select('SHOW TABLES');

    $tables = array_map('current',$tables);

    return $tables;
}
  • 1
Reply Report

In case you need to apply some migration changes to all tables.

In Laravel 6 Schema::getAllTables() became a public method, so you can do something like this:

$tables = array_filter(
    Schema::getAllTables(),
    static function ($table) {
        return preg_match('/some_(\d+)_table/', $table->{'Tables_in_' . env('DB_DATABASE')});
    }
);

foreach ($tables as $table ) {
    Schema::table(
        $table->{'Tables_in_' . env('DB_DATABASE')},
        static function (Blueprint $table) {
            $table->removeColumn('request_id');
        }
    );
}

That's relevant when you need to do (in my case above - remove a column) something with tables that have this naming structure: some_1_table, some_2_table, some_3_table, etc. So basically instead of DB::select('SHOW TABLES'); you can now use Schema::getAllTables().

  • 1
Reply Report

I use this to get all tables with columns:

$schema = collect(DB::connection()->getDoctrineSchemaManager()->listTableNames())->map(function ($item, $key) {
  return [
    'name' => $item,
    'colomns' => DB::getSchemaBuilder()->getColumnListing($item)
  ];
});
  • 0
Reply Report

Find the list of tables from your database in "Laravel"

        $tables_list = DB::select('SHOW TABLES');
        
        foreach($tables_listas $value)
        {
              echo $value->Tables_in_YourDatabaseName;
        }

'Tables_in_YourDatabaseName' means : for example if your database name is "test" then

echo $value->Tables_in_test;

  • 0
Reply Report

Add flowing thing in your Laravel project :- In Controller file :-

 public function debate_list(){
         $records = DB::table('table_name')->get();
        return view('page_link')->with('records',$records);
    }

In page_link.blade.php add flowing :-

@foreach($records as $class)
  <tr>
    <td>{{$class->id}}</td>
    <td>
      {{$class->name}}
    </td>
    <td>
      {{$class->image_url}}
    </td>
    <td>
      {{ $class->created_at }}
    </td>

    <td>
      <a class="btn btn-primary btn-sm " href="{{route('image_status_list')}}"> VIEW</a>
    </td>

  </tr>
@endforeach
  • -2
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions