How to show values from a MySQL database table inside HTML via PHP?

Question

This code works but i want to show data but without using echo, i want index.php which will contain HTML to show it, i don't want to be echoing everything like the code below does. This is the PHP code:

<?php
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt select query execution
try{
$sql = "SELECT * FROM persons";
$result = $pdo->query($sql);
if($result->rowCount() > 0){
    echo "<table>";
        echo "<tr>";
            echo "<th>id</th>";
            echo "<th>first_name</th>";
            echo "<th>last_name</th>";
            echo "<th>email</th>";
        echo "</tr>";
    while($row = $result->fetch()){
        echo "<tr>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . $row['first_name'] . "</td>";
            echo "<td>" . $row['last_name'] . "</td>";
            echo "<td>" . $row['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    // Free result set
    unset($result);
} else{
    echo "No records matching your query were found.";
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

Answer 1

Separating the business logic from the presentation is something that frameworks like Symfony or Laravel do very well.

If you don't want to use one of the frameworks, Twig is a template engine for PHP which might be what you're looking for.

Their documentation is pretty good.

https://twig.symfony.com/doc/2.x/intro.html

A quick example of using twig - change the path to suit your system. This is assuming a linux environment.

First, install twig. This will download twig to a directory call vendor in your home directory. In my case /home/john/vendor

 php composer require "twig/twig:^2.0"

Create the following in public_html

twig
├── bootstrap.php
├── index.php
└── templates
    └── index.php

bootstrap.php

<?php
//load the autoloader from the vendor directory

require_once '/home/john/vendor/autoload.php';

//Tell twig where to look for the template files
$loader = new Twig_Loader_Filesystem('/home/john/public_html/twig/templates');

//load twig
$twig = new Twig_Environment($loader);`

index.php

<?php
require_once 'bootstrap.php';
//Your database logic could go here

//Your results. Could be from a database, but I'm using a array for simplicity
$result_set = [
  [
    'id' => 1,
    'first_name' => 'Edmund',
    'last_name' => 'Blackadder',
    'email' => 'eblackadder@blackadder.com'
  ],
  [
    'id' => 2,
    'first_name' => 'Baldrick',
    'last_name' => 'Does he have one?',
    'email' => 'dogsbody@baldrick.com'
  ]
];

//Render the index.php template in the templates directory, assigning the $result_set to result_set
echo $twig->render('index.php', ['result_set' => $result_set]);

templates/index.php

<table>
  <tr>
    <th>id</th>
    <th>first_name</th>
    <th>last_name</th>
    <th>email</th>
  </tr>
{% for result in result_set %}
  <tr>
    <td> {{ result.id }} </td>
    <td> {{ result.first_name }} </td>
    <td> {{ result.last_name }} </td>
    <td> {{ result.email }} </td>
  </tr>
{% endfor %}
</table>

This both separates the back/front end and avoids using echo

Answer 2

Like others have said, without using some template engines or frameworks you would have to use the likes of echo or print.

If you want to keep it on one file and separate the logic that way, the other answers would help you on that.

However that being said, if you wish to separate logic into 2 files to clean it up a bit more then you can do it like so:

mysql.php

<?php

    try {
        $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");

        // Set the PDO error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e){
        die("ERROR: Could not connect. " . $e->getMessage());
    }

    // Attempt select query execution
    try{
        $sql = "SELECT * FROM persons";
        $result = $pdo->query($sql);

        // Close connection
        unset($pdo);

        if ($result->rowCount() > 0){
            return $result->fetchAll();
        } else{
            die("No records matching your query were found.");
        }
    } catch(PDOException $e){
        die("ERROR: Could not able to execute $sql. " . $e->getMessage());
    }

?>

index.php

<!DOCTYPE html>
<html>
    <head>
        <title></title>
    </head>
    <body>
        <table>
            <thead>
                <tr>
                    <th>id</th>
                    <th>first_name</th>
                    <th>last_name</th>
                    <th>email</th>
                </tr>
            </thead>
            <tbody>

            <?php $rows = (include "mysql.php"); foreach ($rows as $row) { ?>

                <tr>
                    <td><?= $row["id"] ?></td>
                    <td><?= $row["first_name"] ?></td>
                    <td><?= $row["last_name"] ?></td>
                    <td><?= $row["email"] ?></td>
                </tr>

            <?php } ?>

            </tbody>
        </table>
    </body>
</html>

Answer 3

You will have to use a string method to output your print or echo to get the meat from your database to your HTML. But you can certainly split your HTML off from the procedural PHP in a few different ways.

A. You can use jQuery and XHR (AJAX) to pull the data from PHP and populate empty HTML shells with jQuery .html(), .clone(), .append(), etc...

B. You put your database results into an array that you can traverse through later in your HTML like:

// backend code
while($row = $result->fetch()){
 $myData[] = $row; // makes a multi-dimensional array of your data
}

// then later in your HTML
<!DOCTYPE html>
...
<table>
 <tr>
  <th>id</th>
  <th>first_name</th>
  <th>last_name</th>
  <th>email</th>
 </tr>
<?php foreach($myData as $values){ ?>
 <tr>
  <td><?=$values['id'];?></td>
  <td><?=$values['first_name'];?></td>
  <td><?=$values['last_name'];?></td>
  <td><?=$values['email'];?></td>
 </tr>
<?php } ?>
</table>