1:N | Web apps

Here's the doggos-in-the-neighborhood app again.

Doggos in the neighbornood

Tables, fields, and keys

The database for this app has two tables. First, there's households:

households table

There are two columns. household_id is an integer (whole number). It's the primary key, that is, each row has a unique id. There is only one household 1, one household 2, and so on. There might be two different Mathieson households. They would have the same names, but different keys.

The name is a string.

The other table is doggos.

doggos table

It has three columns. doggo_id the table's primary key. name is the doggo's name. household is the household the doggo is in.

household contains a key from the households table. So Renata and Rosie are in household 1, which is Mathiesons. Tannis is in household 2, which is Sruay.

doggo.household (that is, the household column of the doggo table) is called a foreign key. It links the tables together. Foreign keys are how relationships are stored in relational database systems, like MySQL, Oracle, SQL Server, Access, and others.

Some programs can read the structure of a DB, and diagram it. Here's what phpMyAdmin draws.

phpMyAdmin drawing

phpMyAdmin drawing of the database

Here's what PHPStorm draws.

PHPStorm's drawing

1:N relationships

Here are the tables again.

households table

doggos table

Each doggo can be in only one household. However, a household can have more than on doggo. The Mathiesons household has two.

This is a called 1:N, or one-to-many, relationship. You make a 1:N by taking the primary key of the table on the 1 side, and putting it in the table on the N side.

1:N queries

Say you want to know the name of Warbler's household. Here are the steps:

Find the table and column with the thing you know (that's Warbler, in doggos.name).
Find the table and column with the thing you want to know (that's households.name).
Use keys to find a path between the tables.

Let's walk through it. First:

Find the table and column with the thing you know

We know that the doggo's name is Warbler.

Find the table and column with the thing you want to know

We want to know the household's name. That's households.name.

Use keys to find a path between the tables

There's a path from the foreign key doggos.household, to the primary key households.household_id.

OK, let's turn that into SQL. Let's start with a SELECT template:

SELECT
FROM
WHERE

The first step again: start with what we know, which is that the doggo's name is Warbler.

Find the table and column with the thing you know

SELECT
FROM doggos
WHERE doggos.name = 'Warbler'

Add the condition in WHERE. Put the table in FROM.

Step 2: what we want to know.

Find the table and column with the thing you want to know

SELECT households.name
FROM doggos, households
WHERE doggos.name = 'Warbler'

Put the target field after SELECT, and add the table to FROM.

Step 3: add the path

Use keys to find a path between the tables

Link doggos.household and households.household_id.

SELECT households.name
FROM doggos, households
WHERE doggos.name = 'Warbler'
and doggos.household = households.household_id

Note

This way to join tables is called a key chain. There is another method, using keywords like INNER JOIN. Other profs might want you to use that method in their courses.

I use key chain here since it's easier to understand.

1:N in PHP

Let's put that query into PHP. We'll generalize it, so the program can search for a doggo name the user specifies.

You can try the program, and download it.

The program takes a doggo name from the URL:

...?doggo_name=warbler

It outputs something like:

Household for Warbler

Here's the code:

<?php
// Connect to the database.
require_once 'library/useful-stuff.php';
// Show no errors so far.
$errorMessage = '';
// Get the doggo name.
$name = getParamFromGet('doggo_name');
$errorMessage = checkName($name);
if ($errorMessage == '') {
// Lookup the household.
$sql = "
SELECT households.name as lives_at
FROM doggos, households
WHERE doggos.name = :doggo_name
and doggos.household = households.household_id";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$isWorked = $stmnt->execute(['doggo_name' => $name]);
if (!$isWorked) {
$errorMessage = "Sorry, something went wrong with the database.";
}
else {
$row = $stmnt->fetch();
$householdName = $row['lives_at'];
}
}
?><!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Doggo household</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="library/drawing.css">
</head>
<body>
<div class="container">
<div class="col">
<h1>Doggo household</h1>
<?php
if ($errorMessage != '') {
print "<p class='alert-danger text-danger m-2 p-2'>$errorMessage</p>";
}
else {
print "<p>The doggo $name lives in the $householdName household.</p>";
}
?>
</div>
</div>
</body>
</html>

Mostly. The page uses the Bootstrap framework for styling. Hence the link and script tags, and classes like container. You'll see that if you work through the front-end part of the course.

A new thing is this on line 16:

/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);

Without the comment, PHPStorm complains about $dbConnection:

Undefined variable error

That makes sense. $dbConnection is defined in an included file, like this:

PHPStorm isn't smart enough to look in all the included files, and work out which variables are defined there.

No problem. I tell PHPStorm about the variable with a comment:

Error gone

But wait, there's more!

Now that PHPStorm knows that $dbConnection is a PDO object, it can give me some extra help, like this:

Extra help

It shows me the things it knows $stmnt can do. I can select from the list. W00t!

Validation function

useful-stuff.php has a validation function:

/**
* Check the doggo name.
* @param mixed $name Name from user.
* @return string Error message, MT if OK.
*/
function checkName($name) {
global $dbConnection;
$errorMessage = '';
if (is_null($name)) {
$errorMessage = 'Sorry, need a doggo name.';
}
if ($errorMessage == '') {
// Prepare SQL.
$stmt = $dbConnection->prepare('SELECT * FROM doggos WHERE name = :name');
// Run it.
$stmt->execute(['name' => $name]);
// Anything returned?
if ($stmt->rowCount() == 0) {
$errorMessage = "Sorry, no doggo called $name.";
}
}
// Return results.
return $errorMessage;
}

Nothing new here.

Exercise

1:N Lookup

Earlier, you made your own version of the wombat app. Let's reuse that.

Find a 1:N relationship you can add to your current table. A common one is a category. For example:

Shoes have a manufacturer, where each shoe has one manufacturer, but a manufacturer can make more than one product. Same for cars, and other made things.
Movies have one rating, but more than one movie has a particular rating.
Books have one publisher, but a publisher publishes more than one book.
Courses are offered by one department, but a department offers more than one course.

Make a table for the category with at least three columns:

Id (e.g., manufacturer id, course id). INT, autoincrement, PK.
Name. VARCHAR, you choose the length.
Notes or comment. TEXT

Add a foreign key field to your original table. E.g., shoes might have a manufacturer column.

Add data to your new table. Add links to your original table.

Write an app that GETs the name of a entity from your original table (movie, shoe, course, whatevs), and shows the name of the category, and notes for that category. Like the doggo app takes the name of a doggo, and shows its household.

(Hint: URLs can't have spaces. Use + instead, as in: /url?var=foo+bar)

Submit your URL (one that includes a name to look up), and a zip of your files.

If you were logged in as a student, you could submit a solution to this exercise.

What next?

Let's flip the query. How do you show all the doggos in a household?