Goal: A Web application

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.

How PHP and DBMS work together

Where are we?

You’ve seen an overview of PHP database applications that run two small businesses. We’ll work slowly towards being able to write applications like that.

Let’s see how PHP programs and MySQL work together. Not in detail yet. That comes later.

This lesson’s goals

In this lesson, you will learn:

• There is a database client and a database server (a DBMS).

• The client sends SQL statements to the server. The server returns status codes (like OK) and, sometimes, some data.

• You write PHP programs that create SQL statements, and send them to the server. Each one of these programs is a database client.

Database servers

Remember that a server is software (or an entire computer) that does something for other software (or other computers).

An email server helps email clients. They exchange messages using protocols. Recall that a protocol is a language, a set of standards for things like saying hello and saying goodbye. SMTP is an email protocol.

Figure 1. Email server and client

A Web server helps Web clients (like browsers). They exchange messages in the protocol HTTP.

Figure 2. Web server and client

A database server works with database clients. The client sends requests in the language SQL. The server responds with data and status messages.

Figure 3. DB server and client

Figure 3 is a little different from the others. There’s a client and a server, and they communicate. But often the client and server software are on the same machine. They don’t use the Internet to communicate. Instead, they use something like a socket file to exchange messages. A socket file is a way of sending messages between software running independently on the same computer.

Another difference is the type of client. We will be writing our own clients, in PHP. In the DogRock CMS, each PHP program like add-article.php and delete-article.php is a client. It will generate SQL, and send it to the server.

DB server software is usually called a database management system (DBMS). An individual data set is a database.

Interaction pattern

The pattern for interaction is:

• Client sends an SQL statement.

• Server returns a status code, and maybe some data.

Suppose you want your PHP page to add data to a database.

Figure 4. Adding new data

Your page sends an SQL statement that includes the data to add. The statement would start with the word INSERT. The DBMS executes the SQL, and returns a status code, like “OK.”

If you want your PHP page to delete some data, your page would send a DELETE statement.

Figure 5. Deleting data

To fetch some data from the database, send an SQL statement. This one starts with the word SELECT.

Figure 6. Fetching data

The DBMS returns a status code, and some data. Your PHP can then do something with the data, like show it to the user.

MySQL

There are many different DBMS. The one we’ll talk about is MySQL. Why this one? Because:

• It’s free.

• It’s available on every Unix shared hosting account you would want to use.

• It’s used by many open source (free) Web applications.

• There’s lots of documentation, and many people know how to use it.

MySQL is included with the XAMPP package we talked about earlier. So if you installed XAMPP, you already have MySQL. W00f!

SQL

SQL stands for structured query language. It’s been part of the geek toolkit for about 30 years. Yes, you read that right. SQL has survived for a long time. And it’s not going away anytime soon.

We’ll talk more about SQL throughout this chapter. But let’s start with some examples, so you get the flavor of it.

Remember, don’t get hung up on the details. I just want you to understand the idea of “send SQL, get status and data.”

Suppose we have some dog data. For each dog, we know its name, breed, and weight. Like this:

Figure 7. Dog data

This data is sitting on the DB server.

We could send this SQL query to the server:

select name, weight from dogs;

This means “fetch the name and weight of every dog.” Here’s what the DBMS would return:

Figure 8. Two columns

Renata

Earlier you wrote about SELECT, and now about select. Which is right?

Kieran

Both are. MySQL doesn’t care whether keywords like select and insert are in upper- or lowercase.

It also doesn’t care whether SQL statements have semicolons(;) at the end. Strictly, they should be there, but you can leave them off if you like.

Here’s another query:

select name from dogs where weight > 50;

This says “Give me the names of the dogs whose weight is more than 50.”

The DBMS would return:

Figure 9. Heavy dogs

This shows that a DBMS can filter data, sending back only what the client asks for. In our case, the client will be a PHP program.

Another example:

select name, weight from dogs order by weight;

This gives:

Figure 10. Sorted dogs

The DBMS sorts the data before returning it.

Another example:

insert into dogs (name, breed, weight) values ('Brian', 'Lab', 51);

This adds a new row to the dogs table:

Figure 11. New dog

This shows that SQL statements can change a database, as well as fetch data from it.

One more example:

delete from dogs where weight < 20;

This would remove all of the light dogs. You would get:

Figure 12. No light dogs

PHP creates SQL statements

PHP pages send SQL statements to DBMS. So, to mess with data, you write PHP that writes SQL statements. Then you send the SQL to the DBMS.

For example:

$query = "insert into dogs (name, breed, weight) 
    values ('Brian', 'Lab', 51)";
$db->query($query);

Figure 13. Adding a dog

The first line puts the SQL statement into a variable. The second line sends it to the DBMS.

Here’s another one:

$query = "delete from dogs where weight < 20";
$db->query($query);

Figure 14. Removing light dogs

The first line creates the SQL, and the second one executes it.

What happens when you get a bunch of data back from a database? Remember this query:

select name from dogs where weight > 50;

This gives:

Figure 9 (again). Heavy dogs

You write PHP that to handle each row. The general pattern is:

• Make an SQL query.
• Send it to the DBMS.
• For each row:
  • Get the row data.
  • Do something with it.

Here’s an example. Don’t worry about all of the details; just look for the general pattern. The comments explain what each line does.

//Run the SQL query.
$query = 'select name from dogs where weight > 50';
$record_set = $db->query($query);
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a row.
  $name = $row['name'];
  //Output
  print "<p>Dog name: $name</p>";
}

Figure 13. Showing heavy dogs

Lines 2 and 3 run the query. The DBMS sends back some data, in a table (see Figure 9). The table has one column, and a bunch of rows. Each row is also called a record. The bunch of rows the DBMS returns is called a record set.

Lines 5 to 10 form a loop. The loop runs once for each row in the record set. (Don’t worry about how it works; just know that each record is passed through lines 6 to 9.)

Line 7 gets some data from a record, and puts it into a variable. Line 9 shows the data.

CC

I’m getting worried here. It’s looking awfully complex.

Kieran

I don’t blame you. It does get messy. It’s important to take baby steps. Just one little thing at a time.

That’s what we’ll do. But it won’t work if you skip the exercises. The more difficult the material, the more important the hands-on stuff is.

Summary

• There is a database client and a database server (a DBMS).

• The client sends SQL statements to the server. The server returns status codes (like OK) and, sometimes, some data.

• You write PHP programs that create SQL statements, and send them to the server. Each one of these programs is a database client.

What now?

Let’s look at a tool that can make your database work easier.

Tools for developers

Where are we?

You’ve seen that clients send SQL statements to DBMS. There are tools that write SQL statements for you, saving you some work. Let’s talk about phpMyAdmin, the most widely used tool for managing MySQL databases.

This lesson’s goals

You’ll learn:

• To do database work, you need to send SQL statements to a DBMS. But some SQL statements are long and easy to mess up.

• phpMyAdmin is a PHP Web application that writes SQL for you. phpMyAdmin is part of XAMPP.

• You’ll see how to start MySQL and phpMyAdmin on your computer. You’ll see how to use phpMyAdmin on your hosting account.

• You can create databases on your local computer, and use phpMyAdmin to export your work to your hosting account.

phpMyAdmin

Almost everything you do with MySQL is done by sending SQL commands to the server:

Figure 1. Everything is SQL

When you make Web applications, there’s some work you need to do to get things ready for your PHP pages. You create databases, make tables, add fields (columns), and other things.

This is done with SQL. But doing it all manually can be a pain. You have to type out long commands like this:

CREATE TABLE products (
   product_id int(11) NOT NULL AUTO_INCREMENT,
   'name' char(50) NOT NULL,
   description text NOT NULL,
   image_file_name char(50) NOT NULL,
   price decimal(10,2) NOT NULL,
   PRIMARY KEY (product_id)
);

And you have to get everything just right. You might make a mistake, like this:

CREATE TABLE products (
   product_id int(11) NOT NULL AUTO_INCREMENT,
   'name' char(50) NOT NULL,
   description text NOT NULL,
   image_file_name char(50) NOT NULL,
   price decimal(10,2) NOT NULL,
   PRIMARY KEY (prodct_id)
);

It wouldn’t work, but the problem is hard to spot. Grrrr!

There are tools that make it easier.

The one that most people use is phpMyAdmin. It’s actually just a bunch of PHP pages.

Most Web hosting companies install phpMyAdmin for you. It’s also part of the XAMPP package. If you installed XAMPP, you already have phpMyAdmin installed.

So you will have two copies of phpMyAdmin:

• One on your local computer.

• One on your hosting account.

You also have two different MySQL instances:

• One running on your local machine.

• One running on your hosting account.

Starting MySQL and phpMyAdmin on your local machine

Open the XAMPP control panel. Start both Apache and MySQL:

Figure 2. XAMPP control panel

phpMyAdmin is a bunch of Web pages. It needs Apache (or another Web server) to run.

Start your browser, and go to http://localhost/. You’ll see this on the left:

Figure 3. XAMPP home page

Click the phpMyAdmin link to start it.

Starting phpMyAdmin on your Hostgator account

Log in to your Hostgator control panel. Scroll down to the Databases section:

Figure 4. Starting phpMyAdmin on Hostgator

If you aren’t using Hostgator, you’ll start phpMyAdmin in a different way. Check you hosting company’s help pages.

CC

How do I start MySQL on my hosting account?

Kieran

You don’t. MySQL is always running, just like Apache is always running.

Different versions

The phpMyAdmin installation on your local computer might be a different version from the one on your hosting account. As I write this, I’m running version 2.10.3 on my local machine, and version 2.11.9.5 on my hosting account.

Even the versions of MySQL are different. I’m running MySQL version 5.0.45 on my local machine, and version 5.1.30 on my hosting account.

That’s OK. We’re only covering DB basics. At the level we’re working, having different versions won’t matter. The things we’re looking at have been more-or-less the same for twenty years.

And they’re still current. The core of DB tech has been unusually stable.

From local to remote

In earlier chapters, I recommended that you install XAMPP on your own computer, and write PHP programs there. When everything is working, then you upload the finished programs to your hosting account.

Adding databases complicates things a little, but not much. You can create databases on your local machine, and then move them to your hosting account when you’re ready.

phpMyAdmin can both import and export databases, tables, and other objects. So you can just copy most of your work from your local computer to your hosting account.

I’ll go over an example when we get to creating a table.

Other tools

There are many other tools that can help you work with databases. Earlier, I recommended Netbeans for writing Web pages. Netbeans has an integrated DB tool that works nicely with MySQL.

However, to keep things simple, we’ll only use phpMyAdmin. It’s already installed on both your development (local) and production (hosting account) servers. The same tool works no matter where you are.

Going deeper

You can read a review of Netbean’s database explorer.

Summary

• To do database work, you need to send SQL statements to a DBMS. But some SQL statements are long and easy to mess up.

• phpMyAdmin is a PHP Web application that writes SQL for you. phpMyAdmin is part of XAMPP.

• We saw how to start MySQL and phpMyAdmin on your computer. We saw how to use phpMyAdmin on your hosting account.

• You can create databases on your local computer, and use phpMyAdmin to export your work to your hosting account.

What now?

You’re starting to see how PHP programs can work with databases. Let’s look at DogToys and DogRock again. We’ll go through all of their screens, and see how they fit together.

Sample: DogToys

Where are we?

You’ve seen how PHP pages can send SQL statements to MySQL. We’re going to see how you use this ability to write Web applications.

But first, let’s look at two simple applications. Understanding what they do will help you understand why database stuff is done the way it is.

This lesson’s goals

Recall that DogToys has an online product catalog, with things like squeaky balls, and chew ropes. In this lesson, we’ll look at what the site does. Later, you’ll see how it can be built.

By the end of this lesson, you should:

• Know what the business goals of the site are.

• See how the site’s design achieves those goals.

Web site goals

The ToyDogs site has three main goals:

• Customers can see product data.

• Employees can change product data, quickly and easily.

• Webers can update the Web site, quickly and easily.

Let’s talk about each one.

Customers can see product data

Customers want to be able to see pictures, descriptions, and prices of products. The product list should be easy to get to, from anywhere in the site.

Customers should be able to sort product data the way they want. We’ll let them sort the data by price (low to high, and high to low), and by product name.

Employees can change product data

We want DogToys employees to be able to change product data. The employees who change product data will be from the marketing department. They will not have strong technical skills. They don’t know any HTML.

We’ll create an administration section of the Web site. Employees will be able to fill in forms to change product data.

Webers can update the Web site

We will want to change the site. Maybe change the color scheme, add buttons to the nav bar, change the page footer, etc. Webers should be able to make these changes quickly and easily.

We’ll make a template-based site.

Try it

You can try the site. The admin parts of the site don’t actually update the database.

You can download the code.

For the customer

Time for some screens. Here’s the home page:

Figure 1. Home page

Notice the nav bar on the left. This will be the same on every page. Customers can click on the Products button to see the products catalog. This meets the goal of having the products list available from every page on the site. It’s always just one click away.

Here’s the product catalog:

Figure 2. Product catalog

Each product has a name, picture, description and price.

The user can sort the data. Clicking on the Name link sorts the data by name. Clicking on the up and down arrows next to the price sorts by price.

You can try it.

Administration

The administration section of the site lets employees change product data. The administration section is just a bunch of Web pages.

There is no clickable link from the main part of the DogToys site to the administration section. To get to it, you need to know the URL. Add /admin to the home page’s URL to get to the main administration menu.

CC

That isn’t very secure.

Kieran

You’re right, it isn’t. Later in the book, you’ll learn how to add user names and passwords to sites.

Main administration screen

Here’s the main administration screen:

Figure 3. Main administration screen

There’s a link users can click to add a new product.

All the products are listed. It looks like the product catalog, but each product has an Edit and Delete link.

Adding product data

Here’s the form for adding product data:

Figure 4. Adding product data

There are four fields:

• Product name

• Description

• Image file name

• Price

Most are familiar, but what about the image file name? What’s that about?

We want to show a picture of each product. We’ll take a photo of each one, with a digital camera. We’ll store the image files on the Web site. Let’s put all the photos in a directory called product-images.

How to show the photo on a Web page? With the <img> tag, like this:

<img src="product-images/ Image file name " alt=" Product name ">

Suppose we take a photo of the ball. We name the file with the photo ball.jpg, and put the file into the product-images directory. To show it on the product catalog page:

<img src="product-images/ball.jpg" alt="Squeaky ball">

So the user would type “ball.jpg” into the image file name field in Figure 4.

Image file name is just a regular text field. It’s used to make the <img> tag. The tag itself is just text. But when the browser renders the tag, it will download the image file.

Changing product data

The user clicks a product’s Edit link to edit a product record:

Figure 5. Edit link

This goes to a page like this:

Figure 6. Edit form

It looks like the new product form in Figure 4, but the fields already have data in them.

Deleting product data

When on the main administration page, the user can click the Delete link to remove product data:

Figure 7. Delete link

This shows a confirmation page, to make sure the user really wants to delete the product record:

Figure 8. Confirm delete

The user has to click the Confirm button to actually delete the record.

We’ve talked about two of the site’s goals so far:

• Customers can see product data.

• DogToys employees can change product data, quickly and easily.

Now for the last one.

Changing the Web site

We want to make it easy for Webers to change the site. We’ll use the PHP template approach we talked about before.

So we’ll be using PHP for two things:

• Manage product data.

• Assemble pages from template components.

Summary

We looked at the DogToys Web site. The site lets:

• Customer look at product data.

• Employees easily change product data.

• Webers easily alter the Web site.

What now?

Let’s tour the DogRock site.

Sample: DogRock

Where are we?

Let’s look at another application. Understanding it will help you understand why database stuff is done the way it is.

This lesson’s goals

Recall that DogRock’s site shows articles about dog rock music. It’s a content management system (CMS).

In this lesson, we’ll look at what the site does. Later, you’ll see how it can be built.

By the end of this lesson, you should:

• Know the business goals of the site.

• See how the site achieves those goals.

Web site goals

The DogRock site has three main goals:

• Users can see a list of articles, and read each one.

• DogRock’s writers can add and change articles, quickly and easily.

• DogRock’s Webers can update the Web site, quickly and easily.

You can try the site. The admin pages don’t actually change the database.

You can download a zip file of all of the sites files.

Let’s talk about each goal.

Users can a list of articles, and read each one

Users want to get to new articles quickly. They also want to be able to access a list of older articles.

Each article has a title, a body, an author, and a publication date. Users want to be able to see a list of articles sorted by publication date, so they can see recent stuff. They also want to be able to sort by title and author.

Writers can add and change articles

DogRock’s writers are not computer experts. They can handle some basic HTML tags, like <p>, but that’s about it. They don’t know how to create entire Web pages.

We’ll create an administration section of the Web site. Writers will use forms to add and change articles. We’ll let them delete articles as well.

Webers can update the Web site

We will want to change the site. Maybe change the color scheme, add buttons to the nav bar, change the page footer, etc. Webers should be able to make these changes quickly and easily.

We’ll make a template-based site.

For the users

Time for some screens. Here’s the home page:

Figure 1. Home page

The latest three articles are listed on the home page, from most recent to least recent. You can try it.

Notice the nav bar on the left. This will be the same on every page. Users can click on the Articles button to see a complete article list.

Here’s the article list:

Figure 2. Article list

Clicking on the column names (title, date, and author) sorts the list. Clicking on the title of an article shows the article.

You can try it.

Administration

The administration section of the site lets writers add, edit, and remove articles. The administration section is just a bunch of Web pages.

There is no clickable link from the main part of the DogRock site to the administration section. To get to it, you need to know the URL. Add /admin to the home page’s URL to get to the main administration menu.

Main administration screen

Here’s the main administration screen:

Figure 3. Main administration screen

There’s a link users can click to add a new article.

All the articles are listed. It looks like the article list, but each article has an Edit and Delete link.

Adding an article

Here’s the form for adding an article:

Figure 4. Adding an article

There are three fields:

• Title

• Body

• Author name

Some PHP sets the publication date when the article is saved.

Changing an article

The user clicks an article’s Edit link to edit an article record:

Figure 5. Edit link

This goes to a page like the add article form, but with the fields filled in.

Deleting an article

When on the main administration page, writers can click the Delete link to remove an article:

Figure 6. Delete link

This shows a confirmation page, to make sure the user really wants to delete the article record:

Figure 7. Confirm delete

The user has to click the Confirm button to actually delete the record.

Changing the Web site

We want to make it easy for Webers to change the site. We’ll use the PHP template approach we talked about before.

So we’ll be using PHP for two things:

• Manage article data.

• Assemble pages from template components.

Summary

The DogRock site lets:

• Users see a list of articles, and read each one.

• Writers add and change articles, quickly and easily.

• Webers update the Web site, quickly and easily.

What now?

Each application – DogToys and DogRock – uses a database. Let’s see how you create a database, and then add a table to it.

Creating a database

Where are we?

You’ve seen how PHP programs can send SQL statements to MySQL. You’ve seen two sample applications. Time to get your hands dirty.

This lesson’s goals

By the end of this lesson, you should:

• Be able to create a MySQL database on your local machine.

• Be able to create a MySQL user on your local machine.

• Be able to create a MySQL database on your hosting account.

• Be able to create a MySQL user on your hosting account.

• Be able to write PHP test programs to connect to the databases.

Creating a local database with phpMyAdmin

Start phpMyAdmin on your computer (see this explanation).

You’ll see the phpMyAdmin home page. If you get lost, click the home button to get back to it:

Figure 1. Go to the phpMyAdmin home page

Type in the name of the new database on the phpMyAdmin home page, and click the Create button:

Figure 2. Creating a database

phpMyAdmin will make the SQL statement that creates a database, and send it to MySQL. You’ll see something like this:

Figure 3. Feedback from creating a database

That’s it! You’ve created a database.

There are lots of options you can choose. As usual, CoreDogs only talks about the really important stuff.

Creating a local MySQL user

MySQL uses its own security system, with user names and passwords. When you create a database, the next thing is:

• Create a new MySQL user.

• Give the new user access to the database.

You don’t have to create a new user for each database, but it’s common practice.

Remember this:

MySQL user accounts have nothing to do with other user accounts!

MySQL accounts are used for MySQL access, and nothing else. Not FTP, or email, or Web access. Just MySQL.

Creating a local MySQL user

How you do it is a little different on different versions of phpMyAdmin. But the concepts are the same.

Go to the phpMyAdmin home page:

Figure 1 (again). Go to the phpMyAdmin home page

Depending on your version of phpMyAdmin, either click the Privileges link, or the Privileges tab:

Or

Figure 4. Privileges link or tab

Click the add user link:

Figure 5. Add user link

Fill in the user’s name, host, and password and click the Go button:

Figure 6. Add user

Don’t set any global privileges for the user.

I often make the user name the same as the database name. You can type in your own password, or have MySQL generate one for you. If you do that, make sure you copy and paste the password to a file on your computer. You don’t want to lose it!

What’s that “host” thing? This is which computer(s) the user is allowed to connect from. The right value is almost always localhost, because your Web server and your DB server are running on the same machine. Type in localhost unless your hosting company tells you otherwise.

I’ve only ever run into one hosting company that didn’t use localhost. That was Dreamhost.

Giving a user access to a database

Once you have created the user, you can give the user access to a database. Scroll down to the “Database-specific privileges” section, and fill it in:

Figure 7. Add database privileges

Enter the database you want to give the user access to.

<Begin alternate route>

You can also get to Figure 7 from the home page. Go to the home page:

Figure 1 (again). Go to the phpMyAdmin home page

Then go to the privileges screen:

Or

Figure 4 (again). Privileges link or tab

Click the edit user icon:

Figure 8. Edit user privileges

</End alternate route>

Now that you’ve give the user access to the database, you tell MySQL what that user is allowed to do:

Figure 9. Select privileges

Check the privileges in the figure.

You’re done.

Local testing

But did it work? When you create a new database and user, you should run some PHP to make sure it worked.

Here’s a test page.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
  </head>
  <body>
    <h1>PHP-MySQL connection test</h1>
    <?php
    //DB connection data.
    //Probably leave $host alone.
    $host = 'localhost';
    //Set $db to the name of your database.
    $db = 'dogrock';
    //Set user_name to the name of the MySQL user you made.
    $user_name = 'dogrock';
    //Set $password to the user's password.
    $password = '[Secret password here]';
    ?>
    <p>Trying to connect to MySQL.</p>
    <ul>
      <li>Host name: <?php print $host; ?></li>
      <li>Database name: <?php print $db; ?></li>
      <li>User name: <?php print $user_name; ?></li>
      <li>User password: (Secret)</li>
   </ul>
    <?php
    //Connect to the MySQL server.
    $db = new mysqli($host, $user_name, $password, $db);
    //Did it work?
    if ( mysqli_connect_error() ) {
      print '<p>Error! Could not connect to the database. ';
      print 'Error message: '.mysqli_connect_error().'</p>';
    }
    else {
      print '<p>W00f! It worked!</p>';
    }
    ?>
  </body>
</html>

Figure 10. Test a connection

Line 29 makes the connection. It needs four pieces of data:

• Host name, e.g., localhost

• User name, e.g., dogrock

• User password

• Database name, e.g., dogrock

You type in those values in lines 11 to 18.

Line 31 tests whether the connection worked or not. If not, it shows an error message. If there is no error, line 36 will tell you about it.

Grab this code, and put it somewhere on your local computer.

Here’s what happens when everything works:

Figure 11. Connection OK

Here’s what happens if MySQL is not running:

Figure 12. MySQL not running

The error message is “Can’t connect to MySQL server.”

Here’s what happens if the password is wrong:

Figure 13. Wrong password

If the database name is wrong? Here it is:

Figure 14. Wrong database name

Now you know how to:

• Create a database on your computer.

• Create a user with access to the database.

• Test the connection.

What about your hosting account?

Creating a remote database with phpMyAdmin

When you create a database on your hosting account, you do things a little differently. The exact details depend on your hosting company. I’ll run through the procedures for Hostgator.

Log in to your control panel. Find the database icons:

Figure 14. Control panel database icons

Click the “MySQL databases” icon:

Figure 15. Control panel database icons

Type in the name of the new database and click the button:

Figure 16. Create database

You’ll get some feedback, like this:

Figure 17. Create database feedback

The next thing is important!

The real name of the database might not be the name you typed!

Hostgator prepends (adds in front) your user name to the database name, and chops off extra characters, if there are any. So it your Hostgator user name is woof121 and you type dogrock for your database name, the real name of your database might be woof121_dogrock.

You would use woof121_dogrock as the database name in your PHP programs.

Renata

Why does Hostgator do this?

Kieran

Remember that many sites run on the same server. That what’s “shared hosting” means. There’s one copy of MySQL that everyone shares.

Several people might create a database names books. How is the server to tell them apart?

Renata

Oh, I see. Hostgator changes the names, so they are all different.

Kieran

Right! So if the user lab200 typed in books as the name of a database, the actual name of the database would be lab200_books. If the user pug77 typed in books as the name of a database, the actual name of the database would be pug77_books.

Copy and paste the real name of the database into your code.

Creating a remote MySQL user

Go back to the MySQL database page. Remember, to get there from the control panel:

Figure 15 (again). Control panel database icons

To create a user, scroll down to the Add User form, and fill it in:

Figure 18. Create a user

You’ll get feedback like this:

Figure 19. Create a user – feedback

But this is misleading. The control panel changes the user name the same way it changes the database name. So if lab200 creates the user dogrock, the user is really lab200_dogrock.

Hostgator’s control panel won’t change the password. If you type a password of secret_ThinG_12, then that will be the password you use in your PHP.

Now you need to give the user access to the database.

On the MySQL Databases page, you’ll see something like this:

Figure 20. Giving a user access to a database

Select the user and the database, and click the Add button.

Then you select which privileges the user has:

Figure 21. Setting privileges

Use the ones shown in the figure.

You’ll get feedback like this:

Figure 22. Setting privileges – feedback

Hooray! You’ve:

• Created a database on your hosting account.

• Created a MySQL user on your hosting account.

• Given the user access to the database.

Remember: use the real names of the database and the user. You can see them on the control panel’s MySQL Databases page.

Remote testing

How to test that everything worked? Same as before. Put a file with this code on your hosting account:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
  </head>
  <body>
    <h1>PHP-MySQL connection test</h1>
    <?php
    //DB connection data.
    //Probably leave $host alone.
    $host = 'localhost';
    //Set $db to the name of your database.
    $db = 'dogrock';
    //Set user_name to the name of the MySQL user you made.
    $user_name = 'dogrock';
    //Set $password to the user's password.
    $password = '[Secret password here]';
    ?>
    <p>Trying to connect to MySQL.</p>
    <ul>
      <li>Host name: <?php print $host; ?></li>
      <li>Database name: <?php print $db; ?></li>
      <li>User name: <?php print $user_name; ?></li>
      <li>User password: (Secret)</li>
   </ul>
    <?php
    //Connect to the MySQL server.
    $db = new mysqli($host, $user_name, $password, $db);
    //Did it work?
    if ( mysqli_connect_error() ) {
      print '<p>Error! Could not connect to the database. ';
      print 'Error message: '.mysqli_connect_error().'</p>';
    }
    else {
      print '<p>W00f! It worked!</p>';
    }
    ?>
  </body>
</html>

Figure 10 (again). Test a connection

Replace the database name, user name, and password. Use the real names, like lab200_dogrock. Load the page in your browser, and see what you get.

Summary

In this lesson, you learned how to:

• Create MySQL databases and users on your local machine.

• Create MySQL databases and users on your hosting account.

• Write PHP test programs to connect to the databases.

What now?

Now you have some databases. Time to add a table to each one.

Creating a table

Where are we?

You know how to create a database. Now learn how to add a table to a database.

This lesson’s goals

Learn:

• All data is in tables. Each row is about a single thing, like a dog. Each column is an attribute, like a name or a weight. Each row has the same columns.

• Each field has a data type. MySQL has lots of data types. We talked about INT, DECIMAL, CHAR, TEXT, and DATE.

• Tables have primary keys. A primary key is a field whose values are different for every row.

• Usually you create an INT field to be a primary key. Make it unsigned and auto_increment. MySQL will fill in a unique value.

• Learn how to create a table with phpMyAdmin, and add data to it.

• Learn how to export a table from one database (e.g., one on your local machine), and import it into another (e.g., one on your hosting account).

All data is in tables

All MySQL data is in tables. Here’s an example:

Figure 1. dogs table

The data is in rows and columns.

Each row is data about a single dog. So all the data on the first row is about Francis. A row is also called a record.

Each column is a characteristic of a dog, like the dog’s name, breed, or weight. Columns are also called fields. Every row has the same fields.

Here’s the articles table from the DogRock content management system (CMS):

Figure 2. articles table

Each row is data about one article. Each column in an attribute of articles, like the title.

Here’s the products table from the DogToys product catalog:

Figure 3. products table

Each row is data about one product. Each column in an attribute of products, like the price.

Data types

Each column contains a specific type of data. Here are the data types for the dogs fields.

Figure 4. dogs data types

When you create a table, you tell MySQL the data type of each column. Don’t worry about how you do that just yet; we’ll talk about it soon. For now, let’s focus on the data types.

There are many different data types. The core types are in three categories:

• Characters.

• Numbers.

• Date/time.

Character types

Characters are text, as in “I like the number 96.” Character fields are also called string fields.

You can tell MySQL how long character fields should be. For example…

CHAR(10)

... tells MySQL that a field can have up to 10 characters. Try to put more in the field, and the extra characters will be cut off.

Another character data type is:

TEXT

A TEXT field can have up to about 65,000 characters.

Number types

We’ll only talk about two number types:

• Integers (whole numbers)

• Decimals (numbers with a fixed number of decimal places)

Integer fields can contain whole numbers only, like 7. Try to put 7.32 into an integer, and the .32 will be cut off.

Make integer fields with the INT data type.

Decimals are declared like this:

DECIMAL(10,2)

This is a number that is 10 digits long, with 2 decimal places.

Date/time

MySQL has several date/time types. You can store times down to a fraction of a second, if you want.

We’ll just use the simplest date/time type: DATE. It stores just a date, and not a time.

But how to format a date? In the US, it’s month/day/year, so June 3, 2013 would be 6/3/13. In Australia, you would use day/month/year, as in 3/6/13.

Just as there’s a standard for HTTP, and another one for HTML, and another one for CSS, there’s an international standard for dates. It’s YYYY-MM-DD. So June 3, 2013 would be 2013-06-03.

Most Webers use the standard format when storing dates in a database. That’s what we’ll do. But when we show the dates to people, we’ll use the US format. You can adjust the format, if you like.

Other types

There are many other data types. Other types of strings, other types of numbers, and things that aren’t either. For example, coordinates of a place on the earth, used in mapping programs.

We’ll stick with CHAR, TEXT, INT, DECIMAL, and DATE for now. I’ll explain others if we need them.

Primary key

Here’s the products table again:

Figure 3 (again). products table

Most tables have a column that is the table’s primary key. This uniquely identifies each row in the table.

For products, only one product will have a product_id of 1. Only one product will have a product_id of 2. There will never be any duplicates.

Could the name field be a primary key? No. There might be two products from different manufacturers that have the same name. Maybe two manufacturers make a product they call “Giant chew rope.” They will have different product_ids, so the database can tell them apart.

Here’s the articles table again:

Figure 2 (again). articles table

Two articles could have the same name. For example, Wendy could write an article called “Old Time Howl.” In three years, Bounder could write an article called “Old Time Howl.”

The field article_id takes care of it. Each row has a different value for article_id. It’s the table’s primary key.

Here’s the dogs:

Figure 1 (again). dogs table

What is the primary key? Is it name? That’s the best field we have, but it isn’t very good. Two dogs could have the same name. There might be two Ralphs, for example.

This table isn’t designed well. It would be better if it had a primary key:

Figure 5. dogs table with primary key

Integer primary key

It’s common practice to add an integer field (whole number, remember) to a table, and make it the primary key. That’s what I did with the dogs table.

The first row has a value of 1 for this field. The next row has 2, and so on. Actually, it doesn’t matter what the values are, as long as each one is different. I could make the dog_id of the first row 432, the second row 89, the third row 299, etc. But making them 1, 2, 3, etc., is usual.

When we add a new record to the table, we want dog_id to have a new unique value. We can tell MySQL to do this for us. When we create the table, we can mark an integer primary key field as auto_increment.

When our PHP program adds a new record to dogs, we send the values for each field to MySQL. Like this:

insert into dogs (name, breed, weight) values ('Jamie', 'Scottie', 9);

Notice that we didn’t give a value for dog_id. Since dog_id is an auto_increment field, MySQL will set the value for us. It will take the next value in sequence. So if the last record we added have a value of 3,409, then MySQL will fill in the value 3,410.

Creating a table with phpMyAdmin

Let’s see how you actually add a table to the a database.

I added a humans table to the dog database. It has fields for:

• Primary key.

• Name, e.g., Sarah.

• Weight in pounds.

First, I opened the database in phpMyAdmin. Do this by selecting the database in the drop-down:

Figure 5. Open a database

Then I typed the name of the new table and the number of fields into the Create table form:

Figure 6. Create a new table

Then I clicked the Go button.

Here are the settings for the primary key, human_id:

Figure 7. The human_id field

The data type is INT. I didn’t need to give a length; INT has a standard length, and MySQL knows what it is.

Primary keys are not negative; they could be, but it would be strange. So I made the field UNSIGNED. It’s also set to AUTO_INCREMENT. You should make your primary keys INT, UNSIGNED, and AUTO_INCREMENT.

I marked as human_id the table’s primary key, using the radio button next to the picture of a table with a key on it. Don’t forget to mark your primary keys!

Here are the settings for name:

Figure 8. The name field

It’s set to CHAR and given a length of 20. You always need to give a length to CHAR fields.

Here are the settings for weight:

Figure 9. The weight field

It’s set to INT.

I clicked the Save button to make the table. phpMyAdmin created an SQL statement, and told MySQL to run it. Here’s the feedback I got.

Figure 10. Create table feedback

Adding data with phpMyAdmin

Now I have some tables. How about adding data?

Let’s say I want to add data to the dogs table. First, I open the table.

Figure 11. Opening the dogs table

The I click the Insert tab.

Figure 12. Insert tab

I see a form that lets me add data.

Figure 13. Add record form

I fill in the form, but not the auto_increment field!

Figure 14. Adding data

dog_id, the primary key, is an auto_increment field. MySQL will fill in that value for me.

I click the Save button, and get this feedback:

Figure 15. Feedback on adding data

You can see the SQL INSERT statement that phpMyAdmin created. You can also see the message “Data truncated for column ‘breed’ at…” Why? Because I declared breed as CHAR(10). The value I typed in – Whale hound – is 11 characters long. So the last character was lost.

Here’s what the table looks like with the new record:

Figure 16. New data

You can see the truncated breed.

You can also see that the dog_id is 6. Remember that I didn’t type in that value. Because it’s an auto_increment field, MySQL filled in the value itself.

Exporting to remote

I created the dogs table on my local PC, and added some data. How do I get it on my Hostgator server? I could retype everything, but there’s an easier way:

• On localhost: Export the table to a file.

• On hosting server: Import the file to make a table.

When you export a table in phpMyAdmin (on your local machine), phpMyAdmin recreates the SQL statements that made the table and added the data. Then you can tell phpMyAdmin (the one on your hosting account) to execute those statements.

Exporting

Open the table you want to export:

Figure 17. Open table

Click the Export tab:

Figure 18. Export tab

Make sure SQL is the export format:

Figure 19. Export format

Check “Save as file”:

Figure 20. Save as file

Click the Go button. Your browser will save the file.

Open the file in Notepad++, Netbeans, or some other editor. You’ll see the SQL that phpMyAdmin exported.

Importing

Start phpMyAdmin on your hosting account. If you forget how, here’s a reminder.

Open the database you want to put the table into.

Click the import tab:

Figure 21. Import tab

Click the Browse button:

Figure 22. Browse button

Select the file to import. Click the Go button. phpMyAdmin will grab the file and run the SQL statements inside it.

<?php
//DB connection data.
$host = 'localhost';
$db = 'dogtoys';
$user_name = 'dogtoys';
$password = 'dogtoys';
?>

Summary

• All data is in tables. Each row is about a single thing, like a dog. Each column is an attribute, like a name or a weight. Each row has the same columns.

• Each field has a data type. MySQL has lots of data types. We talked about INT, DECIMAL, CHAR, TEXT, and DATE.

• Tables have primary keys. A primary key is a field whose values are different for every row.

• Usually you create an INT field to be a primary key. Make it unsigned and auto_increment. MySQL will fill in a unique value.

• You learned how to create a table with phpMyAdmin, and add data to it.

• You learned how to export a table from one database (e.g., one on your local machine), and import it into another (e.g., one on your hosting account).

What now?

You’ll need a way to apply your database skills. In the next lesson, you’ll set up the Jokes application. You’ll add to it as we work through the rest of the chapter.

Starting the jokes project

Where are we?

You’ll need a way to apply your database skills. In this lesson, you’ll set up the Jokes application. As we work through this chapter, you’ll add pages to Jokes, until it’s complete.

This lesson’s goals

The Jokes application is much like DogToys and DogRock. It’s a template application, so it uses PHP for more than database work. You can try it.

In this lesson, you’ll:

• Create a database on your computer.

• Create a database user.

• Test the connection.

• Create a table.

• Download the starting files to your machine.

Create the jokes database

Create a database called jokes on your machine. A brief recap of the steps:

• Start Apache and MySQL.

• Open phpMyAdmin.

• Create the database.

Create a database user

Create a MySQL user for the database. You’ll need this to connect you PHP code to the database.

Test

Run the test program we used earlier. Here is the code again:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
  </head>
  <body>
    <h1>PHP-MySQL connection test</h1>
    <?php
    //DB connection data.
    //Probably leave $host alone.
    $host = 'localhost';
    //Set $db to the name of your database.
    $db = 'jokes';
    //Set user_name to the name of the MySQL user you made.
    $user_name = '[User name here]';
    //Set $password to the user's password.
    $password = '[Secret password here]';
    ?>
    <p>Trying to connect to MySQL.</p>
    <ul>
      <li>Host name: <?php print $host; ?></li>
      <li>Database name: <?php print $db; ?></li>
      <li>User name: <?php print $user_name; ?></li>
      <li>User password: (Secret)</li>
   </ul>
    <?php
    //Connect to the MySQL server.
    $db = new mysqli($host, $user_name, $password, $db);
    //Did it work?
    if ( mysqli_connect_error() ) {
      print '<p>Error! Could not connect to the database. ';
      print 'Error message: '.mysqli_connect_error().'</p>';
    }
    else {
      print '<p>W00f! It worked!</p>';
    }
    ?>
  </body>
</html>

Figure 1. Test a database connection

Change the code on lines 12 to 18 to match your database.

Create the jokes table

Create a jokes table in the jokes database. Here are the fields and types I used in mine:

• joke_id – INT, primary key, auto_increment

• title – CHAR(30). A few words reminding me what the joke is about.

• joke_text – TEXT. The text of the joke itself.

• funniness – INT. How funny the joke is, from 1 (not funny) to 5 (hilarious).

• when_added – DATE. When the joke was added to the database.

You can modify this list, if you want.

Add two or three jokes to the table.

Extract the starting files

Rather than asking you to write the entire application from scratch, I’ll give you all of the files with the database stuff removed. The PHP for templating is included.

Download the zip file. Extract it to a directory under your Web root, like c:\xampp\htdocs\jokes.

Try it in your browser.

Look through the files. Remind yourself how templating works.

Summary

You set up the Jokes application. It’s a starting point. You’ll add database code to it as we work though the chapter. You:

• Created a database.

• Created a database user.

• Tested the connection.

• Added a table and some data.

• Extracted the starting files.

What now?

Let’s see how you let users add data to a database.

Adding data

Where are we?

You know how to create databases, add tables, and test connections. Now let’s get our hands (brains?) dirty. Let’s see how you help users add data.

This lesson’s goals

In this lesson, you will learn:

• There are two pages for adding a record: one page with a form the user fills in, and another page that adds the user’s data to the database.

• The SQL INSERT statement does the work.

• Use stripslashes() to remove backslashes that PHP adds to form data.

• Use $db->escape_string() to foil SQL injection attacks.

Adding a new toy

Let’s see how data gets added to the DogToys database.

The big picture

What’s the workflow when a user enters a new record?

First, the user clicks the Add product link on the administration menu.

Figure 1. Main DogToys administration screen

The user sees a form:

Figure 2. Adding product data

The user types in the data, and clicks the Save button. This sends the data to a PHP page that saves the data. That page then goes back to the administration menu.

So the workflow is:

Figure 3. Adding product workflow

• Browser: Show the administration menu.

• User: Click the Add product link.

• Browser: Show a form.

• User: Fill in the form and click the Save button.

• Server: Run a PHP program that saves the data. Then tell the browser to…

• Browser: Show the administration menu.

The form

Here’s the code for the form. I omitted some validation stuff; I’ll add it back in the next lesson.

<form id="new_product_form" method="post" action="save-new-product.php">
  <p>
    Name<br>
    <input type="text" name="name" id="name" size="30">
  </p>
  <p>
    Description<br>
    <textarea name="description" id="description" rows="5" cols="30"></textarea>
  </p>
  <p>
    Image file name<br>
    <input type="text" name="image_file_name" id="image_file_name" size="30">
  </p>
  <p>
    Price<br>
    <input type="text" name="price" id="price" size="10">
  </p>
  <p>
    <button type="submit">Save</button>
  </p>
</form>

Figure 4. HTML for the form

First there’s the <form> tag:

<form id="new_product_form" method="post" action="save-new-product.php">

action tells the browser where to go to save-new-product.php when the user submits the form.

The rest of the code should be familiar. The <input type="text"> tags create one-line input fields. <textarea> creates a multi-line input field.

PHP to insert a record

OK, let’s look at save-new-product.php, the PHP that saves the data. The pattern for the page is:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here is the entire code. We’ll run through it a step at a time.

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 5. save-new-product.php

Let’s break it down into steps.

Lines 2 to 7 are documentation. They explain what the page does, and what input it expects. It’s good practice to add comments like this.

Line 9 sets the variable $path_to_root to the path from save-new-product.php (the file we’re talking about) to the root of the site. This is part of the templating system. Recall that one of the business goals of DogToys was to make the site easy to change. The templating system lets us do that.

Database connection

The first step in the pattern is:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Lines 11 and 12 connect to the database.

You could make a database connection with a line like this:

$db = new mysqli('localhost', 'dogtoys', 'password', 'dogtoys');

We’re going to be making connections to the database in many different PHP pages. The page that adds a record to the database needs to connect to the database. So does the page that saves edited data. And the page that deletes a record. And the product catalog page itself.

All of these pages would have a line like:

$db = new mysqli('localhost', 'dogtoys', 'password', 'dogtoys');

Now, what if we need to change the password? We’d need to find every one of these lines, in every page, and change it.

Ack!

There’s an easier way: put the connection information in a separate file, then include that file when needed. Here are the two lines from save-new-product.php:

require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

Part of Figure 5 (again). save-new-product.php

Line 11 loads db-connect.php. Here’s what’s in it:

<?php
//DB connection data.
$host = 'localhost';
$db = 'dogtoys';
$user_name = 'dogtoys';
$password = 'password';
?>

Figure 6. db-connect.php

Line 12 uses the variables set in db-connect.php to make the connection.

Every page that needs to connect to the database uses db-connect.php.

If I want to change the password? I change one line in db-connect.php, and it’s changed for every page on the site.

Hooray! That’s another productivity win from reuse.

Grabbing the form data to insert

So we have a connection to the database. What’s next? Let’s look at the pattern.

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here’s some more code from save-new-product.php.

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

Part of Figure 5 (again). save-new-product.php

This gets the data the user typed into the form fields, and puts it into variables.

As we talked about earlier, PHP sometimes adds backslashes (\) to data typed into form fields. It’s trying to be “helpful.” The stripslashes() function gets rid of them. It’s good to send every value from the user through stripslashes().

Foiling the Evil Doer

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

The next few lines are:

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

Part of Figure 5 (again). save-new-product.php

We’re going to create an SQL command from the data the user types in. It’s possible for an Evil Doer – one who knows SQL – to make trouble. Unless we do something about it.

Suppose someone typed this into the form:

Figure 7. Evil Doer at work

The DROP statement is an SQL command that erases a table. By inserting quotes, semicolons, and SQL, a clever Evil Doer can make our PHP program do bad things.

This is called an SQL injection attack. You can see the consequences at XKCD.

escape_string() will foil the Evil Doer. It will mess up the quotes, semicolons, and other special characters needed for an SQL injection attack.

Hooray!

So run all form data through escape_string() before you do anything with it.

Creating and running an INSERT statement

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here are the next few lines:

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);

Part of Figure 5 (again). save-new-product.php

This makes an SQL statement, and puts it into the variable $query. I split the statement across several lines to make it easier to read. PHP lets you do this when you use double quotes (”) around the string. SQL doesn’t care that statements are split across lines.

Here’s another example of line splitting in PHP:

$temp = "
   <blockquote>
     <p>I love dogs!</p>
   </blockquote>";

The HTML inside the quotes is easy to follow.

Note all the single quotes (’) on line 29. It’s important to get them right. The field name is a text field, so you need to put quotes around the values you put in it.

The field price is a number. So no quotes for its value.

Line 30 sends the SQL to MySQL for execution.

Back to the administration menu

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

The end of save-new-product.php is:

header('location:index.php');

This tells the browser to jump back to index.php. There’s no directory, just a file name. save-new-product.php is in the /admin directory, so the browser will jump to /admin/index.php.

So that’s it.

• The user fills in a form and clicks its submit button.

• The data is sent to a PHP page:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

W00f!

That was DogToys. Let’s have a look at DogRock.

Adding a new article

Writers add articles to DogRock. Let’s look at the overall workflow.

The big picture

What’s the workflow when a user enters a new record?

First, the user clicks the “Add article” link on the administration menu.

Figure 8. Main DogRock administration screen

The user sees a form. S/he user types in the data, and clicks the Save button.

Figure 9. Adding an article

This sends the data to a PHP page, which saves the data. That page then goes back to the administration menu.

So the workflow is:

Figure 10. Workflow for adding an article

• Browser: Show the administration menu.

• User: Click the Add article link.

• Browser: Show a form.

• User: Fill in the form and click the Save button.

• Server: Run a PHP program that saves the data. Then tell the browser to…

• Browser: Show the administration menu.

The form

Here’s the code for the form. Again, I stripped out some validation stuff.

<form id="new_article_form" method="post" action="save-new-article.php">
  <p>
    Title<br>
    <input type="text" name="title" id="title" size="30">
  </p>
  <p>
    Author<br>
    <input type="text" name="author" id="author" size="30">
  </p>
  <p>
    Body<br>
    <textarea name="body" id="body" rows="10" cols="30"></textarea>
  </p>
  <p>
    <button type="submit">Save</button>
  </p>
</form>

Figure 11. HTML for the form

First there’s the <form> tag:

<form id="new_article_form" method="post" action="save-new-article.php">

action tells the browser to send the data to the page save-new-article.php.

The rest of the code should be familiar. The <input type="text"> tags create one-line input fields. <textarea> creates a multi-line input field.

PHP to insert a record

Let’s look at save-new-article.php, the PHP that saves the data. The pattern is:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

The pattern is the same as before, except for the fourth line.

Here’s the code. I’ll go over it step by step in a moment.

<?php
//Save a new article.
//Input (all POST):
//  title: Title of the article.
//  body: Body of the article.
//  author: Author of the article.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

//Prepare the publish date.
$when_published = date('Y-m-d');

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 12. save-new-article.php

Let’s break it down.

Lines 2 to 6 are documentation. They explain what the page does, and what input it expects.

Database connection

The pattern is:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Lines 10 and 11 connect to the database. As before, the connection parameters (host, user name, etc.) are in a separate file.

Grabbing the form data to insert

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here’s some more code from save-new-article.php.

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

Part of Figure 12 (again). save-new-article.php

This gets the data the user typed into the form fields, and puts it into variables. The stripslashes() function gets rid of the backslashes that PHP adds to form field data.

Foiling the Evil Doer

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here’s the code for that step:

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

Part of Figure 12 (again). save-new-article.php

As before, this messes up SQL injection attack.

Setting the date

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

One of the things we store in the record for each article is the publication date. Rather than making the user type it, we can ask PHP what the current date is, and use that.

Here’s the line that does it:

$when_published = date('Y-m-d');

The date() function gets today’s date. The Y-m-d stuff gives a format: four digits for year, then a dash, then two digits for month, then a dash, then two digits for day. This is the international format that MySQL usually expects dates to be in.

What happens to the formatted date? It goes into the variable $when_published. We can use it just like the variables that contain the form data ($title, $body, and $author).

Creating and running an INSERT statement

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

Here’s the code:

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);

Part of Figure 12 (again). save-new-article.php

This makes an SQL statement, and puts it into the variable $query. Again, the single quotes need to be right on line 29.

Line 30 sends the SQL to MySQL for execution.

Back to the administration menu

What’s next?

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• *Jump back to the admin menu.8

The end of save-new-article.php is:

header('location:index.php');

This tells the browser to jump back to index.php. save-new-article.php is in the /admin directory, so the browswer will jump to /admin/index.php.

So that’s it.

• The user fills in a form and clicks its submit button.

• The data is sent to a PHP page:

• Connect to the database.
• Get the values the user typed into the form.
• Make the values safe.
• Get the current date.
• Create and run an SQL INSERT statement.
• Jump back to the admin menu.

W00f!

Summary

• There are two pages for adding a record: one page with a form the user fills in, and another page that adds the user’s data to the database.

• The SQL INSERT statement does the work.

• Use stripslashes() to remove backslashes that PHP adds to form data.

• Use $db->escape_string() to foil SQL injection attacks.

What now?

You know how to add a record. But what happens when something goes wrong? Let’s see.

Handling errors

Where are we?

Yow know how to help people add new data to a database. But what happens when things go wrong?

This lesson’s goals

We’ve talked about error handling before. Both on the client side and the server side.

Using databases adds more things that can go wrong. In this lesson, we’ll see how you can handle database errors.

We’ll talk about two new types of errors:

• Database connection errors

• SQL errors

You can check for both.

We’ll also talk about simple server-side validation. Even if you can check all data on the client, you should check it on the server as well. To foil Evil Doers.

New types of errors

There are two new types of errors:

• Connection errors

• SQL errors

A connection error is when a PHP program can’t connect to the database. An SQL error is when MySQL says something is wrong with an SQL statement. Like SELECT being typed as SELCT.

Let’s look at each type of error.

Connection errors

We had this earlier:

require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

Figure 1. Connecting to a database

If all is well, $db will be a valid connection object, something your PHP can use to send SQL to the database. But what if you have the wrong password, or user name? Or if the database server is down? Here’s how you check:

$db = new mysqli(...);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}
Do something.

Figure 2. Checking for a connection error

mysqli_connect_error() will be FALSE if there was no connection error. Otherwise, it will contain an error message. So if there was no connection error, the if will skip over lines 4 to 7, and continue with normal processing on line 8.

exit() causes PHP to stop immediately and end the page.

So this is how you handle a connection error. But there are also…

SQL errors

It’s easy for errors to find their way into SQL. For example, suppose I tried to use this to create a new record in the DogRock database:

$db = new mysqli(...);
...
$query = "insert into articles
  (title, author, body, when_published)
  values ($title, $author, $body, $when_published)";
$db->query($query);

Figure 3. SQL error

Can you see what’s wrong?

Quotes are missing. Suppose $title had the value Why I like fish. This:

... values($title, …

would become:

... values(Why I like fish,…

What we want is:

... values('Why I like fish',…

The quotes keep everything together, so the title is treated as one value. When the quotes are missing, MySQL doesn’t know what to do.

Here’s how to check that an SQL query is correct:

$db = new mysqli(...);
...
$query = "insert into articles
  (title, author, body, when_published)
  values ($title, $author, $body, $when_published)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
    </blockquote>";
  exit();
}

Figure 4. SQL error checking

Line 7 is:

if ( $db->error != '' ) {

If there is no error, then $db->error will be empty. If there is an error, then $db->error will not be empty.

If there’s an error, line 8 shows the error message. Lines 9 to 12 show the query that caused the error.

Line 13 stops the program immediately.

Summary so far – There are two new types of errors:

• Database connection errors

• SQL errors

You’ve seen how to test for them.

Renata

So, if you don’t get any errors from the $db->error check, that means that everything is OK?

Kieran

No!

It means that the format of the SQL statement was correct. But you might still have coding errors.

Suppose this was in your PHP:

$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$image_file_name', '$description', $price)";
$db->query($query);

Does that look OK?

Renata

Let me check… Hmm… Wait, the image file name and description are backwards. You have:

(name, description, image_file_name, price)

in one place, and:

('$name', '$image_file_name', '$description', $price)

in another.

Kieran

Right! It’s a bug.

But the format of the SQL statement is just fine. MySQL would detect no errors, and would do what you told it to.

CC

So what’s the point, if it won’t tell you about all of the errors?

Kieran

$db->error will tell you about some of the errors. And that’s better than nothing. But you still need to look in the database, and make sure that your data was added correctly.

Renata

And we could use phpMyAdmin to check the data, right?

Kieran

Yes. You can use phpMyAdmin to check whether your PHP program did what you thought it should.

DogToys: Saving a new product record

Let’s see a new version of save-new-product.php. Recall that save-new-product.php is sent the form data the user types, and saves it to the database.

Here is the code with error checking:

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 5. save-new-product.php

The code starting at line 14 checks whether the connection to MySQL was made successfully.

The code starting at line 37 checks whether the SQL statement ran without error.

DogRock: Saving a new article

Let’s do the same with DogRock.

<?php
//Save a new article.
//Input (all POST):
//  title: Title of the article.
//  body: Body of the article.
//  author: Author of the article.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

//Prepare the publish date.
$when_published = date('Y-m-d');

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. save-new-article.php

It has shiny new error checking code.

CC

I can see how this error checking is needed.

But aren’t most errors made by people? Maybe typing something wrong? How are they checked?

Kieran

Ooo, good question! You’re right, people make more mistakes than computers. Let’s see how to check for those sorts of errors

Validating user data

We looked earlier at how to do validation. Remember that we did some client-side checking, and some server side checking.

We ended up with an architecture like this:

Figure 7. Validation architecture

order.php would do client-side checking with JavaScript. It would also do server-side checking with PHP. order.php would send form data to itself to do any server-side checking. Then, if all was OK, it would send the data to process.php.

We hadn’t written process.php at that point. But now we can write it, storing information to a database.

Let’s see how that will work for our two sample applications

Validating in DogToys

Here’s the input form for adding a new product:

Figure 8. Add product form – add-product.php

What errors can the user make? Not many.

• Leaving any field blank.

• Entering a non-number in price, or a negative number.

We could add other checks, like making sure that the image name is a valid file name. But let’s leave it for now.

Both of these checks can be done on the client side. And I added code to do them to add-product.php. Like this:

...
//Check the name field.
var name = $("#name").val();
if ( name == '' ) {
  data_ok = false;
  show_field_error_message('Sorry, there must be a name.', 'name');
}
else {
  hide_error_message('name');
}
...
<p>
  Name<br>
  <input type="text" name="name" id="name" size="30"><br>
  <span id="name_message_container" class="message_container">
    <img src="<?php print $path_to_root; ?>/library/error.png" alt="Error">
    <span id="name_message"/>
  </span>
</p>
...

Figure 9. Client-side error checking

I used the same approach we used earlier to show error messages. The result is like this:

Figure 10. Client-side error

So all of our validation can be done on the client, in JavaScript.

But we want to add server-side validation anyway, in PHP.

Why?

Security. A smart hacker could grab the code from add-product.php (the page that shows the form). S/he could delete the JavaScript code that did the client-side error checks, and run the modified page. It would accept bad data (like a negative price), and send it to save-new-product.php for storage in the database. If save-new-product.php didn’t check the data, it would the evil data into the database.

So we’ll add some simple validation to save-new-product.php. Nothing fancy, but enough to make sure that evil data doesn’t get into the database.

Here’s yet another version of save-new-product.php.

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Validate
if ( $name == '' ) {
  print '<p>Error! Name is missing.</p>';
  exit();
}
if ( $description == '' ) {
  print '<p>Error! Description is missing.</p>';
  exit();
}
if ( $image_file_name == '' ) {
  print '<p>Error! Image file name is missing.</p>';
  exit();
}
if ( $price == '' ) {
  print '<p>Error! Price is missing.</p>';
  exit();
}
if ( is_nan($price) ) {
  print "<p>Error! Price is not a number: $price.</p>";
  exit();
}
if ( $price <= 0 ) {
  print "<p>Error! Price is too low: $price.</p>";
  exit();
}

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 11. Another version of save-new-product.php

The new stuff starts at line 26. If there’s an error, a message is shown, and the program exits at once. This gives ugly error messages, but that’s acceptable. These errors should never happen, unless either:

• The DogToys application is broken.

• An Evil Doer is attacking.

Line 43 shows the “is not a number” function in PHP:

is_nan()

The syntax is a little different from JavaScript. In JS, it’s:

isNaN() This is JavaScript, not PHP.

But they do the same thing.

You could also use:

if ( ! is_numeric($price) ) {

is_numeric() is TRUE if you give it a numeric value. ! means “not.” So this version of the if says: “if it is not true that $price is numeric.”

Use either isNaN() or ! is_numeric(). Use the one that makes more sense to you.

Line 44 shows a good practice: it outputs the value that is incorrect. This helps in debugging.

Validating in DogRock

Here’s some new code for save-new-article.php:

//Validate
if ( $title == '' ) {
  print '<p>Error! Title is missing.</p>';
  exit();
}
if ( $body == '' ) {
  print '<p>Error! Body is missing.</p>';
  exit();
}
if ( $author == '' ) {
  print '<p>Error! Author is missing.</p>';
  exit();
}

Figure 12. Code for save-new-article.php

It just makes sure that each field has a value.

Summary

We talked about error checking and validation on this page. We saw two new types of errors:

• Database connection errors

• SQL errors

You can check for them both.

We also talked about simple server-side validation. Even if you can check all data on the client, you should check it on the server as well. To foil Evil Doers.

What now?

You know how to add data to the database. But how do you show the data that was added? That’s coming up next.

Showing data

Where are we?

You know how to add data to a table. Now let’s see how you can show it.

This lesson’s goals

In this lesson, you learn that:

• The SQL SELECT statement returns a record set. A record set has one or more rows of data.

• You can loop across a record set, extracting each row. From each row, you extract the fields in the row, and use them to create some HTML.

Showing the DogToys product catalog

The goal

We want to make something like this:

Figure 1. Product catalog

The HTML uses the <table> tag. There’s a row for each product.

<Start table detour>

Let’s have a quick review of <table>s. Suppose we want to make a table like this:

Figure 2. Rendered table

Here’s the HTML code:

<table>
  <thead>
    <tr>
      <th>Food</th>
      <th>Rating</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Bacon</td>
      <td>Yum!</td>
    </tr>
    <tr>
      <td>Brussel sprouts</td>
      <td>Yuck!</td>
    </tr>
  </tbody>
</table>

Figure 3. The <table> tag

The whole thing is wrapped in <table>.

The <tr> tag makes a row. The <td> makes a cell in a row. The “d” in <td> stands for “data.”

<th> makes a cell as well, but with some special formating that’s good for column headers (hence the “h” in <th>).

The <thead> and <tbody> tags separate the table into two regions, header and body. The tags help with styling. For example, you can easily give different background colors to the header and body of the table.

To get the spacing in Figure 2, use the following CSS:

table {
  border-spacing: 10px;
}
td {
  padding: 10px;
}

Figure 4. CSS for table spacing

This puts 10 pixels between each cell (line 2) and 10 pixels between the edges of each cell and its content (line 5).

That’s all you need to know about tables for now. You can read the table chapter if you want to know more.

</ End table detour>

So our PHP will output HTML code with the table tag. Here’s the rendered product page:

Figure 1 (again). Product catalog

Here’s the HTML that will make it.

<tr> 
  <td> 
    <img class='product_image' src='./product-images/ball.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Squeaky ball</p> 
    <p class='product_description'>Bouncing, squeaking fun!</p> 
  </td> 
  <td class='product_price'>1.99</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/frisbee.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Frisbee</p> 
    <p class='product_description'>A whirling disk of pure goodness! From <a href="http://www.wham-o.com/">Wham-O</a>.</p> 
  </td> 
  <td class='product_price'>10.95</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/rope.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Giant chew rope</p> 
    <p class='product_description'>Nom nom nom, and nom again. Chew this, not human shoes. You know how touchy they are.</p> 
  </td> 
  <td class='product_price'>12.95</td> 
</tr>

Figure 5. HTML created by PHP

The pattern

Here’s the pattern for PHP that will take data from a MySQL table, and make HTML to show it:

• Create a SELECT statement. SELECT is the SQL command to grab data.

• Execute the SQL, and get back a record set. A record set is the result of a SELECT. Could be one record, or five, or 1,289.
• For each record in the record set:
  • Get the fields from the records.
  • Show the fields.

The code

Here’s the code that will generate the HTML to show the product list. To make the code easier to follow, I’ve omitted some stuff having to do with sorting. I’ll add it back in the next lesson.

I’ve also omitted error checking, e.g., checking that the database connection was successful. That would clutter things, making it harder for you to focus on the core ideas of this lesson.

Here’s the code. We’ll go through it piece by piece.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "
  select product_id, name, description, image_file_name, price
    from products";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $product_id = $row['product_id'];
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>
  </tbody>
</table>

Figure 6. PHP to show the product catalog

Lines 3 and 4 open the database. As usual.

Lines 6 to 8 create the SQL query that fetches the data. I broke it across lines to make it easier for me to read. The statement has the form:

SELECT fields FROM table;

When you send this to MySQL:

select name, description, image_file_name, price from products;

You are telling MySQL:

Open up the products table, and, for each row, fetch name, description, image_file_name, and price.

Here’s what you get:

Figure 7. A record set from MySQL

Renata

Hmm, that looks like part of a phpMyAdmin screen.

Kieran

It is. I copied the SQL query from my code, clicked the SQL tab in phpMyAdmin, and pasted in the query. Clicked the Go button, and MySQL ran it.

That’s a good way to test your own queries.

Once the query has been created, it gets run in line 10:

$record_set = $db->query($query);

query() returns a record set, that gets put into the variable $record_set. The record set is this:

Figure 7 (again). A record set from MySQL

It’s the results of running the query.

We’re used to a variable containing something simple, like a number or some text:

$x = 42;
$y = 'The answer!';

But $record_set is different. It contains a complex object.

We don’t care too much what an object really is. Just think of it as a blob of data. There can be all kinds of data in an object.

You need special functions to get at the data inside an object. We’ll see some in a moment.

OK, so we’ve:

• Created an SQL query.

• Sent it to MySQL.

• Gotten an object back. The object is a record set, with rows and columns.

Now we need to show the records in a <table>. There’s some code in Figure 6 to output the <table> tag and the header:

<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>

Part of Figure 6. PHP to show the product catalog

Line 16 is a little strange.

<th>&nbsp;</th>

The first column in the output table has the item’s picture. It doesn’t need a heading. So line 16 creates an empty heading cell.

Here’s the next part of the code, where the meat is:

<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>

Part of Figure 6. PHP to show the product catalog

while is a new PHP statement for us. It’s a loop. It executes the same PHP statements a bunch of times.

Its format is:

while( test ) {
  Do this
}

It says:

Keep doing “Do this” while the test is true.

Here’s an example:

<?php
$x = 1;
while ( $x <= 5 ) {
  print "$x<br>";
  $x += 1;
}
?>

Figure 8. while loop example

This says:

While $x is less than or equal to 5:
     Output $x and a line break tag.
     Add 1 to $x.

The PHP engine will keep doing lines 4 and 5 while the condition is true.

Line 5 is a common PHP shortcut. += means “add to.” So:

$x += 1;

means “Add 1 to what is already in $x.”

Here’s the HTML the code generates:

1<br>2<br>3<br>4<br>5<br>

Here’s how it renders:

Figure 9. Rendered output

Let’s have another look at the loop in our database program:

while( $row = $record_set->fetch_assoc() ) {
     Stuff to do
}

The PHP engine will keep looping around, doing “Stuff to do” while $row = $record_set->fetch_assoc() is true. When $row = $record_set->fetch_assoc() stops being true (when it is false), the PHP engine stops the loop. It will skip to the code after the loop.

Let’s look at:

$record_set->fetch_assoc()

Recall that $record_set is a record set object. It contains this data:

Figure 7 (again). A record set from MySQL

fetch_assoc() is a function that returns one row.

When a function belongs to an object, it’s really called a “method.” But let’s keep calling it a function for simplicity.

A record set object has other data besides some rows. It also has a record pointer:

Figure 10. Record pointer

The record pointer starts off pointing to the first record.

When you use the fetch_assoc() function, you are saying two things:

• Give me the current record (the one the pointer points to).

• Move the pointer down one record.

What does “Give me the current record” mean? Here’s the code again:

$row = $record_set->fetch_assoc()

fetch_assoc() copies $record_set’s current record into $row. fetch_assoc() doesn’t remove the data from $record_set. It’s still there. fetch_assoc() copies the row, and sends it to $row.

Then fetch_assoc() moves the pointer down. So after fetch_assoc() has run once, we would have:

Figure 11. fetch_assoc() has run once

fetch_assoc() has returned the record it was pointing to, and moved the pointer down one record.

Run fetch_assoc() a second time, and you get:

Figure 12. fetch_assoc() has run twice

Run fetch_assoc() a third time, and you get:

Figure 13. fetch_assoc() has run three times

The record pointer has moved beyond the end of the table.

Run fetch_assoc() a fourth time, and you get:

Figure 14. fetch_assoc() has run four times

The record pointer is pointing to an empty space, so $row = $record_set->fetch_assoc() puts NULL into $row. NULL acts like FALSE in PHP’s if(), while(), and other statements.

Here’s the loop again:

while( $row = $record_set->fetch_assoc() ) {
     Stuff to do
}

This will do “Stuff to do” again and again. The first time, $row will have the first record. The second time, $row will have the second record. And so on.

The last time, $row will have the last record. When the while() tries to run again, fetch_assoc() will return NULL, which acts like FALSE. So the loop will stop. Execution will pick up after the brace (}) that ends the loop.

What we have now is a way to grab each record in the record set. Each one gets put into $row. But what do we do with $row?

Remember our goal. We want to create a <table> that contains rows like this:

<tr> 
  <td> 
    <img class='product_image' src='./product-images/ball.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Squeaky ball</p> 
    <p class='product_description'>Bouncing, squeaking fun!</p> 
  </td> 
  <td class='product_price'>1.99</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/frisbee.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Frisbee</p> 
    <p class='product_description'>A whirling disk of pure goodness! From <a href="http://www.wham-o.com/">Wham-O</a>.</p> 
  </td> 
  <td class='product_price'>10.95</td> 
</tr> 
<tr> 
  <td> 
    <img class='product_image' src='./product-images/rope.jpg'> 
  </td> 
  <td class='product_name_description_container'> 
    <p class='product_name'>Giant chew rope</p> 
    <p class='product_description'>Nom nom nom, and nom again. Chew this, not human shoes. You know how touchy they are.</p> 
  </td> 
  <td class='product_price'>12.95</td> 
</tr>

Figure 5 (again). HTML created by PHP

Lines 1 to 10 are for the first product. They were created from the first row in the record set:

Figure 7 (again). A record set from MySQL

The second row in the table was created from the second row in the record set.

Here’s the entire loop again:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.

Part of Figure 6 (again). PHP to show the product catalog

This from line 24:

$row = $record_set->fetch_assoc()

puts a row from the record set into $row.

Remember that $record_set doesn’t have a single value, like 7 or bark. It’s an object, with a bunch of data.

@$row$ doesn’t contain a single value, either. But it isn’t a record set object. It’s an array. We don’t care right now about arrays. Just know that an array contains a bunch of values that you can access.

Remember the SQL query used to create the record set:

select name, description, image_file_name, price from products

So record (row) in the record set has the fields name, description, image_file_name, and price. When:

$row = $record_set->fetch_assoc()

puts the current record in $row, it puts four values into $row.

Here are the next few lines of code:

//Get fields for a product.

$name = $row[‘name’];

$description = $row[‘description’];

$image_file_name = $row[‘image_file_name’];

$price = $row[‘price’];

You can access array elements like this:

$array_name['element name']

So…

$name = $row['name'];

... gets the name element from $row and puts it into the variable $name. $name is not a complex object. It’s just a variable with a simple string in it.

We end up with four variables containing the name, description, image file name, and price for the current record.

Then we can output the variables:

//Output
print "
  <tr>
    <td>
      <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
    </td>
    <td class='product_name_description_container'>
      <p class='product_name'>$name</p>
      <p class='product_description'>$description</p>
    </td>
    <td class='product_price'>$price</td>
  </tr>";

Part of Figure 6 (again). PHP to show the product catalog

This PHP statement outputs the fields, inserting them into some HTML, to create a table row.

Here’s all the code again:

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "
  select product_id, name, description, image_file_name, price
    from products";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table class="product_table">
  <thead>
    <tr>
      <th>&nbsp;</th>
      <th>Name</th>
      <th>Price</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for a product.
  $product_id = $row['product_id'];
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img class='product_image' src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td class='product_name_description_container'>
        <p class='product_name'>$name</p>
        <p class='product_description'>$description</p>
      </td>
      <td class='product_price'>$price</td>
    </tr>";
} //End while.
?>
  </tbody>
</table>

Figure 6 (again). PHP to show the product catalog

You should be able to follow it now.

Lines 3 and 4 connect to the database.

Lines 6 to 10 run a SELECT query, fetching a record set from MySQL.

Lines 13 to 21 output the table header.

Lines 24 to 43 loop across the record set, showing a table row for each product.

Lines 45 and 46 close the table’s HTML.

W00f!

That’s a lot of new stuff we’ve covered. If you need a brain break, take one.

Let’s take a look at the second sample application, the DogRock CMS.

Showing the DogRock article list

We want to make something like this:

Figure 15. Article list

Here’s part of the HTML that shows the table:

<tr> 
  <td>A new howl on the prowl</td> 
  <td>February 5, 2010</td> 
  <td>Lumis</td> 
</tr> 
<tr> 
  <td>Somebody let them out!</td> 
  <td>February 3, 2010</td> 
  <td>Bounder</td> 
</tr> 

Figure 16. HTML for the article list

I’ve omitted some stuff for simplicity. It’ll come back later.

Here’s the PHP that creates the HTML that makes the page.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "
    select title, author, when_published
    from articles";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="10" cellspacing="10" border="0">
  <thead>
    <tr>
      <th>Title</th>
      <th>Date</th>
      <th>Author</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $title = $row['title'];
  $when_published = $row['when_published'];
  $author = $row['author'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  //Output
  print "
    <tr>
      <td>$title</td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while
?>
    </tbody>
  </table>

Figure 17. Article list code

Lines 3 and 4 connect to the database.

Lines 6 to 8 create an SQL query:

select title, author, when_published from articles

This says:

From the articles table, fetch the fields title, author, and when_published for every record.

Why “every record?” Because there’s no WHERE clause. Like this:

select title, author, when_published from articles where author='Bounder'

This would fetch data only for the rows that have Bounder in the author field.

But we don’t have a WHERE clause, so all rows are returned.

Lines 13 to 21 output the table header and such.

The fun starts here:

//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $title = $row['title'];
  $when_published = $row['when_published'];
  $author = $row['author'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  //Output
  print "
    <tr>
      <td>$title</td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while

Part of Figure 15. Article list code

Line 24 fetches a row from the record set. Lines 26 to 28 fetch fields from the row into variables.

Line 30 formats the date in $when_published. The format in the database is YYYY-MM-DD, like 1960-01-03 (my birthday – yes, I’m old). This is the standard international format, but most people aren’t used to it.

This code…

date('F j, Y', strtotime($when_published))

... uses the date() function to format the date in the common “Month Day, Year” format, such as “January 3, 1960.” date() expects the date you give it to be in a certain format – a Unix timestamp. strtotime() converts a YYYY-MM-DD date (from the database) into a Unix timestamp (that the date() function needs).

The output from the date() function is put back into $when_published:

$when_published = date('F j, Y', strtotime($when_published));

The last few lines show the record:

//Output
print "
  <tr>
    <td>$title</td>
    <td>$when_published</td>
    <td>$author</td>
  </tr>";

So that’s it! We now have a list of articles. W00f!

There’s something missing here, though. Here’s the output we want:

Figure 15 (again). Article list

We want the name of the article to be a clickable link. We’ll look at that later.

Summary

• The SQL SELECT statement returns a record set. A record set has one or more rows of data.

• You can loop across a record set, extracting each row. From each row, you extract the fields in the row, and use them to create some HTML.

What now?

Let’s see how you can let users sort the data.

Showing sorted data

Where are we?

You know how to show users the data in a database. What if they want to sort the data? Let’s see how you do that.

This lesson’s goals

In this lesson, you will learn:

• The ORDER BY clause of the SELECT statement tells MySQL to sort records in the record set.

• Let users choose the sort order by adding a GET parameter to links. Add code that will append different fields to ORDER BY, depending on the parameter.

• The LIMIT clause of the SELECT statement sets the maximum number of records MySQL will add to a record set.

The ORDER keyword

Recall that the SQL SELECT command returns a bunch of rows:

select name, description, image_file_name, price from products;

You can tell MySQL how to sort the returned rows. Add ORDER BY and then the field to sort by.

For example, this…

select name, description, image_file_name, price from products order by name;

... returns:

Figure 1. Sorted by name

This…

select name, description, image_file_name, price from products order by image_file_name;

... returns:

Figure 2. Sorted by image_file_name

This…

select name, description, image_file_name, price from products order by price;

... returns:

Figure 3. Sorted by price

By default, the records are sorted in ascending order, from smaller to larger. But you can change this. For example, if you want to sort by price from high to low:

select name, description, image_file_name, price from products order by price desc;

I added desc for “descending” after the sort field name. This returns:

Figure 4. Sorted by price, descending

Sorting is not difficult. But it’s trickier to…

Let the user choose the sort order

We want to let the user choose how to sort the data. Here’s the interface for the product list:

Figure 5. Sort interface for products

The user can click on the product name to sort by name, or click on an arrow to sort by price, from low to high, or high to low.

You can try it.

How does this work? The URLs give a clue. Here’s how the URL changes when the user clicks on the “Price” link.

Figure 6. User sorting by price

The URL of the page is normally products.php. This gives a default sort order, by name. Then the user clicks the sort arrow. The HTML for the arrow is:

<a href="products.php?order=price_asc"> 
  <img src="./library/arrow_down.png"
    alt="Sort from low to high"> 
</a>

Figure 7. HTML for a sort arrow in products.php

Look at the href in line 1 of Figure 7:

products.php?order=price_asc

So the page points to itself, but with a parameter order.

When this renders (Figure 6), the user sees the data sorted ascending by price.

For this to work, we need:

• A way for the page to get a sort order from the URL.

• A way to apply the sort order.

• Links that send the sort order to the page, as in Figure 7.

Let’s look at the code for DogToys.

Sorting the DogToys product list

Here’s products.php:

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "
  select product_id, name, description, image_file_name, price
  from products
  order by ";
//Append the sort order.
$order = $_GET['order'];
if ( $order == 'price_asc' ) {
  $query .= 'price asc';
}
else if ( $order == 'price_desc' ) {
  $query .= 'price desc';
}
else {
  //Default sort order.
  $query .= 'name';
}

//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="5" cellspacing="0" border="0">
  <thead>
    <tr>
      <th> </th>
      <th><a href="products.php?order=name">Name</a></th>
      <th>Price<br>
        <a href="products.php?order=price_asc">
          <img src="<?php print $path_to_root; ?>/library/arrow_down.png"
               alt="Sort from low to high">
        </a>
        <a href="products.php?order=price_desc">
          <img src="<?php print $path_to_root; ?>/library/arrow_up.png"
               alt="Sort from high to low">
        </a>
      </th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
...

Figure 8. products.php

I’ve omitted some code for simplicity.

We need to add the ORDER BY to the SQL. That’s what lines 9 to 21 do.

Line 9 adds “order by” to the SQL statement. But the statement is not complete. Order by what? That’s what the next few lines figure out.

Line 11 …

$order = $_GET['order'];

... fetches the value of the GET parameter order, if there is one. Recall that GET passes data to a Web page through the URL. The value of order is put in the PHP variable $order.

Here are the next few lines (lines 12 to 14):

if ( $order == 'price_asc' ) {
   $query .= 'price asc';
}

Recall that .= means “append” or “concatenate.” In normal words, “add to the end of.” So id the variable $order contained price_asc, then price asc would get appended to the SQL statement. It would end like this:

... order by price asc

Now the order by clause is complete. When the SQL statement is run, the records will be sorted by price in ascending order.

Lines 15 to 17 append price desc to the SQL statement, if $order contains price_desc:

if ( $order == 'price_desc' ) {
   $query .= 'price desc';
}

If $order contains anything else, or nothing at all, then line 20 sets the default sort order to name:

$query .= 'name';

The SQL query now has a complete ORDER BY clause, so MySQL will sort the data before returning it.

The last thing we need is the interface, that is, a way for the user to select the sort order. That’s what lines 31, 33, and 37 do:

<a href="products.php?order=name">

<a href="products.php?order=price_asc">

<a href="products.php?order=price_desc">

Clicking these links will cause products.php to reload itself, but with a different sort order.

Sorting the DogRock articles list

Here’s the code for articles.php:

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "select article_id, title, author, when_published
    from articles
    order by ";
//Append the sort order.
$order = $_GET['order'];
if ( $order == 'date' ) {
  $query .= 'when_published desc';
}
else if ( $order == 'author' ) {
  $query .= 'author';
}
else {
  //Default sort order.
  $query .= 'title';
}
//Run the query.
$record_set = $db->query($query);
//Start the output table.
?>
<table cellpadding="10" cellspacing="10" border="0">
  <thead>
    <tr>
      <th><a href="articles.php?order=title">Title</a></th>
      <th><a href="articles.php?order=date">Date</a></th>
      <th><a href="articles.php?order=author">Author</a></th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
...

Figure 9. articles.php

It works the same way as products.php. Clicking a link (lines 28, 29, and 30) causes articles.php to reload itself, passing a sort order.

Line 10 gets the order from the URL.

Lines 11 to 20 complete the SQL statement, depending on which sort order the user selected.

The DogRock home page

The DogRock site also shows articles on its home page. It shows the three most recent:

Figure 10. Most recent articles

They are sorted descending by date, so the most recent article is first.

Here’s the code for the page, with some things omitted.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch the three most recently published articles.
$query = "select article_id, title, when_published
    from articles
    order by when_published desc
    limit 3";
$record_set = $db->query($query);
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  $title = $row['title'];
  $when_published = $row['when_published'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  print "<p>$title ($when_published)</p>";
} //End while.
?>

Figure 11. index.php for DogRock

Line 8 adds the sort order:

order by when_published desc

The desc means the the highest (most recent) articles are first.

Line 9 shows something new:

limit 3

MySQL will limit the record set it returns to the first three records.

The rest is as before.

Summary

• The ORDER BY clause of the SELECT statement tells MySQL to sort records in the record set.

• Let users choose the sort order by adding a GET parameter to links. Add code that will append different fields to ORDER BY, depending on the parameter.

• The LIMIT clause of the SELECT statement sets the maximum number of records MySQL will add to a record set.

What now?

Let’s see how you let users “drill down,” that is, go from summary data (e.g., just the title of an article) to details (e.g., all of the data about the article).

Drilling down

Where are we?

You know how to create a listing of data, like the DogRocks’ article list. But each listing only shows the title of the article, not the article itself.

This lesson’s goals

In this lesson, you will learn that:

• Drilling down means going from summary to detail.
• On a summary page, use PHP to create links that include a primary key value.
• On a detail page, get the primary key value from the link, and use it to fetch detail data from the database.

Getting the details

“Drilling down” means to get more detailed information, to go from a summary to detail.

Here’s the article list from DogRock:

Figure 1. Article list, from articles.php

The reader gets a little information on what each article is about: the title. If the reader wants more, s/he can click on the title of the article. For example, clicking on the first one shows:

Figure 2. An article

How does this work? Here’s some of the HTML that creates Figure 1, the article list:

<tr> 
  <td><a href='show-article.php?id=2'>A new howl on the prowl</a></td> 
  <td>February 5, 2010</td> 
  <td>Lumis</td> 
</tr> 
<tr> 
  <td><a href='show-article.php?id=1'>Somebody let them out!</a></td> 
  <td>February 3, 2010</td> 
  <td>Bounder</td> 
</tr> 

Figure 3. HTML generated by articles.php

Lines 1 to 5 show a table row for an article. You can see the link to the article on line 2:

<a href='show-article.php?id=2'>

show-article.php will show an article, if you send it the article’s id. If you want a different article, send it a different id.

Here’s a picture of what’s happening:

Figure 4. Link to article

You can see the HTML that makes the link. The user clicks on the link. The browser jumps to show-article.php, passing id of the article to show.

What are the ids? They’re from the database:

Figure 5. articles table

The article ids are the primary key values.

So we need to do two things to get this working:

• Write PHP for articles.php, to create links to the articles, links like <a href='show-article.php?id=2'>.

• Write show-article.php, so it will take an id, and show the article with that id.

Let’s look at each piece.

The article list

Here’s code from articles.php. I’ve omitted error control and sorting.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Create the query.
$query = "select article_id, title, author, when_published
    from articles";
//Run the query.
$record_set = $db->query($query);
//Start the output table.
...
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  $title = $row['title'];
  $when_published = $row['when_published'];
  $author = $row['author'];
  //Format the date.
  $when_published = date('F j, Y', strtotime($when_published));
  //Output
  print "
    <tr>
      <td><a href='show-article.php?id=$article_id'>$title</a></td>
      <td>$when_published</td>
      <td>$author</td>
    </tr>";
} //end while
?>

Figure 6. articles.php

Lines 3 and 4 connect to the MySQL database.

Lines 6 to 9 create and run the MySQL query. Here it is:

select article_id, title, author, when_published from articles

The query retrieves article_id, because we’ll need it to make the link to show-article.php. show-article.php needs to know which article to show.

Here’s the data the query might fetch:

Figure 7. Article data

The while() loop (lines 13 t0 28) works as before. It grabs the fields from each row, including each record’s article_id and title. They’re put in the variables $article_id and $title (lines 15 and 16).

The program outputs the link to each article with line 24:

<a href='show-article.php?id=$article_id'>$title</a>

If $article_id is 2 and $title is A new howl on the prowl (from the first record in Figure 7), then we get:

<td><a href='show-article.php?id=2'>A new howl on the prowl</a></td>

W00f! We have the article list.

Showing an article

How do we show the article? Here’s show-article.php, with error checking omitted.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch article data.
$article_id = $_GET['id'];
$query = "select title, body, when_published, author
    from articles
    where article_id = " . $article_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$title = $row['title'];
$body = $row['body'];
$when_published = $row['when_published'];
$author = $row['author'];
//Format the date.
$when_published = date('F j, Y', strtotime($when_published));
//For the body, change new lines into <br> tags.
$body = str_replace("\n", "<br>", $body);
//Output.
print "
  <h2>$title</h2>
  <p>By $author</p>
  <p>$when_published</p>
  <p>$body</p>
";
?>

Figure 8. show-article.php

Line 6 gets the id of the article from the URL.

Lines 7 to 9 use the id to create an SQL statement to fetch the article with that id. It will fetch just one article. Remember that article_id is the primary key of the article table, so each row in the table will have a different value for article_id.

Line 10 runs the query, getting a record set back from MySQL. It puts the record set into the variable $record_set.

Line 11 takes the row from the record set:

$row = $record_set->fetch_assoc();

There is only one row, so no loop is needed.

Lines 13 to 16 extract the fields from the $row. Line 18 formats the date.

Renata

I don’t see the article id there. Why don’t you have something like this?

$article_id = $row['article_id'];

Kieran

article_id was passed into the page. The page got it on line 6:

$article_id = $_GET['id'];

So there was no need to fetch it again from the database.

Lines 20 is something new. The body field of the articles table contains the main text of the article. When a writer types it in, s/he might do something like this:

Figure 9. Typing in an article

The writer added a blank line between the two paragraphs. It gets stored this way my MySQL:

Figure 10. Article body shown by phpMyAdmin

But, as you know, browsers don’t render whitespace in HTML. And an empty line is whitespace.

Take this:

<blockquote>
  <p>
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.
  </p>
</blockquote>

Figure 11. Empty line in body

It will render as:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Figure 12. Empty line in body, rendered

The two paragraphs are smooshed together. There isn’t any whitespace between them, because browsers don’t render whitespace in HTML.

How can we make sure that the empty lines in the body are rendered by the browser?

Here’s one way, from line 20 of the code:

$body = str_replace("\n", "<br>", $body);

\n stands for the “new line” character. It’s the character that your keyboard makes when you press the Enter key. The str_replace() function goes through $body, and replaces all of the \ns with <br>s. As you, the <br> renders as a line break.

By the way, it’s important to use double quotes – "\n" – rather than single quotes – '\n'. PHP will only interpret the \n as the new line character when it’s in double quotes.

Here’s what $body looks like before the str_replace():

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

It contains two \ns, one at the end of line 1, and other at the end of line 2. They’re might have been one at the end of line 3, but the writer didn’t type it.

Here’s what $body looks like after the substitution:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.
<br> 
<br>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas vitae quam quam. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Donec commodo vehicula neque, vitae mollis ligula bibendum sit amet.

The two <br>s make the browser show the empty line. W00f!

Here’s the rest of the code from show-article.php:

//Output.
print "
  <h2>$title</h2>
  <p>By $author</p>
  <p>$when_published</p>
  <p>$body</p>
";

Figure 13. Part of show-article.php

It outputs the article data.

Summary

You learned:

• Drilling down means going from summary to detail.
• On a summary page, use PHP to create links that include a primary key value.
• On a detail page, get the primary key value from the link, and use it to fetch detail data from the database.

The administration menu

Where are we?

You know how to add records, and show them. What about maintaining the data? How do we let people edit and delete records?

That’s what we look at next. But we start with the administration menu. It lets users select the records they want to edit or delete.

This lesson’s goals

Learn that:

• Our applications’ sites have two parts: one for regular users, and one for administration.

• The admin menu lists items (products or articles), with Edit and Delete links for each.

The administration section

Both DogToys and DogRock have two parts to their sites:

• The public part, with product and article lists.

• The adminstration part.

The pages for the administration part of the site are all in the admin directory. Here is the layout of the DogToys site:

Figure 1. DogToys directory layout

The administration menu (admin/index.php) gives users access to the all of the administrative functions. This is what it looks like:

Figure 2. DogToys administration menu

At the top is a link to add a new record. At the bottom is a list of existing records, with Edit and Delete links. This is much like the list of products on the product catalog, but with some extra stuff.

Let’s see how this works.

DogToys administration menu

Here’s the code. I’ve included all the sorting and error checking.

<p>What do you want to do?</p>
<blockquote>
  <p><a href="add-product.php">Add a new product</a></p>
</blockquote>
<h2>Current products</h2>
<?php
//List the current products, along with edit/delete action for each one.
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}
//Fetch product data.
$query = "select product_id, name, description,
      image_file_name, price
    from products
    order by ";
//Get the product list order, if given.
$order = $_GET['order'];
if ( $order == 'price_asc' ) {
  $query .= 'price asc';
}
else if ( $order == 'price_desc' ) {
  $query .= 'price desc';
}
else {
  //Default sort order.
  $query .= 'name';
}
$record_set = $db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}
//Start the product table.
?>
<table cellpadding="5" cellspacing="0" border="0">
  <thead>
    <tr>
      <th> </th>
      <th><a href="index.php?order=name">Name</a></th>
      <th>Description</th>
      <th>Price<br>
        <a href="index.php?order=price_asc">
          <img src="<?php print $path_to_root; ?>/library/arrow_down.png"
               alt="Sort from low to high">
        </a>
        <a href="index.php?order=price_desc">
          <img src="<?php print $path_to_root; ?>/library/arrow_up.png"
               alt="Sort from high to low">
        </a>
      </th>
      <th>Action</th>
    </tr>
  </thead>
  <tbody>
<?php
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an product.
  $product_id = $row['product_id'];
  $name = $row['name'];
  $description = $row['description'];
  $image_file_name = $row['image_file_name'];
  $price = $row['price'];
  //Output
  print "
    <tr>
      <td>
        <img src='$path_to_root/product-images/$image_file_name'>
      </td>
      <td>$name</td>
      <td>$description</td>
      <td>$price</td>
      <td>
        <a href='edit-product.php?id=$product_id'>Edit</a><br>
        <a href='confirm-delete-product.php?id=$product_id'>Delete</a>
      </td>
    </tr>";
} //End while.
?>
  </tbody>
</table>

Figure 3. DogToys administration menu code

Line 3 makes a link to the add-product.php page.

Lines 9 and 10 connect to the database. Lines 11 to 15 check that the connection was successful.

Lines 17 to 32 create the SQL query. The statement has an ORDER BY clause attached.

Line 33 runs the query. Lines 34 to 41 check for error reports from the database.

Lines 66 to 87 output a table row for each product. Here are the lines that make the Edit and Delete links:

<a href='edit-product.php?id=$product_id'>Edit</a><br>
<a href='confirm-delete-product.php?id=$product_id'>Delete</a>

They use the “drill-down” method we used in the previous lesson. The product id is attached to the URL for the edit and delete pages.

We’ll look at how editing and deletion is done in future lessons. For now, just notice how the links are created to edit-product.php and confirm-delete-product.php.

DogRock administration menu

The DogRock administration menu is much the same:

Figure 4. DogRock administration menu

Here are excerpts from the code that makes the menu:

<p>What do you want to do?</p>
<blockquote>
  <p><a href="add-article.php">Add a new article</a></p>
</blockquote>
<h2>Current articles</h2>
<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
...
//Fetch article data.
$query = "select article_id, title, author, when_published
    from articles
    order by ";
...
$record_set = $db->query($query);
...
//Loop across records.
while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  $title = $row['title'];
  $when_published = $row['when_published'];
  $author = $row['author'];
  ...
  //Output
  print "
    <tr>
      <td><a href='../show-article.php?id=$article_id'>$title</a></td>
      <td>$when_published</td>
      <td>$author</td>
      <td>
        <a href='edit-article.php?id=$article_id'>Edit</a><br>
        <a href='confirm-delete-article.php?id=$article_id'>Delete</a>
      </td>
    </tr>";
}
?>

Figure 5. DogRock administration menu code

It’s much the same as the other administration menu. Lines 33 and 34 create the Edit and Delete links.

Summary

• Our applications’ sites have two parts: one for regular users, and one for administration.

• The admin menu lists items (products or articles), with Edit and Delete links for each.

What now?

Let’s see how record deletion works.

Deleting records

Where are we?

You know how to create an admin menu, with a delete link for each record. But what happens when the user clicks on one? Let’s see.

This lesson’s goals

In this lesson, learn:

• On the admin menu, there’s a delete link for each record. It includes the id of the record.

• Clicking the delete link shows a confirmation page. This page shows all of the data in the record to be deleted. It has a form with a hidden field containing the record id, and a confirmation button.

• If the user confirms the delete, another PHP program creates and runs an SQL DELETE statement.

The DELETE statement

The SQL DELETE statement deletes records from a table. Here’s a typical example:

delete from dogs where weight < 25;

This will delete all records from the dogs table that have a weight less than 25.

Typically, you use the primary key value to delete a single record. Like this:

delete from articles where article_id = 17;

Delete a DogToys product

The administration menu looks like this:

Figure 1. DogToys administration menu

There’s a Delete link for each record. Here’s the PHP that makes it:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an product.
  $product_id = $row['product_id'];
  ...
  print "
  ...
        <a href='confirm-delete-product.php?id=$product_id'>Delete</a>
?>
  </tbody>
</table>

Figure 2. DogToys administration menu code

The code generates HTML like this:

<a href='confirm-delete-product.php?id=2'>Delete</a>

Figure 3. HTML generated by PHP

When the user clicks on a Delete link, we could delete the record immediately. But that’s not a good idea. It would be easy to accidentally click the Delete link, maybe when aiming for the Edit link.

So let’s ask the user to confirm the deletion. That’s why the link in Figure 3 calls the page confirm-delete-product.php. It renders like this:

Figure 4. Confirm deletion

The page shows the record that the user has chosen to delete. The user has to click the button before the record will be deleted.

Here’s code for this page.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$name = $row['name'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
//Output confirmation.
print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";
?>
<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>
<p><a href="index.php">< Back</a></p>

Figure 5. confirm-delete-product.php

Lines 3 and 4 connect to the database.

Line 6 gets the id of the record from the URL.

Lines 7 to 9 creates an SQL SELECT statement with the id. Line 10 runs the query.

Line 11 gets a row from the record set (there will be only one) and puts it into the variable $row. Lines 13 to 15 get the fields from $row, putting them into variables.

The next few lines show the field values:

print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";

Part of Figure 5. confirm-delete-product.php

Line 21 looks a little odd:

<p>$$price</p>

Why two dollar signs? The second dollar sign is part of the variable name. The first one is shown on the page. So if $price is 9.95, then the following shows:

<p>$9.95</p>

Let’s look at the screen shot again:

Figure 4 (again). Confirm deletion

We’ve done everything except for the button. Here’s the code:

<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>

Another part of Figure 5. confirm-delete-product.php

The program that will delete the product is delete-product.php. It’s the action property of the form:

<form method="post" action="delete-product.php">

We only need to send one piece of data to the page: the id of the product to delete. How to add it to the form? With a hidden field. It’s in line 29:

<input type="hidden" name="id" value="<?php print $product_id; ?>">

If the product id is, say, 17, the HTML will be:

<input type="hidden" name="id" value="17">

A hidden field is like a regular <input> field, but it isn’t shown on the page. Its value, however, is passed to the destination page, like any other field on a form.

What happens when the user clicks the Confirm button? The id is sent to delete-product.php, which actually does the deletion.

Here’s the code for delete-product.php. I left out the error checking code.

<?php
//Delete a product.
//Input:
//  id: id number of the product. POST.
$path_to_root = '..';
$product_id = $_POST['id'];
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Delete the product.
$query = "delete from products where product_id = $product_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. delete-product.php

Line 6 gets the id passed to the page.

Lines 8 and 9 connect to the database.

Line 11 creates the SQL statement that will delete the product:

$query = "delete from products where product_id = $product_id";

Line 12 runs the query.

Finally…

header('location:index.php');

...back to the administration menu.

Delete a DogRock article

The administration menu for DogRock looks like this:

Figure 7. DogRock administration menu

There’s a Delete link for each record. Here’s the PHP that makes it:

while( $row = $record_set->fetch_assoc() ) {
  //Get fields for an article.
  $article_id = $row['article_id'];
  ...
  print "
  ...
        <a href='confirm-delete-article.php?id=$article_id'>Delete</a>
?>
  </tbody>
</table>

Figure 8. DogRock administration menu code

Clicking on the Delete link loads a confirmation page:

Figure 9. Confirm deletion

Here’s the code for this page.

<?php
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Get fields.
$name = $row['name'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
//Output confirmation.
print "
  <p>You have chosen to delete this product:</p>
  <blockquote>
    <p><img src='$path_to_root/product-images/$image_file_name'> $name</p>
    <p>$$price</p>
  </blockquote>
";
?>
<form method="post" action="delete-product.php">
  <p>Are you sure you want to do this?</p>
  <p>The action cannot be undone.</p>
  <p>
    <input type="hidden" name="id" value="<?php print $product_id; ?>">
    <button type="submit">Confirm</button>
  </p>
</form>
<p><a href="index.php">< Back</a></p>

Figure 10. confirm-delete-article.php

As before, it shows the record to be deleted, then a <form> with:

• A hidden field with the id of the record.

• A confirmation button.

Here’s the code for delete-article.php.

<?php
//Delete an article.
//Input:
//  id: id number of the article. POST.
$path_to_root = '..';
$article_id = $_POST['id'];
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Delete the article.
$query = "delete from articles where article_id = $article_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. delete-article.php

This is almost identical to delete-product.php.

Summary

• On the admin menu, there’s a delete link for each record. It includes the id of the record.

• Clicking the delete link shows a confirmation page. This page shows all of the data in the record to be deleted. It has a form with a hidden field containing the record id, and a confirmation button.

• If the user confirms the delete, another PHP program creates and runs an SQL DELETE statement.

What now?

We’re almost done. Time to let users edit existing records.

Editing records

Where are we?

You’ve created an administration menu, that lets users delete and edit records. You know how to program the delete part. Let’s talk about the edit part.

This lesson’s goals

In this lesson, learn:

• The add and edit forms are similar, the edit form has data already in it, and passes around an id number.

• The SQL UPDATE statement changes data in an existing record. Usually, it has a single primary key value in its WHERE clause.

Editing is like adding

Here’s a screenshot of someone editing a product record:

Figure 1. Editing a product record

The user changes the values and clicks the button.

Here’s the form for adding a product:

Figure 2. Adding a product

They’re almost the same. They have the same form fields, and the same client-side validation.

The main differences between add and edit are:

• Records being edited already exist in the database. Records being added do not. Therefore…

• Records being edited already have a primary key value. For products, that’s a product_id. New records don’t have a product id. MySQL will choose an id value when it adds a record to the database. (Recall that we made product_id an auto_increment field.)

• The edit form has the current data in the fields. All of the fields on the add form are blank.

• To edit a record, you use the SQL statement UPDATE. To add a new record, we use the INSERT statement.

The UPDATE statement

Let’s look at the dogs table again. Each row has data about one dog. The table has the fields:

• dog_id: the dog’s id number (integer, primary key).

• name: name of the dog (character).

• breed: breed of the dog (character).

• weight: weight of the dog in pounds (integer).

Here’s some sample data:

Figure 3. Dog data

Suppose Brian puts on two pounds. Here’s an SQL statement to show the change:

update dogs
   set weight = 53
   where dog_id = 5;

The statement tells MySQL three things:

• What table to change: dogs.

• Which record to change: the one with a dog_id of 5.

• What the new field values are: change weight to 53.

Most update statements just change one record, using a primary key value.

You can change text values, like this:

update dogs
   set name = 'Fido'
   where dog_id = 5;

Don’t forget the quotes around the text.

You can change more than one field at a time. For example:

update dogs
   set name = 'Fido',
     weight = 56
   where dog_id = 5;

Renata

When we changed the weight to 53. Could we do this?

update dogs
   set weight = 53
   where name = 'Brian';

Kieran

Yes, we could. You can use text fields in the where clause. It would work in this case, but…

Could there be more than one dog named Brian?

Renata

Hmm, I suppose there could be.

Kieran

Right! And your SQL statement would change the weight of all those dogs to 53.

So, usually, when you use the update statement, you use the primary key. This identifies a single record. Here it is again:

update dogs
   set weight = 53
   where dog_id = 5;

There is only one dog with an id of 5. Even if we had eight dogs called Brian, only one of them would have an id of 5.

Let’s see how we can use the update statement.

DogToys: Editing a product

Suppose a user wants to edit product data on the DogToys site. Let’s look at the workflow.

The workflow

The user starts at the admin menu, and selects a product to edit:

Figure 4. Admin menu

Here’s a sample Edit link:

<a href='edit-product.php?id=2'>Edit</a>

Clicking the link jumps to the page edit-product.php, passing an id of 2. This is the product_id of the product the user wants to edit.

edit-product.php shows a form like this:

Figure 1 (again). Editing a product record

The user changes the data and clicks the Save button. If all the validation checks are passed, the new data is saved into the database, and the user is taken back to the main menu.

Here’s a picture of the workflow:

Figure 5. Workflow for editing a product record

The edit form

Let’s look at the code for edit-product.php, the edit form. Here’s what the code has to produce.

Figure 1 (again). Editing a product record

Each of the form fields shows the current data.

Here’s how edit-product.php works:

• Get the id of the product.
• Look up the data for that product.
• Show the form, with product data in each field.

Here’s the page. Some of the code has been removed for simplicity.

<?php
...
//Connect to the database.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch product data.
$product_id = $_GET['id'];
$query = "select name, description,
      image_file_name, price
    from products
    where product_id = " . $product_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Extract fields.
$name = $row['name'];
$description = $row['description'];
$image_file_name = $row['image_file_name'];
$price = $row['price'];
?>
...
<form id="edit_product_form" method="post" action="save-edited-product.php">
  <p>
    Name<br>
    <input type="text" name="name" id="name" size="30"
           value="<?php print $name; ?>">
  </p>
  <p>
    Description<br>
    <textarea name="description" id="description" rows="5" cols="30"><?php print $description; ?></textarea>
  </p>
  <p>
    Image file name<br>
    <input type="text" name="image_file_name" id="image_file_name" size="30"
           value="<?php print $image_file_name; ?>">
  </p>
  <p>
    Price<br>
    <input type="text" name="price" id="price" size="10"
           value="<?php print $price; ?>">
  </p>
  <p>
    <input type="hidden" name="product_id" value="<?php print $product_id; ?>">
    <button type="submit">Save</button>
  </p>
</form>

Figure 6. edit-product.php

Line 4 to 5 connect to the database.

Line 7 gets the product id from the URL. Recall that the URLs are like this:

edit-product.php?id=2

Lines 8 to 11 create an SQL statement that looks up the data for the product. For example, if id was 2, the query would be:

select name, description, image_file_name, price
   from products
   where product_id = 2

Line 12 runs the query. The query only returns one row, because the where clause tests the primary key.

Line 13 fetches the row. Lines 15 to 18 get the individual fields, and put them into variables. For example:

$name = $row['name'];

Here’s how that data is used:

<input type="text" name="name" id="name" size="30"
   value="<?php print $name; ?>">

If $name contained Frisbee, this line would become:

<input type="text" name="name" id="name" size="30"
   value="Frisbee">

When the browser renders the field, it will put the value Frisbee into it:

Figure 7. Name field rendered

This page has to send the new data to save-product.php. It needs to include the product_id. But the id is not actually shown in the form. The user can’t change it, so there’s no point in showing it.

So how do you put some data into a form so that it can be sent, but not have it visible to the user?

The solution: use a hidden field. Like this:

<input type="hidden" name="product_id" value="<?php print $product_id; ?>">

The product_id will travel along with the rest of the form fields. But the user won’t see it.

W00f!

Saving the data

Here’s the workflow again:

Figure 5 (again). Workflow for editing a product record

When the user clicks the Save button, the browser sends the data to save-edited-product.php. This is the action property of the form in line 21 in Figure 6:

<form id="edit_product_form" method="post" action="save-edited-product.php">

Here’s what save-edited-product.php has to do:

• Get the new data for the product.
• Make an update statement.
• Run it.
• Jump back to the admin menu.

Here’s the code. Error checking and some other code has been omitted.

<?php
$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Get the form fields.
$product_id = stripslashes($_POST['product_id']);
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);
//Make the fields safe.
$product_id = $db->escape_string($product_id);
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);
//Create and run the SQL.
$query = "update products
  set name = '$name',
  description = '$description',
  image_file_name = '$image_file_name',
  price = $price
  where product_id = $product_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 8. save-edited-product.php

Lines 4 and 5 connect to the database.

Lines 7 to 11 get the form data, including the product_id passed as a hidden field. Note that stripslashes() was used to undo PHP’s “helpful” insertion of backslashes.

Lines 13 to 17 make the data safe from Evil Doers. It defuses SQL injection attacks.

Lines 19 to 24 creates the SQL query. It includes quotes (’) for text fields, and a where clause to select the right product.

Line 25 runs the query.

Line 27 jumps back to the admin menu.

That’s it for DogToys. Let’s move on.

DogRock: Editing an article

Workflow

The workflow for editing an article is the same as the workflow for editing a product:

• User clicks the Edit link in the admin menu.

• Browser shows an edit form.

• User changes the data and clicks the Save button.

• A PHP page saves the data.

The edit form

Here’s what the form looks like:

Figure 9. Editing an article

Here’s the code, with some stuff omitted:

<?php
//Connect to the database.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Fetch article data.
$article_id = $_GET['id'];
$query = "select title, body, when_published, author
    from articles
    where article_id = " . $article_id;
$record_set = $db->query($query);
$row = $record_set->fetch_assoc();
//Extract fields.
$title = $row['title'];
$body = $row['body'];
$when_published = $row['when_published'];
$author = $row['author'];
//Format the date.
$when_published = date('F j, Y', strtotime($when_published));
?>
<form id="new_article_form" method="post" action="save-edited-article.php">
  <p>
    Title<br>
    <input type="text" name="title" id="title" size="40"
           value="<?php print $title; ?>">
  </p>
  <p>
    Author<br>
    <input type="text" name="author" id="author" size="40"
           value="<?php print $author; ?>">
  </p>
  <p>
    When published<br>
    <input type="text" name="when_published" id="when_published" size="40"
           value="<?php print $when_published; ?>">
  </p>
  <p>
    Body<br>
    <textarea name="body" id="body" rows="8" cols="40"><?php print $body; ?></textarea>
  </p>
  <p>
    <input type="hidden" name="article_id" value="<?php print $article_id; ?>">
    <button type="submit">Save</button>
  </p>
</form>

Figure 10. edit-article.php

Lines 3 and 4 connect to the database.

Lines 6 gets the id of the article wants to edit. Lines 7 to 9 create the SQL query, which includes the article’s primary key. Line 10 runs the query, and line 11 gets the row fetched by MySQL.

Lines 13 to 16 extract the individual fields from the row. Line 18 formats the date to a familiar format.

The form is then shown. Each field’s value property puts the current value into the field. Line 41 adds the article-id as a hidden field, so the id will get passed to the page that saves the data.

Saving the data

Here’s save-edited-article.php, the program that saves the new article data.

<?php
$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Get the form fields.
$article_id = stripslashes($_POST['article_id']);
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);
$when_published = stripslashes($_POST['when_published']);
//Make the fields safe.
$article_id = $db->escape_string($article_id);
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);
$when_published = $db->escape_string($when_published);
//Format the date.
$when_published = date('Y-m-d', strtotime($when_published));
//Create and run the SQL.
$query = "update articles
  set title = '$title',
  author = '$author',
  body = '$body',
  when_published = '$when_published'
  where article_id = $article_id";
$db->query($query);
//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 11. save-edited-article.php

Lines 4 and 5 connect to the database. Lines 7 to 11 get the data passed into the form, and strips the excess backslashes with stripslashes().

Lines 13 to 17 sanitize the data. Line 19 converts the publication date into the format MySQL prefers.

Lines 21 to 26 create the SQL update statement. Line 27 runs the query.

Line 29 jumps back to the admin menu.

W00f!

Patterns

Here’s the pattern for the edit page itself.

[node:pattern/updating-database-record noterms]

Summary

• The add and edit forms are similar, the edit form has data already in it, and passes around an id number.

• The SQL UPDATE statement changes data in an existing record. Usually, it has a single primary key value in its WHERE clause.

What now?

W00f!

Time for some more exercises.

Exercises: Basic database applications