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- gt get connection     select    db- gt query  SELECT   FROM contacts      total column    select- gt columnCount    var dump  total column    for   counter   0   counter  lt   total column   counter            meta    select- gt getColumnMeta  counter        column      meta  name      print r  column     Then I get   Array        0    gt  id      1    gt  name      2    gt  age

User · Answer

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.

User · Answer

This is an old question but here s my input  function getColumns  dbhandle   tableName         columnsquery    dbhandle- gt 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

User · Answer

Just Put your Database name username password  Where i marked    and table name  amp  Yuuppiii      you get all data from your main database  with column name    lt  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- gt getMEssage             qry    dbcon- gt query  q        qry- gt setFetchMode PDO   FETCH OBJ        return  qry       echo   lt table gt       Get Colums Names in table row     columns   array      qry1  qry  SHOW COLUMNS FROM Your table name     while    column    qry1- gt fetch  - gt Field        echo   lt td gt    column   lt  td gt         columns      column      echo   lt tr gt         Fetch all data into a html table       qry2   qry  SELECT   FROM Your table name     while    details    qry2- gt fetch           echo   lt tr gt        foreach   columns as  c name        echo   lt td gt    details- gt  c name   lt  td gt           echo   lt  table gt       gt

User · Answer

PDOStatement  getColumnMeta    As Charle s mentioned  this is a statement method  meaning it fetches the column data from a prepared statement  query

User · Answer

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- gt query  SELECT   FROM my table LIMIT 0    for   i   0   i  lt   rs- gt columnCount     i           col    rs- gt getColumnMeta  i        columns      col  name      print r  columns

User · Answer

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   lt table border  1  gt  n        ncols   oci num fields  stid       echo   lt tr gt        for   i   1   i  lt    ncols   i                   column name    oci field name  stid   i               echo   lt td gt  column name lt  td gt              echo   lt  tr gt          while   row   oci fetch array  stid  OCI ASSOC OCI RETURN NULLS                 echo   lt tr gt  n               foreach   row as  item                        echo       lt td gt       item     null   htmlentities  item  ENT QUOTES      amp nbsp        lt  td gt  n                             echo   lt  tr gt  n             echo   lt  table gt  n

User · Answer

My contribution ONLY for SQLite          Returns an array of column names for a given table     Arg   dsn should be replaced by  this- gt 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- gt query  PRAGMA table info     table      - gt fetchAll  PDO  FETCH COLUMN  1

User · Answer

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- gt setAttribute PDO  ATTR EMULATE PREPARES  false    condb- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION       get column names  query    condb- gt prepare  DESCRIBE  tablenames     query- gt execute     table names    query- gt fetchAll PDO  FETCH COLUMN   return  table names     Close connection  condb   null     catch PDOExcepetion  e    echo  e- gt getMessage          Usage Example    columns   getColumns  name of table       OR getColumns  name of table   if you are using variable   foreach  columns as  col    echo  col     lt br  gt

User · Answer

q    dbh- gt prepare  DESCRIBE tablename     q- gt execute     table fields    q- gt fetchAll PDO  FETCH COLUMN     must be   q    dbh- gt prepare  DESCRIBE database table     q- gt execute     table fields    q- gt fetchAll PDO  FETCH COLUMN

User · Answer

My 2 cents    result    db- gt query  select   from table limit 1     fields   array keys  result- gt fetch PDO  FETCH ASSOC      And you will get the column names as an array in the var  fields

User · Answer

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- gt table         stmt    this- gt connection- gt prepare  sql         try                if  stmt- gt execute                   raw column data    stmt- gt fetchAll PDO  FETCH ASSOC                 foreach  raw column data as  outer key   gt   array                    foreach  array as  inner key   gt   value                                if    int  inner key                                     this- gt column names      value                                                                                               return  this- gt column names             catch  Exception  e                    return  e- gt getMessage      return exception

User · Answer

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- gt query  PRAGMA table info      table name              container result- gt setFetchMode PDO  FETCH ASSOC         foreach   container result as  conkey   gt   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    gt  ccid      administration title    gt  administration title      content type id    gt  content type id      author id    gt  author id      date created    gt  date created      language id    gt  language id      publish date    gt  publish date      status    gt  status      relationship ccid    gt  relationship ccid      url alias    gt  url alias     This is particularly nice to have when the table is empty

User · Answer

I solve the problem the following way  MySQL only    q    dbh- gt prepare  DESCRIBE tablename     q- gt execute     table fields    q- gt fetchAll PDO  FETCH COLUMN

[php] php pdo: get the columns name of a table

Examples related to php

Examples related to pdo

Examples related to fetch