Where are we?

This chapter is about creating database Web applications. We need to know what a “database Web application” is.

This lesson’s goals

In this lesson, we:

• Look at the architecture of two Web applications.

• See what they have in common.

• Talk about how you create Web applications.

Let’s look at some examples, where DB tech is key to a Web application.

DogToys, an ecommerce Web site

The goal

DogToys is an online store selling toys, like frisbees, chew ropes, and squeaky balls.

Customers see pages like this:

Figure 1. Product catalog

The catalog is the public face of the business. There’s a lot of activity behind the scenes. DogToys has to:

• Select products people want to buy.

• Decide on prices.

• Write product descriptions.

• Add new products.

• Drop products that aren’t selling.

• Update prices, dropping some and raising others.

DogToys has Web pages to help with these tasks. Customers can’t use these pages, just DogToys’ employees.

For example, here’s a screen that would let someone change a price:

Figure 2. Updating product data

Changing prices is quick and easy. No HTML. Change a number in a form, and click the Save button.

Here is what the customer sees after the change:

Figure 3. The new catalog

Architecture

How to make such an application? It’s easiest if there is one central data store, with all of the information about the products. A database.

The database has a products table, with every price in it, Here’s part of the product data:

Figure 4. Part of the product data in the DB

When a customer visits the catalog Web page, a PHP program fetches the product from the database and shows it.

Figure 5. Customer viewing product data

When a marketer changes a price, s/he fills in an HTML form. The form sends the data to a PHP program that writes the new price to the database.

Figure 6. Marketer changing product data

DogToys has a bunch of PHP programs that all connect to the same database.

Figure 7. The system

One PHP page shows the product catalog. This is all the customers are allowed to see.

Another PHP page updates product data. Another page adds a new product. Another removes a product. Employees use these pages to keep the catalog up-to-date.

The database is at the center of it all. Different PHP pages use the same database to show different people what they need.

Let’s look at another example of a Web application.

DogRock, a content management system

The goal

DogRock is a site about dog rock music. It has articles about new dog bands, albums, concerts, tours, and such.

Here’s a sample article:

Figure 8. An article

People who write for DogRock aren’t Web experts. They don’t know what FTP means, or about nav bars and such. How to let writers add new stories without knowing much about Web tech?

That’s what a content management system (CMS) is for. CMS let nontech people manage Web site content.

When a user goes to the site, s/he sees an article list:

Figure 9. Article list

Suppose Turlough, one of DogRock’s authors, writes a new article. He creates it by filling in a form:

Figure 10. Writing an article

When Turlough has finished writing his story, it will show up on the article list:

Figure 11. New article list

Click on the link, and read the article:

Figure 12. Article

Turlough created the new article by filling in a form on a Web page. He didn’t make an HTML file, or FTP anything.

This is a Big Thing that CMS do: non-tech people can create Web pages.

Architecture

Articles aren’t stored in HTML files. Instead, they’re stored in a database table.

Figure 13. Article table

A bunch of PHP pages let people read articles, write new ones, edit articles, and delete them. Which pages people are allowed to access depends on whether they are a reader or writer.

Figure 14. DogRock architecture

Things in common

Both of these systems – ecommerce and CMS – help different people do different tasks. But they have things in common.

Database

There’s a central database in each one.

CRUD

In both systems, people do four things to data:

• Create (e.g., add a new article)

• Read (e.g., read an article)

• Update (e.g., change a product’s price)

• Delete (e.g., remove a product)

DogToys and DogRock are CRUD systems.

User roles

People do different tasks with the same system, that is, they have different roles. For the ecommerce system, people with the “customer” role do some things (like look at the catalog). People with the “employee” role do different things, like change prices.

With the CMS, there’s the “writer” role and the “reader” role. Writers can create and edit stories. Readers can only read them.

Making a CRUD system

Here are the main steps in making a CRUD Web application:

• Lists the tasks the system should support.

For example, for the DogRock CMS: “Create article, read article, update article, and delete article.”

• Figure out what data is needed to support the tasks.

Each article has a title (“The Barkers to tour”), a publication date (June 15, 2011), and a body (“Those old time rockers, The Barkers, are at it again! The band last toured in 2007, when…”).

• Create the database.

• Write PHP to let people keep the data up-to-date.

add-article.php is a page that lets people add a new article, delete-article.php is a page that lets people erase an article, ...

• Write PHP to let people see the data.

articles.php lists all the articles, show-article.php shows a particular article, ...

Moving forward

Learning how to do all of this at once is too hard. Let’s start with the things that are easy to learn (like making a table), and move towards the more complicated stuff (like updating data).

By the time we get to the complicated stuff, you’ll have so much background that it won’t be so difficult.

Summary

We looked at two examples of Web applications: ecommerce and CMS. For each one, there is:

• A database

• CRUD operations (create, read, update, and delete)

• User roles

To create an application, you:

• Lists the tasks the system should support.

• Figure out what data is needed to support the tasks.

• Create the database.

• Write PHP to let people keep the data up-to-date.

• Write PHP to let people see the data.

What now?

Let’s dig down a little, and see how a PHP page and a database management system (DBMS) work together in a Web application.