How to Retrieve MySQL Table Column Names using PHP?-PHP Tutorial-php.cn

How to Retrieve MySQL Table Column Names using PHP?

Barbara Streisand

Release： 2024-12-15 10:16:10

Original

338 people have browsed it

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>";
}

Copy after login

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>";
}

Copy after login

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>";
}

Copy after login

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>";

Copy after login

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!