On Mon, 04 Feb 2008 10:46:53 +0100, Gilles
<[EMAIL PROTECTED]> wrote:
>Hello
>
> I'd like to display the column names as headers in an HTML table.
>
> 1. I need to retrieve the column names, but the following doesn't work:
>
> # cat cols.php
> <?php
> $dbh = new
> PDO("sqlite:db.sqlite");
>
> $sql = ".header ON";
> $dbh->exec($sql);
>
> $sql = "SELECT * FROM customer";
> $row = $dbh->query($sql)->fetch();
> foreach ($row as $col) {
> print $col . "<P>\n";
> }
>
> $sql = ".header OFF";
> $dbh->exec($sql);
>
> $dbh = null;
>?>
You can't use the dot commands with the SQLite
library, they are only valid in the command line tool.
Here is a method in my sqlitedb class,
<?php
/* sqlitedb class */
class sqlitedb extends PDO {
// left out function __construct(){} etc.
/**
* Print ($caption,$sql,FALSE) or
* return ($caption,$sql,TRUE) an HTML table
* representing the full resultset from the
* query in $sql (non-paging) in row/column
* representation.
* Requires classes in css for every datatype
* that might be returned, like:
.datetime { text-align: right; }
.integer { text-align: right; }
.text { text-align: left; }
.varchar { text-align: left; }
.string { text-align: left; }
*
*/
function HTMLtable($caption,$sql,$ReturnString =
FALSE){
$res = $this->query($sql);
if ($res){
if ($row = $res->fetch()){
$nrofcols = $res->columnCount();
if ($nrofcols > 0){
$ret = sprintf('<table
summary="tableview"><caption>%s</caption>
',$caption);
// column horizontal alignment according to type
for ($i = 0;$i < $nrofcols;$i++){
$metadata =
$res->getColumnMeta($i);
reset($metadata);
$class = preg_replace(
array('/[)(0-9]*/')
,array("")
,strtolower(
(array_key_exists ( 'sqlite:decl_type', $metadata )
)?$metadata['sqlite:decl_type']:$metadata['native_type']
));
$ret .= sprintf(' <colgroup
span="1" class="%s"></colgroup>%s',$class,"\n");
}
$ret .= ' <tr>';
// column headings
for ($i = 0;$i < $nrofcols;$i++){
$metadata =
$res->getColumnMeta($i);
$ret .= sprintf(
'<th>%s</th>',$metadata['name']);
}
$ret .= "</tr>\n";
// table rows
while ($row){
$ret .= ' <tr>';
for ($i = 0;$i <
$nrofcols;$i++){
// column data
$ret .=
sprintf('<td>%s</td>',$row[$i]);
}
$ret .= "</tr>\n";
$row = $res->fetch();
}
$ret .= '</table>';
} else {
$ret = 'no columns';
}
} else {
$ret = 'no data';
}
} else {
$ret = 'no table';
}
if ($ReturnString){
return $ret;
} else {
print $ret;
unset($ret);
}
} // HTMLtable()
} // end class sqlitedb
?>
> 2. Additionally, if possible, I'd like to
> display a user-friendly name
> instead of the names I use internally.
<?php
print '<tr><th>col1name</th><th>col2name</th></tr>'
?>
You could fetch the column names from a 'dictionary'
table in your database:
CREATE TABLE Friendlynames (
tablenm TEXT NOT NULL,
columnnm TEXT NOT NULL,
friendlynm TEXT NOT NULL,
PRIMARY KEY (tablenm,columnnm)
);
> Does someone have some code handy?
>
>Thank you.
HTH
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
