PHP - getting field name from column index using PDO

Question

I'm looking for a way to get the field name from the column index returned by a SELECT query using PDO. What I'm looking for is an equivalent to

mysql_field_name(resource $result, int $field_offset)

The PHP documentation says the above function is deprecated as of PHP 5.5.0 and the alternative they suggest for PDO is considered experimental and the behavior of it may change without notice. That doesn't sound like something I would want in my application.

I also do not want to query the MySQL system tables to get the columns since that just would not be efficient.

If the results of the fetchAll() function returns the resulting array below, is there a way I can retrieve the value of the column name (which equals "NAME" below) by specifying the column index?

Array
(
[0] => Array
    (
        [NAME] => pear
        [0] => pear
        [COLOUR] => green
        [1] => green
    )

[1] => Array
    (
        [NAME] => watermelon
        [0] => watermelon
        [COLOUR] => pink
        [1] => pink
    )

)

Answer 1

This worked for me, although creating an object is not required.

<?php
namespace yourNameSpace;

// Extend PDO class for safe error handling
Class SafePDO extends \PDO {

    public static function exception_handler($exception) {
        // Output the exception details
        die("Uncaught exception: " . $exception->getMessage());
    }

    public function __construct($dsn, $username='', $password='', $driver_options=array()) {

        // Temporarily change the PHP exception handler while we . . .
        set_exception_handler(array(__CLASS__, 'exception_handler'));

        // . . . create a PDO object
        parent::__construct($dsn, $username, $password, $driver_options);

        // Change the exception handler back to whatever it was before
        restore_exception_handler();
    }

}

class TableRows extends \RecursiveIteratorIterator { 
    function __construct($it) { 
        parent::__construct($it, self::LEAVES_ONLY); 
    }

    function current() {
        return "<td>" . parent::current(). "</td>";
    }

    function beginChildren() { 
        echo "<tr>"; 
    } 

    function endChildren() { 
        echo "</tr>" . "\n";
    } 
} 


trait traitSQLExecution {

    private function generateTable(
        $sql,
        $servername = "default_value",
        $username = "default_value",
        $password = "default_value",
        $dbname = "default_value"
    ) {

        // Connect to the database with defined constants
        $pdo = new SafePDO("mysql: host=$servername;dbname=$dbname", $username, $password);

        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        try {               
            $stmt = $pdo->prepare($sql);
            $stmt->execute();

            // set the resulting array to associative
            $result = $stmt->setFetchMode(\PDO::FETCH_ASSOC); 

            $recordSet = $stmt->fetchAll();

            echo '<table id="myReport"><tr>';

            // Get table headers dynamically
            foreach($recordSet[0] as $k=>$v) {
                echo "<th>$k</th>";
            }

            echo '</tr>';

            foreach(new TableRows(new \RecursiveArrayIterator($recordSet)) as $k=>$v) { 
                echo $v;
            }
        }
        catch(PDOException $e) {
            echo 'Error: ' . $e->getMessage();
        }
        $pdo = null;
        $stmt = null;
        echo '</table>';
    }
}

class obj_report {
    // use section is used to include reusable pieces of code
    use traitSQLExecution;


    // Construct is called upon instantiation of an object
    function __construct($table){
        /****************************************************************************************
        * Do not include public variables as this is bad design.
        * Only public methods should be available to change the object in a predictable fashion.
        ****************************************************************************************/

        if ( $table !== "" ) {
            $this->generateTable($table);
        }
    }
}

$obj = new obj_report((string)htmlspecialchars($_POST['table']) ?: "");
$obj = NULL;

?>

Answer 2

When I read this question I felt like I was the one posting it because it's exactly what I was looking for since I wanted to create an html table dynamically from a query using pdo and I didn't want use show tables or query the schema and I wanted to use the fields in my query

the answer that hector gave is exactly the key, I just thought I would share/post a working sample in which I'm parsing an array to get my table headers and I'm creating and html table with the items queried from the db:

$sqlselect = "SELECT name, last, othercol FROM persontable";

// I prepare and execute my query
$stmt = $conn->prepare($sqlselect);
$stmt->execute();

//get full recordset: header and records
$fullrs = $stmt->fetchAll(PDO::FETCH_ASSOC); 

//get the first row (column headers) but do not go to next record
$colheaders = current($fullrs)

out = ""; //variable that will hold my table

out .= <table>;

//get my columns headers in the table          
foreach($colheaders as $key=>$val) 
{
    $out .= '<th>'.$key.'</th>';
}

//get my records in the table
foreach($fullrs as $row) 
{
    $out .= "<tr>"; 
        $out .= '<td>'.$row['name'].'</td>';
        $out .= '<td>'.$row['last'].'</td>';
        $out .= '<td>'.$row['othercol'].'</td>';
    $out .= "<tr>";
}

out .= </table>;  

//spit my table out
echo $out;

Hope this helps someone

Answer 3

I guess you could use array_flip($array) to swap the keys/values of your array, then you could get the "NAME" field easily

Also, you can retrieve the data using fetch(PDO::FETCH_ASSOC), that way you only will get the associative names of the query