M:N

Time for a new example. My fave podcast is Nobody Listens to Paula Poundstone. A crinkler, cruiser, comedian, and corraler, with experts guests and carousel of house bands, talk about... whatevs.

Let's make a DB of episodes, and people. We can see which people have been on an episode, and which episodes a person has been on. You can download an SQL file that makes tables, and adds data. Import it into your own DB, if you want it on your own machine.

Here are two of the tables. First, the people table.

people table

There are four fields:

• person_id, the primary key. Required.
• first_name, e.g., Paula. Required.
• last_name, e.g., Poundstone. Optional.
• comments, free text. Optional.

Because of people like Cher. Just one name. She won't return Paula's calls, by the way.

Here's the episodes table:

episode

There are four fields describing each episode:

• episode_id, PK. Required.
• episode_number, the episode number. Required.
• title. Required.
• comment. Optional.

That's the first thing you would think of, but it's not a great idea. Suppose the show's producers decided to add a special episode, and called it "XMAS 2021". That can't be an value for the primary key.

Keep ids as numbers that have no meaning in the outside world. They only link rows in the DB, and that's all.

Connecting people and episodes

We want to know which people were on which episodes. This is an M:N, or many-to-many, relationship, since:

• One person can be on many episodes.
• One episode can have many people on it.

The rule for representing M:Ns is to make a new table, with the PKs from both sides as foreign keys.

Here's that table:

people_in_episodes table

Person 3 was in episode 1. Person 3 was also in episodes 2, 3, and 4. Episode 2 had person 5 in it. Etc.

What we've done is replace the M:N with two 1:Ns. people has a 1:N relationship with people_in_episodes. That is, one record in people can be related to more than one record in people_in_episodes.

Also, episodes has a 1:N relationship with people_in_episodes. That is, one record in episodes can be related to more than one record in people_in_episodes.

Here's phpMyAdmin's drawing of the DB:

A business web app

Now let's write some PHP to get at this data. You can try it. You can also download the code.

Let's see what the app does. Here's the main page:

There's a navbar, and a links to two reports:

• A list of episodes
• A list of people

Here's the list of episodes:

The data comes from the database, obviously. Click on a link, and you'll see the episode's deets.

Let's check out some HTML from the show list page. Here are the links to the first three shows:

The PHP that makes this page, makes links that include some GET data on the end, like episode.php?episode_id=1.

Click on the first link, and you'll see the deets for a show:

Right. I made the browser window narrower for the last screenshot, to capture all of the comments.

When the browser window is wide, like on a desktop or laptop, the navbar looks like:

It stretches out.

When the browser window is narrow, like on a phone or tablet, the navbar shrinks to:

That happens automatically. Bootstrap handles it.

Aye, that it is. You'll learn how to do that later. Your sites will look great on any size screen.

Anyway, back to the episode deets screen.

There's the deets for the episode, and a list of the people on the episode. Each person's name is a link to their deets. For example, click on the second link, and see:

The person's deets, and a list of the episodes the person has been on.

Try the app. Jump around, and explore the data. Fast, easy to use, with a fun look. What you need in a business app.

The episode list page

Here's the episode list page again:

Here's some of the HTML that makes that display.

Let's check out the PHP that makes that HTML.

By line 18, the variable $episodeEntities will be an array of records. Here's part of the array, according to the debugger.

Each element of $episodeEntities is data for one episode. It's an array, with the fields for that episode.

Let's go further down the PHP. Remember, we want HTML like this:

Here's the PHP that makes it:

That means you're learning. Show the code to you as you were at the start of the course...

True dat.

Showing an episode

Here's one of those links we just saw:

Say the user clicks on it. episode.php will run, with as episode_id of 1.

Here's what they'll see:

At the top, there's data about the episode: number, title, and comments. Then there's names of people on the episode.

(BTW, Thomas Coyne didn't make it. Do you know where he is? Let me know, if you do.)

Let's work out the data needed to make the page.

We know we want to show episode 1. That's in the URL: episode.php?episode_id=1. Let's start there (1).

That will give us the number and name of the episode, and it's description, so we can make the HTML for that:

Then we take the episode number, go over to the people_in_episodes table, and get the rows with that episode number (2). That will give us the people ids of 1, 2, and 3.

Then we take the people ids, and find them in the people table (3). Grab those rows, and we'll have what we'll need to make the HTML we need to make links to the each person's page:

(Sorry for my sloppy drawing. I ain't no artist, as you can see.)

Loading the data

Let's start with the code that grabs the data from the database. Here it is.

We follow the same pattern as before. Get the episode id from the URL, by calling getParamFromGet(). Then call a validation function. It's much like the others we've written:

Again, we're using $errorMessage as a flag. If it's MT, no error. If it's not MT, there is a problem, so skip further processing.

Let's go back to the main program. You'll see:

I'm extending our code library. Rather than loading the deets of an episode in the page, I'm moving that code to a reusable function. That's common in business web apps. You extract code for common app tasks, like loading data about business entities.

Here's the code for the new function:

Most of it should be familiar, but there are some wrinkles. (I have wrinkles, too. I'm really old. And tired. Soooo tired.)

The function returns the episode record, rather than an error message. Check out the docblock:

It returns the array from $stmt->fetch() (line 19), with the data about the episode.

But it still has to handle errors. If there's a problem, it returns our ol' friend null.

Let's quickly run through it. The function ends up returning a variable called $result (line 21):

So, the point of the code above it is to set $result. The function starts by making it null (line 8):

If everything goes well, that null will be replaced with data about an episode.

Lines 10-17 create and run an SQL query. Here's line 17:

It has something new there. execute() return a boolean (true/false) value, true if the query worked, false if something went wrong. It doesn't say what went wrong. Maybe Brian from IT spilled vodka into the DB server. Again. He needs an intervention.

Now, the program was already done validation on the id that was passed in, so everything should be OK. So, whatever went wrong, the program can't do anything about it, other than say "something broke."

The next line checks $isWorked, that contains the error flag:

Remember that $result has null in it to start. The null gets replaced if everything went OK. The if handles that.

So, we've expanded our code library, with a reusable function that can load an episode, given an id.

W00t! Praise us! PRAISE US!

Back to the main program, and you can see how our functiony friend is called:

People in an episode

We've got the episode data, but we need to know who was in the episode, so we can make this HTML:

We lookup the ids of the people in the episode, and the data about the people with those ids. Here's my messy drawing again.

Here's the code in the main program that loads the people data:

Right! Another reusable function, in our library. Reuse saves time, and money.

With that money, you could get more dogs, a chocolate fountain, hire Pinkerton to find Thomas Coyne... whatevs.

OMG! Groan! (I like it, though.)

Another functiony friend

Much the same as the last function, with two differences. First, the query is more complex.

• Start with what we know: the episode id.
• Find rows in people_in_episodes with that id: people_in_episodes.episode_id = :episode_id_to_show
• Track that over to people: and people.person_id = people_in_episodes.person_id

Remember: some people don't like this key chain method, and will want to use JOIN.

The second difference is more subtle. Check out line 25:

The last function used fetch(), since it was grabbing only one row. This functiony friend returns a bunch of records, so it uses fetchAll().

Making the HTML

After this is done...

... we have the data needed for the page. $episodeEntity has the DB row about the episode. Title, description, like that. $peopleEntities is an array of DB rows. Each one is a row for one person.

Now we make the HTML, using this data. Here's the code for making the HTML.

As usual, test for errors first (line 4). If there aren't any, then show the basic data for the episode (lines 9-11). If there's people data, then:

Run through the $peopleEntities array, one element at a time. For each one, make HTML to show a link to a page for that person:

Up next

In the last required part of the course, we'll see how to make forms, to help users enter data into a DB.