Getting column names in mssql pdo
I am creating an HTML table from some MS SQL PDO queries. Or I'm trying. The first roadblock I hit is that I cannot get the column names of a particular table. Found it here , I tried to find a 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
While this (from the same SO post)
$q = $dbh->prepare("DESCRIBE username");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
print_r($table_fields);
gave an 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 column names so I can loop through and get the values of each of my rows. How can i do this? Thanks to
+3
source to share
2 answers
DESCRIBE
is a MySQL specific command. In MS SQL, you can use a handled procedure for this:
exec sp_columns MyTable
You will find documentation on MSDN
Here's a small example of 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);
+3
source to share
This is an old question, but I'll add what I've learned anyway. I was able 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();
}
0
source to share