How to display all DB column's record in HTML without specifying the column manually?

Question

I want to extract all records from PostgreSQL DB table and display those data using table in HTML without specifying the column name. I have many tables with many columns, so I want my query in HTML to be as responsive as it can.

Using this code below, I'm able to query the data from my database and print the data on browser, but I have to specify which column that I want to display.

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset=utf-8"> 
<title>Map</title>
</head>

<body onload="init()">
<h1>Map</h1>

<div>
<?php
$conn = pg_connect("host=localhost port=5432 dbname=visualization user=postgres password=*******");
$result = pg_query($conn,"SELECT * FROM myTable");
echo "<table>";
while($row=pg_fetch_assoc($result)){
    echo "<tr>";
    echo "<td align='center' width='200'>" . $row['lon'] . "</td>";  // set col manually
    echo "<td align='center' width='200'>" . $row['lat'] . "</td>";  // set col manually
    echo "</tr>";
}
echo "</table>";
pg_close($conn);
?>
</div>
</body>
</html>

Here's the result :

I only display 2 columns because I specified the column manually. I want to display all columns in the table with its records without specifying it manually (up to 100 columns). How to achieve that?

Have a look at pg_field_name in the PHP manual - it shows an example which might be of interest - php.net/manual/en/function.pg-field-name.php — Professor Abronsius
– Professor Abronsius, Commented Mar 3, 2020 at 7:14
One thing to look at is that if you have tables with 100 columns, it may be worth checking your database design in case there is some normalization which needs to be done. — Nigel Ren
– Nigel Ren, Commented Mar 3, 2020 at 7:15

Nigel Ren · Accepted Answer · 2020-03-03 07:11:16Z

1

As your columns are in an array, you can just foreach() over the row. The display of the columns will either need to be good enough or just left as defaults to make the display look reasonable though...

while($row=pg_fetch_assoc($result)){
    echo "<tr>";
    foreach ( $row as $key => $value )  {
        echo "<td align='center' width='200'>" . $value . "</td>";
    }
    echo "</tr>";
}

answered Mar 3, 2020 at 7:11

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iv67 Over a year ago

1 question, why do you need => $value ?

Nigel Ren Over a year ago

You output the $value in the loop, you may not need the $key => part unless you want to use the name of the column in some way.

Illya · Accepted Answer · 2020-03-03 07:12:31Z

1

Try :

while($row=pg_fetch_assoc($result)){
    echo "<tr>";
    $maxColumnDisplay = 100;
    foreach($row as $col){
        if(!$maxColumnDisplay--) break;
        echo "<td align='center' width='200'>" . $col . "</td>";
    }
    echo "</tr>";
}

answered Mar 3, 2020 at 7:12

Illya

1,2751 gold badge7 silver badges16 bronze badges

Comments

Naveen Kumar · Accepted Answer · 2020-03-03 07:13:59Z

1

Try this:

function printTable(&$result) //result set
{
    /**  Set up Table and Headers **/
    $fieldinfo = $result->fetch_fields();
    echo '<table class="sqldump">' . "\n";
    echo '<tr class="header">' . "\n";
    foreach ($fieldinfo as $fieldval) {
        echo "<td>".$fieldval->name."</td>\n";
    }
    echo "</tr>\n";

    /** dump rows **/
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        foreach ($row as $column) {
            echo "<td>".$column."</td>\n";
        }
        echo "</tr>";
    }
    echo "</table>\n";

    /**  gc  **/
    $result->free();
}

answered Mar 3, 2020 at 7:13

Naveen Kumar

1,9961 gold badge10 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to display all DB column's record in HTML without specifying the column manually?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related