php pdo populate html with headers

Question

Using PHP PDO: I would like to create and populate html table (thead and tbody) with information obtained using select statement against mysql database.

All done dynamically providing only basic credentials and table name

Answer 1

Home brewed solution

Firstcreate a connection:

function getConnection1(){
    $dbhost = "localhost";
$dbuser = "test";
$dbpass = "test";
$dbname = "schema name";
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbh;
}

Create a function that returns the table:

function retun_html_from_mysql(){

$sql_head = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'schemaname' AND TABLE_NAME = 'table_name';";
$sql_body = "select * from table_name;";

try {
    $db = getConnection1();
    $stmt_head = $db->query($sql_head);
    $stmt_body = $db->query($sql_body);
    $colcount_head = $stmt_head->columnCount();
    $colcount_body = $stmt_body->columnCount();
    $db = null;
    $data =  "<div style='clear: both;'></div>";
    $data .= "<table>";
    $data .= "    <thead>";
    $data .= "        <tr>";
    while ($row = $stmt_head->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
        for ($i=0;$i<$colcount_head;$i++){
            $data .= "<th>$row[$i]</th>";
        }
    }
    $data .= "        </tr>";
    $data .= "    </thead>";
    $data .= "    <tbody>";
    while ($row = $stmt_body->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
        $data .= "<tr>";
        for ($i=0;$i<$colcount_body;$i++){
            $data .= "<td>$row[$i]</td>";
        }
        $data .= "</tr>";
    }
    $data .= "    </tbody>";
    $data .= "    <tfoot>";
    $data .= "        <tr>";
    for ($i=0; $i<$colcount_head;$i++){
        $data .= "<th></th>";
    }
    $data .= "        </tr>";
    $data .= "    </tfoot>";
    $data .= "</table>";        
    echo $data;

} catch (PDOException $e) {
    echo '{"error":{"text":' . $e->getMessage() . '}}';
}


}

Happy Coding!