php pdo: get the columns name of a table

I solve the problem the following way (MySQL only)

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

$dbh means "database handle" and it represents the connection object.

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.

A simple PHP function

function getColumnNames($pdo, $table) {
    $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$table]);
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

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).

I needed this and made a simple function to get this done.

function getQueryColumns($q, $pdo){
    $stmt = $pdo->prepare($q);
    $stmt->execute();
    $colCount = $stmt->columnCount();
    $return = array();
    for($i=0;$i<$colCount;$i++){
        $meta = $stmt->getColumnMeta($i);
        $return[] = $meta['name'];
    }
    return $return;
}

Enjoy :)

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

?>