How to Retrieve MySQL Table Column Names using PHP?

Retrieving Table Column Names in MySQL using PHP

MySQL provides several methods to obtain the column names of a table. Here are the most commonly used techniques:

DESCRIBE

The DESCRIBE statement returns detailed information about a table, including the column names.

$stmt = $db->prepare("DESCRIBE my_table");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    echo "<p>" . $row['Field'] . "</p>";
}

INFORMATION_SCHEMA

Starting with MySQL version 5.0, the INFORMATION_SCHEMA database contains information about database schemas, tables, and columns.

$stmt = $db->prepare("SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table'");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    echo "<p>" . $row['COLUMN_NAME'] . "</p>";
}

SHOW COLUMNS

The SHOW COLUMNS statement displays detailed information about table columns.

$stmt = $db->prepare("SHOW COLUMNS FROM my_table");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($result as $row) {
    echo "<p>" . $row['Field'] . "</p>";
}

Group Concatenation

To obtain the column names as a comma-separated string, you can use group concatenation:

$stmt = $db->prepare("SELECT group_concat(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table'");
$stmt->execute();
$columnNames = $stmt->fetchColumn();

echo "<p>" . $columnNames . "</p>";

The above is the detailed content of How to Retrieve MySQL Table Column Names using PHP?. For more information, please follow other related articles on the PHP Chinese website!