Re: Cycle thru columns in table?
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Tue, 02 Mar 2010 22:57:24 +0100
Message-ID: <4b8d89c3$0$2864$ba620e4c_at_news.skynet.be>
Rich wrote:
>
> 1 am using 10g.
>
> I need code that returns the column name of the table and the value of
> a row of code. For example, assume I have a table called Customer.
> Then imagine if there is a column called First_Name. My SQL will
> return one record. So that the output might look like
>
> First_Name = Fred
>
> The psudo code might look like
>
> For i = 1 to the last column in table
> column_name := column(i)
> record_value := cursor(i)
> Loop
>
> How can I do the above in Oracle?
Date: Tue, 02 Mar 2010 22:57:24 +0100
Message-ID: <4b8d89c3$0$2864$ba620e4c_at_news.skynet.be>
Rich wrote:
>
> 1 am using 10g.
>
> I need code that returns the column name of the table and the value of
> a row of code. For example, assume I have a table called Customer.
> Then imagine if there is a column called First_Name. My SQL will
> return one record. So that the output might look like
>
> First_Name = Fred
>
> The psudo code might look like
>
> For i = 1 to the last column in table
> column_name := column(i)
> record_value := cursor(i)
> Loop
>
> How can I do the above in Oracle?
An example using PHP, good for any database, I guess. The column names are used as column headers. You can drop the rowcount and bail out after the first row.
$tables =
array( "users", "machines", "games", "runs_on", "owns", "likes");
// loop over tables
foreach( $tables as $table ) {
// loop over rows of database table
$sql = "SELECT count(*) aantal FROM $table";
foreach ($db->query($sql) as $row) {
$rowcount = $row[0];
print "$table contains $row[0] row(s)<br>";
}
// print contents if any
if ($rowcount > 0) {
$sql = "SELECT * FROM $table";
$stmt = $db->query($sql);
print "<table><tr>";
// show column names
for ($i = 0; $i < $stmt->columnCount(); $i++) {
$cmeta = $stmt->getColumnMeta($i);
print "<th>" . $cmeta['name'];
}
print "</tr>";
// print the rows
foreach ($stmt as $row) {
print "<tr>";
// print column by column
for ($i = 0; $i < $stmt->columnCount(); $i++) {
print "<TD>" . $row[$i];
}
print "</tr>";
}
print "</table>";
}
}
Received on Tue Mar 02 2010 - 15:57:24 CST