SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Get PDO Column name - the easy way :)

    Hi

    Czaries wrote this post regarding Column Meta Attributes... his method works well; however I needed something simpler so I wrote this method (that goes with a crud class) that someone may find handy if they are using PDO and would like to simply return all column names of a given table.

    Keep in mind:

    • $this->table_name is a property in my Crud Class so change it to your own property name or variable
    • $this->dbc should be a PDO object; again rename it if you don't like it.
    • $this->column_names is a property in my Crud Class so you can return it (once it has been propagated; you can return it directly or just change it to a name you prefer.
    • Yes I know that it is simple and most of you already know this; however there are always some lurkers in need right .
    • [edit] As sweatje points out (below) this works only for MySQL PDO driver.

    PHP Code:
    function getColumnNames(){
            
    $sql 'SHOW COLUMNS FROM ' $this->table_name;
            
            
    $this->stmt $this->dbc->prepare($sql);
                
            try {    
                if(
    $this->stmt->execute()){
                    
    $raw_column_data $this->stmt->fetchAll();
                    
                    foreach(
    $raw_column_data as $outer_key => $array){
                        foreach(
    $array as $inner_key => $value){
                                
                            if (
    $inner_key === 'Field'){
                                    if (!(int)
    $inner_key){
                                        
    $this->column_names[] = $value;
                                    }
                                }
                        }
                    }        
                }
                return 
    true;
            } catch (
    Exception $e){
                    return 
    $e->getMessage(); //return exception
            
    }        
        } 
    Cheers,
    ServerStorm
    Last edited by ServerStorm; Jul 13, 2008 at 20:00.
    ictus==""

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only for the MySQL PDO driver, what if you are using Sqlite?
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Yes I should have stated that this is only for MySQL,

    I currently don't use Sqlite. Do you have a simple more flexible solution?

    I guess that you are gently trying to say 'don't post it, if it is not flexible' . I just thought it might help someone that eventually ran into this (using MySQL) and didn't quite now how to do it.

    Regards,
    ServerStorm
    ictus==""

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I never got around to investigate it further, but I believe you can use the INFORMATION_SCHEMA views to get the same information (and more). The benefit of this method, is that this is that this format follows a standard, which is supported from other databases as well, which would make the code portable across different rdbms'es. From the MySql manual:
    The implementation for the INFORMATION_SCHEMA table structures in MySQL follows the ANSI/ISO SQL:2003 standard Part 11 Schemata. Our intent is approximate compliance with SQL:2003 core feature F021 Basic information schema.
    I'm pretty sure that SQLite doesn't (yet) implement this interface though, but it seems like the right way to go with this.

  5. #5
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way, for SQLite, the following is equivalent to SHOW COLUMNS ... :
    Code:
    $result = $pdo->query("PRAGMA table_info(" . $table_name . ")");
    $result->setFetchMode(PDO::FETCH_ASSOC);
    $meta = array();
    foreach ($result as $row) {
      $meta[$row['name']] = array(
        'pk' => $row['pk'] == '1',
        'type' => $row['type'],
      );
    }

  6. #6
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi,

    Kyber I had not come across INFORMATION_SCHEMA when I was doing searching on how best to do this - most people where using DESCRIBE or SHOW COLUMNS FROM, so I'm going to look into this as I would prefer an ANSI compliant way.

    Thanks - also for the SQLite example

    Regards,
    ServerStorm
    ictus==""

  7. #7
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    By the way, for SQLite, the following is equivalent to SHOW COLUMNS ... :
    Thanks for that. I had not run across that, even asked about it at a sqlite presentation I attended at a conference. I won't disgrace this forum with the ugly sqlite_master.sql select and regex parsing I was doing
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  8. #8
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    I never got around to investigate it further, but I believe you can use the INFORMATION_SCHEMA views to get the same information (and more). The benefit of this method, is that this is that this format follows a standard, which is supported from other databases as well, which would make the code portable across different rdbms'es.
    Yes, you should probably update the code to use this method. I wrote that code a while ago, and have been re-using it ever since. That was before I found out MySQL supported the INFORMATION SCHEMA standard as kyber shared.

    I'm glad you found some of my old code useful though - that's what these forums are for

  9. #9
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Czaries,

    Thanks... I do find some of your old posts useful. You know what is said that old isn't necessarily bad

    I am in the process of finishing a project, but as soon as I finish I am going to tackle updating this to an INFORMATION_SCHEMA based class. If I think that it is not too bad for this forum then I'll post it an hopefully save others the time.

    Regards,
    ServerStorm
    ictus==""

  10. #10
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Thanks... I do find some of your old posts useful. You know what is said that old isn't necessarily bad
    Yep... and there's a lot to be said for something that works. I'm a pragmatist, so usually even if I know there's a better way, I won't change something until I need to .

  11. #11
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Czaries,

    I'm a pragmatist, so usually even if I know there's a better way, I won't change something until I need to .
    Ya, Kyber and others have many times said, find the simplest solution and then if needed refactor. Lately I've been living by this and my code is becoming easier to write and not as complex, yet the surprising thing is that it seems to be just as functional. Sticking to what works is certainly pretty good advise - well that is if it works but is not horrible and inefficient I guess some quality does have to exist

    Regards,
    ServerStorm
    ictus==""

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I am in the process of finishing a project, but as soon as I finish I am going to tackle updating this to an INFORMATION_SCHEMA based class. If I think that it is not too bad for this forum then I'll post it an hopefully save others the time.
    Yes, please do, Lurkers++;

  13. #13
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hey Cups,

    Thanks for the support (I think ). I think that you are a better programmer than me so I don't really consider you a Lurker; although how is anyone to know right?

    I will be sure to post even my less eloquent snippets for all to take or berrate!
    ictus==""

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Nah, mate, I can only talk the talk I am afraid.

    There is something I have on my "refactor next time you use it" list and your thread has reminded me of it existence ... and in fact might just make it more presentable.

  15. #15
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Cups

    Thanks - I was apprehensive to post it because of some of the very advanced developers on this form, so I'm glad it helped someone else. Good luck refactoring!

    Regards,
    ServerStorm
    ictus==""

  16. #16
    SitePoint Member
    Join Date
    Dec 2007
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to reply to such an old thread, but this is just about the only useful page that comes up in Google when trying to work out how to best get a list of a tables columns, I've taken peoples suggestions on board (mainly the bit about the information_schema) and written this:

    PHP Code:
            function getColumnNames(){
                
            
    $sql 'select column_name from information_schema.columns where lower(table_name)=lower(\''.$this->table.'\')';
            
    #$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
                
    }        
            } 
    I'm sure it is far from perfect, but hopefully, like the code that came before it, it'll give people who are lost a bit of direction!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •