php之当查询没有返回记录时，如何通过 PDO/Sqlite 获取列名_编程设计

下面的代码允许我将一个 SQL 语句传递给一个类并调用它的方法来显示一个漂亮的结果表，包括列名。

但是，如果没有结果，我仍然希望显示列名。

不幸的是，getColumnMeta 没有像我发现的其他示例那样返回任何数据。

有谁知道如何让 getColumnMeta() 在此示例中工作，或者当查询返回零行时我可以通过其他方式从 SQL 语句中获取字段的名称？

<!DOCTYPE html> 
<html> 
    <head> 
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> 
        <style type="text/css"> 
            table thead tr td { 
                background-color: #ddd; 
                padding: 5px; 
                font-weight: bold; 
            } 
            table tbody tr td { 
                background-color: #eee; 
                padding: 5px; 
                color: navy; 
            } 
            div.sqlCommand { 
                font-family: courier; 
            } 
 
            h2 { 
                border-bottom: 1px solid #777; 
            } 
        </style> 
    </head> 
    <body> 
        <?php 
 
        $sql = 'SELECT LastName,FirstName,Title FROM employee WHERE 1=2 ORDER BY LastName'; 
 
        echo '<h2>sqlite</h2>'; 
        $dbSqlite = new DbSqlite($sql); 
        echo $dbSqlite -> displayHtmlTable(); 
 
        class DbSqlite { 
            protected $sql; 
            protected $records = array(); 
            protected $columnNames = array(); 
            public function __construct($sql) { 
                $this -> sql = $sql; 
                $this -> initialize(); 
            } 
 
            protected function initialize() { 
                $db = new PDO('sqlite:chinook.sqlite'); 
                $result = $db -> query($this -> sql); 
                $result -> setFetchMode(PDO::FETCH_ASSOC); 
                $columnsAreDefined = false; 
                while ($row = $result -> fetch()) { 
                    $this -> records[] = $row; 
                    if (!$columnsAreDefined) { 
                        foreach ($row as $columnName => $dummy) { 
                            $this -> columnNames[] = $columnName; 
                        } 
                        $columnsAreDefined = true; 
                    } 
                } 
 
                if (count($this -> records) == 0) { 
                    $total_column = $result -> columnCount(); 
                    var_dump($total_column); 
 
                    for ($x = 0; $x < $total_column; $x++) { 
                        $meta = $result -> getColumnMeta($x); 
                        //var_dump($meta); 
                        //bool(false) 
                        //$column[] = $meta['name']; 
                    } 
                } 
            } 
 
            public function displayHtmlTable() { 
                $r = ''; 
 
                $r .= '<div class="sqlCommand">' . $this -> sql . '</div>'; 
 
                $r .= '<table>'; 
 
                $r .= '<thead>'; 
                $r .= '<tr>'; 
                foreach ($this->columnNames as $columnName) { 
                    $r .= '<td>' . $columnName . '</td>'; 
                } 
                $r .= '</tr>'; 
                $r .= '</thead>'; 
 
                $r .= '<tbody>'; 
                foreach ($this->records as $record) { 
                    $r .= '<tr>'; 
                    foreach ($record as $data) { 
                        $r .= '<td>' . $data . '</td>'; 
                    } 
                    $r .= '</tr>'; 
                } 
                $r .= '</tbody>'; 
                $r .= '<table>'; 
                return $r; 
            } 
 
        } 
        ?> 
    </body> 
 
</html>

请您参考如下方法:

元表 sqlite_master 包含所有信息。以下代码会将 sqlite_master 解析为 $colnames 的列名:

$colnames = array() ; 
 
$stmt = $dbh->prepare("SELECT sql FROM sqlite_master WHERE tbl_name = 'put_table_name_here'") ; 
$stmt->execute() ; 
$row = $stmt->fetch() ; 
 
$sql = $row[0] ; 
$r = preg_match("/\(\s*(\S+)[^,)]*/", $sql, $m, PREG_OFFSET_CAPTURE) ; 
while ($r) { 
  array_push( $colnames, $m[1][0] ) ; 
  $r = preg_match("/,\s*(\S+)[^,)]*/", $sql, $m, PREG_OFFSET_CAPTURE, $m[0][1] + strlen($m[0][0]) ) ; 
}