下面的代码允许我将 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]) ) ;
}
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
