Get a MySQL Table Row-names and Types using PHP PDO

Sometimes it is advantageous to have information about the columns of a table in the database.

If all you need is the SQL, copy the box bellow and use it in your favorite program.

SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_DEFAULT`, `COLUMN_TYPE` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?";

 
Here is a nice example function written in PHP that uses PDO to read the table columns and return a formatted array with the data.

/*
 * Recieves as input an PDO object, the database and table name
 * Returns an array with the column names and information about 
 * the columns of a table in the following format:
 * [ "COLUMN_NAME" => 
 *          [
 *           "type" => DATA_TYPE, 
 *           "default" => DEFAULT_VALUE, 
 *           "size" => "The Size or 0"
 *          ],
 * ]
 *
 * @param PDO $pdo
 * @param string $databaseName
 * @param string $tableName
 * @return array
*/
function getTableRowNames($pdo, $databaseName, $tableName) {
    // Generate a string with placeholders
    $sqlString = "SELECT `COLUMN_NAME`, `DATA_TYPE`, `COLUMN_DEFAULT`, `COLUMN_TYPE` 
            FROM `INFORMATION_SCHEMA`.`COLUMNS` 
            WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ?";
    // Create the parameters array
    $sqlParameters = [$databaseName, $tableName];
    $formatedRows = [];
    // Always use prepared statements to avoid sql injections
    $stmt = $pdo->prepare($sqlString);

    $stmt->execute($sqlParameters);
    $columnList = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($columnList as $columnDataRow) {
        if (isset($columnDataRow["COLUMN_NAME"], $columnDataRow["DATA_TYPE"],$columnDataRow["COLUMN_TYPE"])) {
                $tmp["type"] = $columnDataRow["DATA_TYPE"];
                $tmp["default"] = $columnDataRow["COLUMN_DEFAULT"];
                $tmp["size"] = 0;
                if (preg_match("/\((\d*)\)/",$columnDataRow["COLUMN_TYPE"], $sizeData)) {
                    $tmp["size"] = $sizeData[1] ?? "ERROR";
                }
                $formatedRows[$columnDataRow["COLUMN_NAME"]] = $tmp;
        }
    }
    return $formatedRows;
}

 

Sources

https://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names
https://dev.mysql.com/doc/refman/8.0/en/columns-table.html
https://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql
https://www.php.net/manual/en/class.pdo.php