How to Get the Column Names from Table in MySQL using PHP

The SHOW COLUMNS syntax shows the information about columns in a specified table. This syntax is useful to run the SQL query on phpMyAdmin panel and display fields of MySQL table. But if you want to select and get the column names from the table in the script, MySQL query needs to be executed using PHP.

The INFORMATION_SCHEMA is the best way to get the columns of a table in MySQL. In the example code snippet, we will show you how to get and show the column names from a table using PHP and MySQL.

MySQL Query to Get Column Names
The following query will find the column names from a table in MySQL and returns all the columns of the specified table.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name'

Use TABLE_SCHEMA to select the columns of a table from a specific database.

Get Columns from Table using PHP and MySQL
The following code shows the complete process to get all column names from a table using PHP and MySQL.

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}


// Query to get columns from table
$query = $db->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name'");

while($row = $query->fetch_assoc()){
    $result[] = $row;
}

// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');