Get PDO Column name - the easy way :)

[ServerStorm]

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.

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

[sweatje]

Only for the MySQL PDO driver, what if you are using Sqlite?

[ServerStorm]

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

[kyberfabrikken]

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.

[kyberfabrikken]

By the way, for SQLite, the following is equivalent to SHOW COLUMNS ... :

$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'],
  );
}

[ServerStorm]

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

[sweatje]

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

[Czaries]

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

[ServerStorm]

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

[Czaries]

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 .

[ServerStorm]

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

[Cups]

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

[ServerStorm]

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!

[Cups]

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.

[ServerStorm]

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

[RandomCake]

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:

        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!