In the previous part you have learned how to send commands (SQL queries) to a database from within a PHP script. This is a very important step because now you know all the core pieces needed to build a web application.
In the previous exercises, you have made a script which prints various lists of
persons using the print_r
function. Although functional, this is plain ugly.
To create a reasonably formtted list, you need to modify your script to output
a HTML page. Although you should already know everything necessary, I feel that
you might appreciate a little guidance.
A big task lies ahead of you. Print first_name
, last_name
, nickname
and
age
rounded to years of all persons ordered by last_name
and first_name
(ascending).
Print the persons in a HTML table, one row each. Use a
layout template for the HTML page.
Again, approach the task in steps, e.g.:
Consult the HTML guide if you are not sure.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Persons List</title>
</head>
<body>
<table>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Nickname</th>
<th>Age</th>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
</table>
</body>
</html>
Create a PHP script, a template and a layout template.
<?php
require 'latte.php';
$latte = new Latte\Engine;
$tplVars['pageTitle'] = 'Persons List';
$latte->render('persons-dynamic-1.latte', $tplVars);
{extends layout.latte}
{block content}
<table>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Nickname</th>
<th>Age</th>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>Johnny</td>
<td>42 years</td>
</tr>
</table>
{/block}
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>{$pageTitle}</title>
</head>
<body>
{include content}
</body>
</html>
Define the persons to be displayed as an array in the PHP script, make sure the array has the same form as the one returned from the database functions.
<?php
require 'latte.php';
$latte = new Latte\Engine;
$tplVars['pageTitle'] = 'Persons List';
$tplVars['persons'] = [
[
'first_name' => 'John',
'last_name' => 'Doe',
'nickname' => 'Johnny',
'age' => '42',
],
[
'first_name' => 'John',
'last_name' => 'Doe',
'nickname' => 'Johnny',
'age' => '42',
],
[
'first_name' => 'John',
'last_name' => 'Doe',
'nickname' => 'Johnny',
'age' => '42',
],
];
$latte->render('persons-dynamic-2.latte', $tplVars);
{extends layout.latte}
{block content}
<table>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Nickname</th>
<th>Age</th>
</tr>
{foreach $persons as $person}
<tr>
<td>{$person['first_name']}</td>
<td>{$person['last_name']}</td>
<td>{$person['nickname']}</td>
<td>{$person['age']}</td>
</tr>
{/foreach}
</table>
{/block}
Write the SQL query and test that it works.
SELECT first_name, last_name, nickname, date_part('years', AGE(birth_day)) AS age
FROM person
ORDER BY last_name ASC, first_name ASC
Modify the PHP script to load the variable from the database.
<?php
require 'latte.php';
$latte = new Latte\Engine;
$tplVars['pageTitle'] = 'Persons List';
try {
$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
exit("I cannot connect to database: " . $e->getMessage());
}
try {
$stmt = $db->query(
"SELECT first_name, last_name, nickname, date_part('years', AGE(birth_day)) AS age
FROM person
ORDER BY last_name ASC, first_name ASC"
);
$tplVars['persons'] = $stmt->fetchAll();
} catch (PDOException $e) {
exit("I cannot execute the query: " . $e->getMessage());
}
$latte->render('persons-list.latte', $tplVars);
No one is forcing you to take all the above steps separately or in the shown order. But you must always be able to divide a complex task into simpler steps. This is really important – the scripts will become only more and more complicated and there is really only one way to be oriented in all the code and debug it. You have to split it into smaller pieces, write and test the pieces individually. Notice how – in the above steps – I have changed only one thing at a time. Some parts (like the template layout) don’t need to be changed at all. However splitting the code requires you to understand the connections between all the code parts:
In this chapter, you have learned how to use SQL queries from within a PHP script and output the result into a HTML page. Because the entire application code is now becoming a bit complex, it is really important that you are able to separate the code into individual parts and test each part individually.