php pdo: get the columns name of a table

How can I get all the column names from a table using PDO?

id         name        age
1          Alan        35      
2          Alex        52
3          Amy         15

The info that I want to get are,

id         name        age

EDIT:

Here is my attempt,

$db = $connection->get_connection();
$select = $db->query('SELECT * FROM contacts');

$total_column = $select->columnCount();
var_dump($total_column);
	
for ($counter = 0; $counter < $total_column; $counter ++) {
	$meta = $select->getColumnMeta($counter);
	$column[] = $meta['name'];
}
print_r($column);

Then I get,

Array
(
    [0] => id
    [1] => name
    [2] => age
    ...
    
)

I solve the problem the following way (MySQL only)

$q = $dbh->prepare("DESCRIBE tablename");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);

This will work for MySQL, Postgres, and probably any other PDO driver that uses the LIMIT clause.

Notice LIMIT 0 is added for improved performance:

$rs = $db->query('SELECT * FROM my_table LIMIT 0');
for ($i = 0; $i < $rs->columnCount(); $i++) {
    $col = $rs->getColumnMeta($i);
    $columns[] = $col['name'];
}
print_r($columns);

My 2 cents:

$result = $db->query('select * from table limit 1');
$fields = array_keys($result->fetch(PDO::FETCH_ASSOC));

And you will get the column names as an array in the var $fields.

> $sql = "select column_name from > information_schema.columns where > table_name = 'myTable'";

PHP function credits : http://www.sitepoint.com/forums/php-application-design-147/get-pdo-column-name-easy-way-559336.html

function getColumnNames()
{
    $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = 'myTable'";
    #$sql = 'SHOW COLUMNS FROM ' . $this->table;
    $stmt = $this->connection->prepare($sql);
    try {
        if ($stmt->execute())
        {
            $raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach($raw_column_data as $outer_key => $array)
            {
                foreach($array as $inner_key => $value
                {
                    if (!(int)$inner_key)
                    {
                        $this->column_names[] = $value;
                    }
                }
            }
        }
        return $this->column_names;
    }
    catch (Exception $e)
    {
        return $e->getMessage(); //return exception
    }
}

Here is the function I use. Created based on @Lauer answer above and some other resources:

//Get Columns
function getColumns($tablenames) {
global $hostname , $dbnames, $username, $password;
try {
$condb = new PDO("mysql:host=$hostname;dbname=$dbnames", $username, $password);

//debug connection
$condb->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$condb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// get column names
$query = $condb->prepare("DESCRIBE $tablenames");
$query->execute();
$table_names = $query->fetchAll(PDO::FETCH_COLUMN);
return $table_names;

//Close connection
$condb = null;

} catch(PDOExcepetion $e) {
echo $e->getMessage();
}
}

Usage Example:

$columns = getColumns('name_of_table'); // OR getColumns($name_of_table); if you are using variable.

foreach($columns as $col) {
echo $col . '<br/>';
}

This is an old question but here's my input

function getColumns($dbhandle, $tableName) {
    $columnsquery = $dbhandle->query("PRAGMA table_info($tableName)");
    $columns = array();
    foreach ($columnsquery as $k) {
        $columns[] = $k['name'];
    }
    return $columns;
}

just put your variable for your pdo object and the tablename. Works for me

This approach works for me in SQLite and MySQL. It may work with others, please let me know your experience.

• Works if rows are present
• Works if no rows are present (test with DELETE FROM table)

Code:

$calendarDatabase = new \PDO('sqlite:calendar-of-tasks.db');    
$statement = $calendarDatabase->query('SELECT *, COUNT(*) FROM data LIMIT 1');
$columns = array_keys($statement->fetch(PDO::FETCH_ASSOC));
array_pop($columns);
var_dump($columns);

I make no guarantees that this is valid SQL per ANSI or other, but it works for me.

PDOStatement::getColumnMeta()

As Charle's mentioned, this is a statement method, meaning it fetches the column data from a prepared statement (query).

A very useful solution here for SQLite3. Because the OP does not indicate MySQL specifically and there was a failed attempt to use some solutions on SQLite.

    $table_name = 'content_containers';
    $container_result = $connect->query("PRAGMA table_info(" . $table_name . ")");
    $container_result->setFetchMode(PDO::FETCH_ASSOC);


    foreach ($container_result as $conkey => $convalue)
    {

        $elements[$convalue['name']] = $convalue['name'];

    }

This returns an array. Since this is a direct information dump you'll need to iterate over and filter the results to get something like this:

Array
(
    [ccid] => ccid
    [administration_title] => administration_title
    [content_type_id] => content_type_id
    [author_id] => author_id
    [date_created] => date_created
    [language_id] => language_id
    [publish_date] => publish_date
    [status] => status
    [relationship_ccid] => relationship_ccid
    [url_alias] => url_alias
)

This is particularly nice to have when the table is empty.

My contribution ONLY for SQLite:

/**
 * Returns an array of column names for a given table.
 * Arg. $dsn should be replaced by $this->dsn in a class definition.
 *
 * @param string $dsn Database connection string, 
 * e.g.'sqlite:/home/user3/db/mydb.sq3'
 * @param string $table The name of the table
 * 
 * @return string[] An array of table names
 */
public function getTableColumns($dsn, $table) {
   $dbh = new \PDO($dsn);
   return $dbh->query('PRAGMA table_info(`'.$table.'`)')->fetchAll(\PDO::FETCH_COLUMN, 1);
}

Just Put your Database name,username,password (Where i marked ?) and table name.& Yuuppiii!.... you get all data from your main database (with column name)

<?php 

function qry($q){

    global $qry;
    try {   
    $host = "?";
    $dbname = "?";
    $username = "?";
    $password = "?";
    $dbcon = new PDO("mysql:host=$host; 
    dbname=$dbname","$username","$password");
}
catch (Exception $e) {

    echo "ERROR ".$e->getMEssage();

}

    $qry = $dbcon->query($q);
    $qry->setFetchMode(PDO:: FETCH_OBJ);

    return $qry;

}


echo "<table>";

/*Get Colums Names in table row */
$columns = array();

$qry1= qry("SHOW COLUMNS FROM Your_table_name");

while (@$column = $qry1->fetch()->Field) {
    echo "<td>".$column."</td>";
    $columns[] = $column;

}

echo "<tr>";


    

/* Fetch all data into a html table * /

$qry2 = qry("SELECT * FROM Your_table_name");

while ( $details = $qry2->fetch()) {

    echo "<tr>";
    foreach ($columns as $c_name) {
    echo "<td>".$details->$c_name."</td>";

}

}

echo "</table>";

?>

$q = $dbh->prepare("DESCRIBE tablename");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);

must be

$q = $dbh->prepare("DESCRIBE database.table");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);

There is no need to do a secondary query. Just use the built in oci_field_name() function:

Here is an example:

oci_execute($stid);                  //This executes

    echo "<table border='1'>\n";
    $ncols = oci_num_fields($stid);
    echo "<tr>";
    for ($i = 1; $i <= $ncols; $i++) {
            $column_name  = oci_field_name($stid, $i);
            echo "<td>$column_name</td>";
    }
    echo "</tr>";


    while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
            echo "<tr>\n";
            foreach ($row as $item) {
                    echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
            }
            echo "</tr>\n";
    }
    echo "</table>\n";