查询不返回记录时，如何通过PDO/Sqlite获取列名? [英] How can I get column names via PDO/Sqlite when query returns no records?
本文介绍了查询不返回记录时，如何通过PDO/Sqlite获取列名?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
以下代码允许我将SQL语句传递给类，并调用其方法以显示漂亮的结果表，包括列名.
The following code allows me to pass an SQL statement to a class and call its method to display a nice table of the results, including column names.
但是，如果没有结果，我仍然希望显示列名.
不幸的是，getColumnMeta没有像我发现的其他示例中那样返回任何数据.
Unfortunately, getColumnMeta is not returning any data as it does in other examples I have found.
在此示例中，是否有人知道如何使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:
Meta table sqlite_master contains all information. Following code will parse sqlite_master into column names as $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]) ) ;
}
这篇关于查询不返回记录时，如何通过PDO/Sqlite获取列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
