Many records | Web apps

In the last lesson, we looked at this DB:

households table

doggos table

There's a one-to-many relationship. A dog can only be in one household, but a household can have many dogs. We represent that by using the primary key of households as a foreign key in doggos.

We then made a query to find the name of Warbler's household.

Query

The SQL was:

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

This query gave us one result, since Warbler can only be in one household.

What dogs are in a household?

Let's flip the question around: what dogs are in the Mathieson household? Here's the data again:

households table

doggos table

You can see there are two: Renata, and Rosie.

But we don't know how to deal with multiple records coming from the DB.

Time to learn how.

The SQL

To make the SQL for the query, let's follow the same steps as before.

Find the table and column with the thing you know.
Find the table and column with the thing you want to know.
Use keys to find a path between the tables.

Here's a query template:

SELECT
FROM
WHERE
ORDER BY

I added an ORDER BY this time, since we'll get multiple records back.

The question is: which dogs are in the Mathieson household?

The first step: Find the table and column with the thing you know.

We know the name of the household. Let's add that to WHERE.

SELECT
FROM households
WHERE households.name = 'Mathieson'
ORDER BY

Next step: Find the table and column with the thing you want to know.

We want to know the dog's names. That's what we'll sort by, as well.

SELECT doggos.name
FROM households, doggos
WHERE households.name = 'Mathieson'
ORDER BY doggos.name

OK, now the last step: Use keys to find a path between the tables.

We start in households, and go to doggos. Let's add that link:

SELECT doggos.name
FROM households, doggos
WHERE households.name = 'Mathieson'
AND households.household_id = doggos.household
ORDER BY doggos.name

Here's another way to look at it.

Start in the households table, and find the rows with 'Mathiesons' in the name field.

Start in households

Grab the id of that household, and track it to the household foreign key in doggos.

Jump to doggos

Now grab values from the name field of those records.

Grab the names

A new HTML tag: ul

We want the output to look like this:

Output

A bulleted list of doggos. There's an HTML tag to make lists like that: ul, the unordered list tags.

Here's the HTML from the page that made the screenshot:

<ul>
<li>Renata</li>
<li>Rosie</li>
</ul>

The entire list is between the start and end uls. Each list item is in a pair of lis.

Changing the query PHP

Not much changes in the PHP that runs the query. Here it is. The variable $householdName contains the household the user wants, taken from the end of the URL.

Just a couple of things to note.

// Lookup the doggos.
if ($errorMessage == '') {
$sql = "
SELECT doggos.name as doggo_name
FROM households, doggos
WHERE households.name = :household_name
AND households.household_id = doggos.household
ORDER BY doggos.name;
";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$result = $stmnt->execute(['household_name' => $householdName]);
// Found any records?
if ($stmnt->rowCount() == 0) {
$errorMessage = "Sorry, couldn't find doggos in the $householdName household.";
}
else {
$rows = $stmnt->fetchAll();
}
}
?><!doctype html>

With this...

SELECT doggos.name

... would the output field be called name, or doggo.name? I wasn't sure, so I named it explicitly, with AS. It'll be called doggo_name. No guessing.

In the examples were the query would return one row, we used this code:

$row = $stmnt->fetch();

Now we might be many rows back. So we change the code to:

$rows = $stmnt->fetchAll();

$rows instead of $row, and fetchAll instead of fetch.

Other than that, the query code hasn't changed.

The output code is different, though.

Output

This is what the query returns for the Mathiesons:

Mathieson doggos

We want PHP to turn that into this HTML:

<ul>
<li>Renata</li>
<li>Rosie</li>
</ul>

Here's some pseudocode:

Print <ul>
For each result row:
Print <li> doggo_name </li>
Print </ul>

OK, let's see what this...

$rows = $stmnt->fetchAll();

... actually gives us. I can check with PHPStorm's debugger.

Two rows

It sends back an array.

An array is a list of things, like a column in a worksheet:

Worksheet

The array is in PHP's memory, though, not on a worksheet.

Each array is in a variable. For example, here is an array of goat names:

Some goats

$goatNames is the entire array. If we want to get an individual value, we need to use an index. So:

$goatNames[0] is Dob
$goatNames[1] is Mug
$goatNames[2] is Mop
$goatNames[3] is Jib

In PHP, the indexes can be strings, too. Here's a TMNT array:

Array with string indexes

Turtle power!

$tmnts['blue'] is Leonardo
$tmnts['purple'] is Donatello
$tmnts['red'] is Raphael
$tmnts['orange'] is Michelangelo

You can even mix the index types.

Mixed indexes

This array has eight elements. You can access the elements by mask color, or number.

What this does...

$rows = $stmnt->fetchAll();

... is make $rows an array with two elements, one for each row.

One element for each row

Adela

Wait, that won't work. What if we'd done a query that sent back more than one field, like:

SELECT doggos.id, doggos.name...

You'd have to shove id and name into one element. Like trying to put two values into the same Excel cell.

Hmm. You have a point. Here's the goat names from before:

Worksheet

Goat names

We couldn't just shove another value, like goat cuteness rating, into the same cell.

Goat names and cuteness

Ethan

In Excel, you'd use another column. So each goat had two a row, with two columns.

Like this. I added column names, too.

Two columns, with names

Adela

Yes! Can $stmnt->fetchAll() make that?

Well...(dramatic music)...it already does!

Here's that debug shot again:

Two rows

$rows has two elements, one for each row returned by the SELECT. Each element is an array of fields.

Ray

Whaaaa...

OK, back to the goats for a mo.

Two columns, with names

Let's take Dob's row, just by itself. We could make an array for it, like this:

Dob's row

If that array was named, say, $x, x['name'] would be Dob, and x['cuteness'] would be 4.

If we made Mug's row into an array:

Mug's row

So, we can make an array for each row in...

Two columns, with names

That would give us four arrays, one for each row. Each of those arrays had two elements each.

Array of arrays

Ray

Oh, OK. So, like, $rows[2] is one record, with all of the columns wrapped up inside it.

Right! This is what fetchAll() returns, an array of arrays.

You could get at the cuteness of that record like this: $rows[2]['cuteness'].

Back to the dogs

$stmnt->fetchAll() gave this:

Debugger output showing $rows is an array

$rows is an array of arrays. Open those arrays up, and you see:

Debugger output showing $rows is an array of arrays

PDO's fetchAll() gives you two ways to access each field, as you can see, by name, or by number. We'll always use the name, since it makes the code easier to follow.

Making the list

Here's what we wanted to make:

<ul>
<li>Renata</li>
<li>Rosie</li>
</ul>

Here's some pseudocode:

Print <ul>
For each result row:
Print <li> doggo_name </li>
Print </ul>

Here's the PHP for that:

print "<ul>\n";
foreach ($rows as $row) {
$doggoName = $row['doggo_name'];
print "<li>$doggoName</li>\n";
}
print "</ul>\n";

This statement...

$rows = $stmnt->fetchAll();

... put an array of records into $rows. We want to go through them one at a time.

This...

foreach ($rows as $row) {
Loop body code
}

... is a loop. foreach() goes through the array you give it, once element at a time.

The first time through the loop, $rows[0] is put into $row, and the body code is run.
The second time through the loop, $rows[1] is put into $row, and the body code is run.
The third time through the loop, $rows[2] is put into $row, and the body code is run.
...

Here's the body code:

$doggoName = $row['doggo_name'];
print "<li>$doggoName</li>\n";

$row is one of the rows. $row['doggo_name'] is one field in the row.

The whole thing

The goal was to let the user ask what doggos were in a household. You can try it, and download the code.

The code:

<?php
// Connect to the database.
require_once 'library/useful-stuff.php';
// Show no errors so far.
$errorMessage = '';
// Get the household name.
$householdName = getParamFromGet('household_name');
$errorMessage = checkHouseholdName($householdName);
// Lookup the doggos.
if ($errorMessage == '') {
$sql = "
SELECT doggos.name as doggo_name
FROM households, doggos
WHERE households.name = :household_name
AND households.household_id = doggos.household
ORDER BY doggos.name;
";
/** @var PDO $dbConnection */
$stmnt = $dbConnection->prepare($sql);
$isWorked = $stmnt->execute(['household_name' => $householdName]);
if (!$isWorked) {
$errorMessage = 'Something went wrong with the look up.';
}
if ($errorMessage == '') {
// Found any records?
if ($stmnt->rowCount() == 0) {
$errorMessage = "Sorry, couldn't find doggos in the $householdName household.";
} else {
$rows = $stmnt->fetchAll();
}
}
}
?><!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>Doggos in 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>Doggos in household</h1>
<?php
if ($errorMessage != '') {
print "<p class='alert-danger text-danger m-2 p-2'>$errorMessage</p>";
}
else {
// There are doggos to show.
print "<p>Doggos in the $householdName household.</p>\n";
print "<ul>\n";
foreach ($rows as $row) {
$doggoName = $row['doggo_name'];
print "<li>$doggoName</li>\n";
}
print "</ul>\n";
}
?>
</div>
</div>
</body>
</html>

Lines 11-32 run a query, and put the results of fetchAll() into $rows@.

Lines 57 to 60 loop over $rows, outputting the doggo_name field for each one.

print "<ul>\n";
foreach ($rows as $row) {
$doggoName = $row['doggo_name'];
print "<li>$doggoName</li>\n";
}
print "</ul>\n";

Getting real

Our programs are getting closer to those used in real business web apps. The doggo list is a report. Users can ask a question is business terms...

What doggos are in household X?

... and get a result that helps them do their work.

Exercise

Wombat list

Show a list of wombat names. Each one is a link to a page on that wombat's deets. You can try my solution.

Here's the list page:

List

The list should be based on data fetched by an SQL query.

Here's a wombat deets page:

Deets page

Hint: check the HTML produced by the list page. It will show you want you're aiming to produce.

Put database connection info in a file outside the web root.
Require that file in a library file.

Here's what my file tree looks like:

File tree

Upload the URL of your solution, and a zip of your files. The usual coding standards apply.

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

What now?

In the last DB lesson, let's look at many-to-many queries.