CRUD

warning: Creating default object from empty value in /home/coredogs/public_html/modules/taxonomy/taxonomy.pages.inc on line 33.

Drilling down

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.

Handling errors

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.

Editing records

See more about:

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.

Deleting records

See more about:

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 administration menu

See more about:

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.

Showing sorted data

See more about:

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.

Showing data

See more about:

Learn that:

  • The SQL SELECT statement returns a record set. A record set has one or more rows of data.
  • You can loop across the record set, extracting each row. From each row, you extract the fields in the row, and use them to create some HTML.

Adding data

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.

Goal: A Web application

See more about:

We look at three examples of Web applications: ecommerce, CMS, and training tracker. 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.

Lessons

How to...


Dogs