getting column names in mssql pdo

Question

I'm building an HTML table from some MS SQL PDO queries. Or trying to. The first roadblock I've hit is that I cannot get the column names of a particular table. Found here, I've tried to us the solution

function getColumnNames(){ 

$sql = "select column_name from information_schema.columns where table_name = 'myTable'";
#$sql = 'SHOW COLUMNS FROM ' . $this->table; 

$stmt = $this->connection->prepare($sql); //this is the line that triggers the error

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

getColumnNames();

got the error:

Fatal error: Using $this when not in object context

Whereas this (from the same SO post)

$q = $dbh->prepare("DESCRIBE username");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
print_r($table_fields);

yielded the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2812 General SQL Server error: Check messages from the SQL Server [2812] (severity 16) [(null)]'

I'm just trying to get the names of the columns so I can loop through and get the values of each of their rows. How can I accomplish this? Thanks

hek2mgl · Answer 1 · 2013-02-05T03:57:11.530

3

DESCRIBE is a MySQL specific command. On MS SQL you can use a strored procedure for that:

exec sp_columns MyTable

You'll find documentation at MSDN

Here comes a little example how this can be done using PDO:

<?php

// will contain the result value
$return = null;

// replace table name  by your table name
$table_name = 'table_name';

// prepare a statement
$statement = $pdo->prepare("exec sp_columns @table_name = :table_name");

// execute the statement with table_name as param
$statement->execute(array(
    'table_name' => $table_name
));

// fetch results
$result = $statement->fetchAll($sql);

// test output
var_dump($result);

edited Feb 05 '13 at 03:57

answered Feb 05 '13 at 03:18

hek2mgl

152,036
28
249
266

Thanks for pointing me to the documentation. Though I don't really understand what I must do. Can you show me an example? – 1252748 Feb 05 '13 at 03:24
Unfortunately I've no MS SQL server for testing. I found [this](http://www.php.net/manual/en/ref.pdo-dblib.php#101323). Does it help? – hek2mgl Feb 05 '13 at 03:32
Unfortunately not so much. I don't really know much about prepared statements. Just trying to delve into them right now. Thanks for your help – 1252748 Feb 05 '13 at 03:41
Will try to give an example .. (but can unfortunately not test it) – hek2mgl Feb 05 '13 at 03:42

score 0 · Answer 2 · answered Apr 27 '16 at 12:29

This is an old question but I'll add what I've learnt anyway. I managed to get the column values by doing this.

try {
            $query = $this->db->prepare("DESCRIBE posts");
            $query->execute();
        //retrieve the columns inside the table posts
            $forumrows = $query->fetchAll(PDO::FETCH_COLUMN); 
            //var_dump($forumrows);
        //Output each column Id with its Value
            foreach($forumrows as $forumrow) {
                echo $forumrow . "</br>";
        }
          } catch(PDOException $e) {
                echo $e->getMessage();
        }

getting column names in mssql pdo

2 Answers2