Get table column names in MySQL?

Get table column names in MySQL?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Retrieving MySQL Column Names in PHP

Retrieving column names for a specific table is a fundamental operation when dynamically inspecting database schemas or building introspection tools within an application. While modern frameworks often abstract this away, situations arise where you need direct access to the metadata without relying on schema migrations alone. The most reliable approach involves querying the information_schema system tables, which are standard across MySQL installations.

The primary method utilizes the COLUMNS table within the information_schema database. You can execute a straightforward SQL query to fetch the column names along with their data types and nullability constraints. This approach is robust because it returns data directly from the server's metadata engine rather than inferring structure from a single row, which might miss auto-increment fields or columns defined in default values if not handled carefully.

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'your_table_name';

In this query, TABLE_SCHEMA refers to the database name where the table resides, and TABLE_NAME is the specific table you are inspecting. The result set provides the actual column names in the COLUMN_NAME field, alongside critical details like DATA_TYPE. This extra information is often necessary for dynamic form generation or validation logic, ensuring that your application understands not just what fields exist, but how they are structured.

If you are working within a PHP context, executing this query requires establishing a connection to the MySQL server first. Using the PDO extension is generally preferred over the older mysqli class due to its superior error handling and security features regarding prepared statements, although simple introspection queries do not strictly require parameters. Here is how you might integrate this into a PHP script:

<?php
$host = 'localhost';
$db   = 'your_database_name';
$user = 'db_user';
$pass = 'db_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    $table = 'users';
    
    $stmt = $pdo->prepare("
        SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE 
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = :dbname 
          AND TABLE_NAME = :table
    ");
    
    $stmt->execute(['dbname' => $db, 'table' => $table]);
    
    foreach ($stmt as $column) {
        echo "Column: {$column['COLUMN_NAME']}, Type: {$column['DATA_TYPE']}\n";
    }
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

There are alternative methods depending on your specific use case. For instance, if you only need a simple list of column names and do not require data types, the SHOW COLUMNS FROM table_name command is more concise. While this returns text output rather than a structured result set, it can be parsed easily in PHP using string manipulation functions like explode or str_getcsv.

SHOW COLUMNS FROM your_table_name;

However, relying on SHOW COLUMNS for programmatic logic is often less elegant than querying information_schema because the output format can vary slightly between MySQL versions (e.g., 5.7 vs 8.0) and may include extra columns like COLUMN_TYPE which contains complex representations of types.

Another consideration is the use of Laravel's Query Builder, which simplifies schema inspection significantly if you are already within the Laravel ecosystem. The Schema::getColumnListing() method returns an array of column names for a given table, abstracting away the underlying SQL query entirely. This is useful for rapid prototyping or when strict type safety isn't required during runtime checks.

use Illuminate\Support\Facades\Schema;

$columns = Schema::getColumnListing('users'); // Returns ['id', 'name', 'email', ...]

It is worth noting that getColumnListing() only returns the column names and does not provide data types or constraints. If your application needs to enforce specific validation rules based on column definitions, you must fall back to querying information_schema directly or using Laravel's Schema::getColumnType() method for individual columns.

When designing systems that rely on dynamic schema inspection, always remember that this approach reflects the current state of the database at runtime. If your application writes code that assumes a certain column exists based on introspection, ensure that database migrations are strictly enforced to prevent inconsistencies between development and production environments.

Our network