Entity list | Web apps

Dish deets

For example, suppose we had a DB table called dishes, with data on fave dishes at the Mathieson's. It might look like this:

Dish data

There are three columns. The first one is an id number.

You could writ a program that would get a dish id from the URL, and show the dish's deets. Here's some output:

Dish deets

You can try it.

Here's the code:

<?php
// To connect to a DB, need DB name, user name, and password.
$dbName = 'dishes';
$dbUserName = 'meals';
$dbPassword = 'meals';
// Create DSN.
$dsn = "mysql:host=localhost;dbname=$dbName";
// Connect.
global $dbConnection;
$dbConnection = new PDO($dsn, $dbUserName, $dbPassword);
$errorMessage = '';
// Get the id of the wombat the user wants to see.
$id = getParamFromGet('dish_id');
$errorMessage = checkDishId($id);
if ($errorMessage == '') {
// Prepare SQL.
$stmt = $dbConnection->prepare('SELECT * FROM dishes WHERE dish_id = :id');
// Run it.
$isQueryWorked = $stmt->execute(['id' => $id]);
if (!$isQueryWorked) {
$errorMessage = 'Sorry, something went wrong looking up the dish.';
}
else {
$row = $stmt->fetch();
}
}
?><!DOCTYPE html>
...
<h1>Dish deets</h1>
<?php
if ($errorMessage != '') {
print "<p class='error-message'>$errorMessage</p>";
}
else {
print "<p>Name: {$row['name']}</p>";
print "<p>Comments: {$row['notes']}</p>";
}
?>
<?php
...
/**
* Check a dish id.
* @param int $dishId The id to check.
* @return string Error message, MT if none.
*/
function checkDishId($dishId)
{
$errorMessage = '';
// Missing?
if (is_null($dishId)) {
$errorMessage = "Sorry, dish id is missing.<br>\n";
}
// Errors so far?
if ($errorMessage == '') {
// Is numeric?
if (!is_numeric($dishId)) {
$errorMessage = "Sorry, dish id must be a number, not '$dishId'.<br>\n";
}
}
// Errors so far?
if ($errorMessage == '') {
// Is it big enough?
if ($dishId <= 0) {
$errorMessage = "Sorry, dish id of $dishId is too small.<br>\n";
}
}
// Return error message, if any.
return $errorMessage;
}

Nothing new here. Lines 1 to 10 connect to the DB. Lines 13 and 14 get an id from the URL, and check it. Lines 17 to 25 look up the dish with that id. Lines 31 to 37 output the dish's deets.

Array of fields

Checkout this line:

$row = $stmt->fetch();

PHP puts an array into $row. An array is a collection of different values. In this case, each value is a field. Here's how it looks in memory, and how you access each field:

Array of fields

The variable name doesn't matter. If we did this...

$dog = $stmt->fetch();

...then we'd have:

Array of fields

OK, that's what we get when we fetch one row.

Dish list

The URL of that page has an id at the end:

http://webappexamples.skilling.us/database/dishes/show-dish-deets.php?dish_id=2

We couldn't expect people to type id numbers in, when they wanted to see a dish. It would be better if they had a page with a list of dish names, and could click on the one they want:

Dish list

That's what we'll do.

The list HTML

You can see the list. Give it a trt.

Now check out the HTML. You'll see something like:

<p>Here are some favorite dishes at the Mathieson's.</p>
<ul>
<li><a href='show-dish-deets.php?dish_id=4'>Black Bean Soup</a></li>
<li><a href='show-dish-deets.php?dish_id=2'>Crunchy Crust Za</a></li>
<li><a href='show-dish-deets.php?dish_id=1'>Soup of Doom</a></li>
<li><a href='show-dish-deets.php?dish_id=3'>Spanakopita</a></li>
<li><a href='show-dish-deets.php?dish_id=5'>Spinach Salad</a></li>
</ul>

There's a link for each dish, matching the data in the DB:

Dish data

The order is different. The links are sorted by name. The DB data isn't.

The HTML for the link has the dish's id and name in it. If we set up the variables right, this PHP would make the link:

$link = "<a href='show-dish-deets.php?dish_id=$id'> $name </a>";

Code to make the list page would do this:

Get all of the dish data
For each dish:
$id = dish id
$name = dish name
$link = "<a href='show-dish-deets.php?dish_id=$id'>$name</a>";
print $link

Gimme code!

Here it is:

<?php
// To connect to a DB, need DB name, user name, and password.
$dbName = 'dishes';
$dbUserName = 'meals';
$dbPassword = 'meals';
// Create DSN.
$dsn = "mysql:host=localhost;dbname=$dbName";
// Connect.
global $dbConnection;
$dbConnection = new PDO($dsn, $dbUserName, $dbPassword);
$errorMessage = '';
// Prepare SQL.
$stmt = $dbConnection->prepare('SELECT * FROM dishes ORDER BY name');
// Run it.
$isQueryWorked = $stmt->execute();
if (!$isQueryWorked) {
// Something broke.
$errorMessage = 'Sorry, something went wrong looking up the dishes.';
}
else {
$rows = $stmt->fetchAll();
}
?><!DOCTYPE html>
...
<h1>Dishes</h1>
<?php
if ($errorMessage != '') {
print "<p class='error-message'>$errorMessage</p>";
}
else {
print "<p>Here are some favorite dishes at the Mathieson's.</p>\n";
print "<ul>\n";
// Loop across dishes.
foreach ($rows as $row) {
// Get data for one dish.
$id = $row['dish_id'];
$name = $row['name'];
$notes = $row['notes'];
// Make the link to the deets display page.
$link = "<a href='show-dish-deets.php?dish_id=$id'>$name</a>";
// Output list item with link.
print "<li>$link</li>\n";
}
// End the list.
print "</ul>\n";
}
?>
</body>
</html>

Lines 1 to 10 connect to the DB. Line 13 sets up the query:

$stmt = $dbConnection->prepare('SELECT * FROM dishes ORDER BY name');

There isn't any id here, since we want all the records in the table.

Then we run the SQL:

$isQueryWorked = $stmt->execute();

The execute() doesn't have any parameters. It doesn't need any.

Next, we grab the data:

$rows = $stmt->fetchAll();

Since we get more than one row back, we use fetchAll(), rather than fetch(). fetchAll() returns an array of rows. Each element is an array, the data for one row. Here's what it looks like in memory:

Array of arrays The first item in the $rows array is item 0, the second one is item 1, and so on. Why do they start at 0, and not 1? Because... don't worry about it. It has to do with memory pointers, the C language, and... stuff.

Here's the code that will run through $rows:

foreach ($rows as $row) {
// Get data for one dish.
$id = $row['dish_id'];
$name = $row['name'];
$notes = $row['notes'];
// Make the link to the deets display page.
$link = "<a href='show-dish-deets.php?dish_id=$id'>$name</a>";
// Output list item with link.
print "<li>$link</li>\n";
}

PHP takes the first element in $rows, puts it into the variable $row, and runs the code inside the foreach. Then it takes the next element in $rows, puts it into the variable $row, and runs the code inside the foreach.

Summary

The goal is to make a page of links to deets about entities (wombats, meals, productions, whatevs). Make an SQL query that returns data about all of the entities you want to make links for. Use a foreach statement to loop over the records. For each one, generate a link to a page showing deets for that entity. The link will usually include an id.