Get MySQL Table Column Names Using PHP

You will learn how MySQL exposes table metadata and how to retrieve column names from PHP. You will see the common SHOW COLUMNS approach, a portable INFORMATION_SCHEMA query, and the safety rules for using dynamic table names.

A database stores metadata: information that describes its own structure. A table's column names, data types, keys, and default values are all metadata.

MySQL provides SQL commands and system tables for reading this information:

• SHOW COLUMNS FROM table_name returns details about each column in a table.
• DESCRIBE table_name is a short alias for inspecting a table's columns.
• INFORMATION_SCHEMA.COLUMNS is a system view that can be queried with normal SELECT statements.

In PHP, you run one of these queries and loop through the result rows. Each row represents one column, and the Field value from SHOW COLUMNS is the column name.

This is useful when building admin tools, import/export utilities, schema inspection scripts, or dynamic reports. In most application code, however, the expected columns are usually known in advance, so metadata queries should not replace normal application design.

Think of a database table as a spreadsheet and its columns as the labels across the top row.

A normal query asks for the spreadsheet's data, such as customer names or order totals. A metadata query asks MySQL for the spreadsheet's blueprint: the labels, types, and rules that define the sheet.

SHOW COLUMNS is like asking: “What headers does this spreadsheet have, and what kind of values belong under each header?”

With PHP's PDO extension, use SHOW COLUMNS when you want column information for one known table.

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=shop;charset=utf8mb4',
    'app_user',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$stmt = $pdo->query('SHOW COLUMNS FROM `products`');

$columnNames = [];

while ($column = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $columnNames[] = $column['Field'];
}

print_r($columnNames);

Example output:

Array
(
    [0] => id
    [1] => name
    [2] => price
    [3] => created_at
)

SHOW COLUMNS FROM \products`` returns rows similar to these:

The code uses $column['Field'] because Field is the result-column name MySQL returns for the table column name.

If you only need the names, INFORMATION_SCHEMA can return exactly one value per row:

$sql = '
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = :database
      AND TABLE_NAME = :table
    ORDER BY ORDINAL_POSITION
';

$stmt = $pdo->prepare($sql);
$stmt->execute([
    'database' => 'shop',
    'table' => 'products',
]);

$columnNames = $stmt->fetchAll(PDO::FETCH_COLUMN);

PDO::FETCH_COLUMN collects the first selected column from every result row, producing a simple array of names.

Consider this code:

$stmt = $pdo->query('SHOW COLUMNS FROM `products`');
$columnNames = [];

while ($column = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $columnNames[] = $column['Field'];
}

Step by step:

1. $pdo->query(...) sends SHOW COLUMNS FROM \products`` to MySQL.
2. MySQL reads the definition of the products table rather than its product data.
3. $stmt holds the returned result set.
4. $columnNames = [] creates an empty array for the names.
5. fetch(PDO::FETCH_ASSOC) reads one metadata row as an associative array.
6. On the first loop, $column['Field'] might be id, so id is added to the array.
7. The loop continues once for every column in products.
8. When no rows remain, fetch() returns false and the loop ends.

If the table contains id, name, and price, the final value is:

['id', 'name', 'price']

Common uses for table-column metadata include:

• CSV imports: Match incoming CSV header names to valid database columns before importing rows.
• CSV exports: Build a file header row from the columns selected for an export.
• Admin dashboards: Display a generic table browser or schema viewer.
• Data migration scripts: Check whether an expected column exists before applying a migration or copying data.
• Reporting tools: Let a trusted administrator choose from available fields for a report.
• Debugging tools: Inspect a database schema while diagnosing an issue.

For user-facing features, do not automatically expose every column. Tables can contain private fields such as password hashes, access tokens, internal notes, or audit data.

In real projects, developers typically isolate schema inspection in a small repository, service, command, or migration utility instead of scattering SHOW COLUMNS queries throughout application code.

A practical pattern is to return a known PHP array:

function getTableColumns(PDO $pdo, string $table): array
{
    $allowedTables = ['products', 'orders', 'customers'];

    if (!in_array($table, $allowedTables, true)) {
        throw new InvalidArgumentException('Unknown table requested.');
    }

    $statement = $pdo->query("SHOW COLUMNS FROM `{$table}`");

    return array_column(
        $statement->fetchAll(PDO::FETCH_ASSOC),
        'Field'
    );
}

Important project practices:

• Validate identifiers: Table and column names cannot normally be supplied as PDO placeholders.
• Use an allowlist: Map accepted user choices to known table names.
• Use least-privilege credentials: The application account needs permission to inspect only the schemas it uses.
• Cache stable metadata when appropriate: A production table definition normally does not change per request.
• Prefer explicit allowlists for output: For exports and APIs, define permitted fields instead of exposing all discovered columns.

For a fixed application query, explicit SQL is clearer:

$stmt = $pdo->query('SELECT id, name, price FROM products');

Use dynamic metadata only when the feature genuinely needs to adapt to the schema.

Trying to parameterize a table name

This does not work as intended:

$stmt = $pdo->prepare('SHOW COLUMNS FROM ?');
$stmt->execute([$table]);

Placeholders represent data values, not SQL identifiers such as table names. Validate the table name against an allowlist, then safely insert that known identifier into the SQL.

Concatenating untrusted input into SQL

Avoid this:

$table = $_GET['table'];
$stmt = $pdo->query("SHOW COLUMNS FROM $table");

A user controls $table, so this can create SQL injection risks or invalid SQL. Never treat escaping alone as a substitute for validating identifiers.

Forgetting identifier quotes

Some names conflict with SQL keywords or contain special characters. Backticks identify a MySQL table or column name:

SHOW COLUMNS FROM `order`

Do not use single quotes for identifiers:

SHOW COLUMNS FROM 'products'

Single quotes mean a string value, not a table identifier.

Using SELECT * just to discover keys

This is inefficient and unreliable:

$row = $pdo->query('SELECT * FROM products LIMIT 1')->fetch(PDO::FETCH_ASSOC);
$names = array_keys($row);

It fails when the table has no rows and reads application data unnecessarily. Query metadata instead.

Assuming every database uses SHOW COLUMNS

SHOW COLUMNS is MySQL-specific. If portability matters, use a database abstraction layer or query the database's metadata facilities. INFORMATION_SCHEMA is widely available, though details can vary by database system.

SHOW COLUMNS versus INFORMATION_SCHEMA

Use SHOW COLUMNS for a short, readable query when the table is known and you want its definition.

Use INFORMATION_SCHEMA.COLUMNS when you need conditions such as “all nullable columns,” “columns in several tables,” or “only columns with a particular data type.” It also works naturally with prepared values for TABLE_SCHEMA and TABLE_NAME.

PDO versus MySQLi

Both PHP extensions can run these queries. PDO is often convenient because its API is consistent across supported database drivers. MySQLi is specific to MySQL. The SQL concept is identical either way.

-- MySQL: inspect columns in one table
SHOW COLUMNS FROM `products`;

-- Equivalent quick inspection command
DESCRIBE `products`;

-- Query metadata with normal SQL
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND TABLE_NAME = 'products'
ORDER BY ORDINAL_POSITION;

// PDO: get an array containing only column names
$stmt = $pdo->query('SHOW COLUMNS FROM `products`');
$names = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'Field');

// PDO: INFORMATION_SCHEMA with values safely bound
$stmt = $pdo->prepare(
    'SELECT COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = :schema AND TABLE_NAME = :table
     ORDER BY ORDINAL_POSITION'
);
$stmt->execute(['schema' => 'shop', 'table' => 'products']);
$names = $stmt->fetchAll(PDO::FETCH_COLUMN);

Rules to remember:

• Field is the column-name key returned by SHOW COLUMNS.
• Use backticks for validated MySQL identifiers when needed.
• Bind values with prepared statements; do not assume placeholders can represent table names.
• Allowlist dynamic table names.
• Use ORDINAL_POSITION to preserve the table's defined column order.

How do I get only column names from a MySQL table in PHP?

Run SHOW COLUMNS FROM \your_table`and collect theFieldvalue from each returned row. Alternatively, selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSand usePDO::FETCH_COLUMN`.

What does Field mean in SHOW COLUMNS output?

Field is MySQL's label for the table column name. Other returned values include Type, Null, Key, Default, and Extra.

Can I use a PDO placeholder for a table name?

No. PDO placeholders bind data values, not SQL identifiers. Validate the requested table against an allowlist before including a known-safe identifier in SQL.

Which is better: SHOW COLUMNS or DESCRIBE?

For this task, both are suitable and return similar information in MySQL. SHOW COLUMNS is often clearer in application code; DESCRIBE is popular for interactive inspection.

How can I retrieve column data types too?

With SHOW COLUMNS, read the Type key. With INFORMATION_SCHEMA.COLUMNS, select fields such as DATA_TYPE, COLUMN_TYPE, and IS_NULLABLE.

Does this work if a table is empty?

Yes. Metadata belongs to the table definition, so it is available even when the table contains zero data rows.

Should an application expose every discovered column to users?

Usually no. Use an explicit allowlist for API responses, reports, and exports so sensitive internal columns are not exposed.

• PDO prepared statements — Safely bind data values in PHP database queries.
• SQL injection prevention — Essential when table selection or query construction involves external input.
• MySQL INFORMATION_SCHEMA — System metadata views for tables, columns, indexes, and constraints.
• Database migrations — Version and apply schema changes predictably.
• PHP associative arrays — The natural format for rows fetched with PDO::FETCH_ASSOC.
• Database normalization — Helps design tables and columns with clear responsibilities.
• MySQL indexes and primary keys — Additional schema metadata often inspected alongside columns.