How do I get column names from a given MySQL table?

Question

How do I get the column names of a table into a PHP array using the mysqli extension? I need to fetch the column names of any given table without fetching any data from the table.

Solution 1

The following code gets all column names from table table_name:

$mysqli = new mysqli('localhost', 'USERNAME_HERE', 'PASSWORD_HERE', 'DATABASE_HERE');

$sql = 'SHOW COLUMNS FROM table_name';
$res = $mysqli->query($sql);

while($row = $res->fetch_assoc()){
    $columns[] = $row['Field'];
}

Since I have the columns id and name in my table, this is the result:

Array
(
    [0] => id
    [1] => name
)

---

If you want to get the columns from a resultset, it depends, but here is one way to do it:

$mysqli = new mysqli('localhost', 'USERNAME_HERE', 'PASSWORD_HERE', 'DATABASE_HERE');

$sql = 'SELECT * FROM table_name';
$res = $mysqli->query($sql);

$values = $res->fetch_all(MYSQLI_ASSOC);
$columns = array();

if(!empty($values)){
    $columns = array_keys($values[0]);
}

Example result for $columns:

Array
(
    [0] => id
    [1] => name
)

Example result for $values:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Name 1
        )

    [1] => Array
        (
            [id] => 2
            [name] => Name 2
        )

)

Solution 2

You can use array_keys() to get all the keys of an array,

$myarray = array('key1' => 'a', 'key2' => 'b')
$x = array_keys($myarray);

The result you want can be obtained from $x

$x = array(0 => 'key1', 1 => 'key2');

Inorder to get the column names of a table,

$sql = "SELECT * FROM table_name LIMIT 1";
$ref = $result->query($sql);
$row = mysqli_fetch_assoc($ref);
$x = array_keys($row);

now $x array contains the column names of the table

Comments

[benj rei]

When using either solutions the query returns a boolean not the column names.

[FirstOne]

@benjrei make sure the connection works... can you check for errors?