php pdo: get the columns name of a table

Question

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

)

Rigger

Do you want the column names in a table, or the column names in the record set from a query? These are two different things done in two different ways. Phil's answer does the former, JapanPro's answer does the latter! – Disloyal

@charles: I think I only want to get the column names in a table. I don't quite understand what u mean by column names in the record set from a query. but see my answer in my edit above. thanks. – Rigger

@lauthiamkok: You're doing that in your updated post -- you're making a query, which returns a set of records, then you're grabbing the column names for that specific set. It just happens that the column names in your result set are identical to those in your table. Remember, SQL columns can be aliased. – Disloyal

@lauthiamkok, if you were trying to get the column names from a table, then @JapanPro's answer below is the best way to do that -- using the information_schema method. What you are doing does the job but isn't the "right" way. – Disloyal

when you have an empty table your method fails, because there's no records to fetch the columns from. – Outfit

Answer 1

I solve the problem the following way (MySQL only)

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

Turnstone

This is only supported by MySQL. – Breastfeed

why not $q = $dbh->query("DESCRIBE tablename"); $table_fields = $q->fetchAll(PDO::FETCH_COLUMN);? – Rowley

Reader, for a generic solution, see @Pseudoscope answer. – Erle

IMHO, while this answer may be useful to many, it doesn't match the need expressed in the question, i.e. "Is there a way to retrieve the column name from the rowset array while I'm iterating data?" The user wants to see the key in the array while iterating values. The key($array) function may help. – Mersey

What is $dbh? – Scholastic

Answer 2

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

Pseudoscope

Thanks Will! Hello reader, that is the generic PDO solution! The Lauer solution only supported by MySQL -- and PDOStatement::getColumnMeta() is complex, is not supported for all PDO drivers, and is listed as experimental. PS: the query runs also with empty tables. – Erle

Perhaps simplify with something like $columns = array_keys($rs->fetchAll(PDO::FETCH_ASSOC));... I not try. – Erle

@PeterKrauss, I tried your second comment, there are no results (no columns even) if the LIMIT is 0. – Lancastrian

... hum... And (for check where the bug) changing SQL to LIMIT 1 (in a not-empty table) it works? – Erle

@PeterKrauss, yeah, it needs at least one result. – Lancastrian

@DaviddCeFreitas, sorry the method getColumnMeta have a 6+ years old "is EXPERIMENTAL" alert...The only way is using a non-empty table. You can use something like SELECT * FROM my_table WHERE id=0 and populate all tables with a no-data row. – Erle

LIMIT 0 is a MySQL / PGSQL specific syntax. How is this generic? How to make it so? – Venom

Postgres PDO getColumnMet will not work for this SQL: select 'abc', 123 -- any idea to get column name? the output will be ?column? If MySQL, it will show 'abc', 123 as column names – Electropositive

@ber A universal answer would be to replace your SELECT: 'SELECT * FROM my_table WHERE 3 < 2'. I believe (but have not done exhaustive testing) that the performance of that statement (at least for MYSQL, DB2, Oracle) will rival LIMIT 0. (You could use any other "always false" test.) – Lutenist

Answer 3

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.

Marseilles

This produces a Warning if the table is empty! ===> "Warning: array_keys() expects parameter 1 to be array, bool given in..." – Godliman

Answer 4

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);
}

Sway

add also schema_name to where – Bowls

+1 for information_schema instead of something DB-specific. – Disloyal

@Disloyal Isn't information_schema MySQL specific? This question isn't tagged MySQL so it could be anything. Mind you, the method in my answer probably doesn't work on every DB either ;) – Hymenopteran

@Phil, information_schema is part of the ANSI standard. It's been implemented in MySQL, PostgreSQL and MSSQL. Unless the OP is using Oracle or SQLite, it should work fine for him (assuming this answer was what he wanted). – Disloyal

@Disloyal Thanks, learned something. Hadn't seen it in MSSQL and the only other DB I've worked with is Oracle – Hymenopteran

Just checked this solution, and it appears to show missleading results when the user has access to two databases, which both have a table myTable. The result is a combined list of columns in both the tables, instead of the list of of columns in the particular table in the current database. – Deciare

@PawełStawarz there is a TABLE_SCHEMA column that indicates what schema the table and column belong to. However, the downsides to using information_schema is that it is possible not all users/accounts will have access to this system table. This is also slower than getColumnMeta(). – Idiot

@PawełStawarz - did you find a solution to solving the 2 tables in 2 db's issue? – Fortyfive

@JamesWilson No, I just used ragnars solution instead: https://mcmap.net/q/270371/-php-pdo-get-the-columns-name-of-a-table – Deciare

Answer 5

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/>';
}

Gallipot

Answer 6

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

Liebig

Answer 7

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.

Glaydsglaze

How performant is this going to be if there are 284,000 rows in the database? – Tortious

@Tortious thanks for that note. Updated code to work on arbitrary-sized databases – Glaydsglaze

Answer 8

PDOStatement::getColumnMeta()

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

Hymenopteran

And, as @Darragh mentioned, it is experimental and may not work in future. – Acidhead

Answer 9

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

Godliman

Answer 10

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.

Greedy

Answer 11

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);
}

Byington

Answer 12

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

?>

Clever

Answer 13

$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);

Theron

Answer 14

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

Onstad

He's using PDO, not OCI. Also, I'd vote you down for calling someone else's solution "dumb"...but I don't want to be unwelcoming. Thanks for trying! – Faradmeter