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);
?>

@Lachie Because i don't want to write html in the backend, i want to keep logic in the back and html in the front — soviet
– soviet, Commented Jul 31, 2018 at 23:56
You need JS for this then, if I understood correctly that is. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 1, 2018 at 1:27

John Darville · Accepted Answer · 2018-08-03 09:10:43Z

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' => '[email protected]'
  ],
  [
    'id' => 2,
    'first_name' => 'Baldrick',
    'last_name' => 'Does he have one?',
    'email' => '[email protected]'
  ]
];

//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

Huh?! what does this have anything to do with their question?
soviet asks how to show a value without using echo because they want to separate business logic from the front end. Get the results from the db and loop over them in a twig template as in my edit. This does what soviets asks, separates html and logic and doesn't use echo. I don't think JS is needed here.
Thanks for making the edit John. Adding a code example helps support your answer, given that your original post only contained a link. If you have a look at https://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers, you will see why they're discouraged. Just trying to help :-)
No, thank you for the feedback. After posting I realised an example would be better. Would've been better still with an example of how to install, initialize and call twig to render the template. I'll bear this in mind.

freddythunder · Accepted Answer · 2018-08-01 00:18:11Z

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>

dmotors · Accepted Answer · 2018-08-01 18:14:10Z

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>

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related