MySQL

Introduction

The Goal of this Exercise

MySQL

Introduction

Websites use databases to help users access vast amounts of related information. When shopping for shoes at Zappos.com, the page for each pair of shoes displays the colors available, the sizes available, the price, the customer reviews, and the product number. Each of these is stored as related information in the Zappos database. Every database stores data and relationships inside that data in a documented, systematic way. This is important because data itself should not depend on the environment used to access it; if Zappos decides to use a different piece of software next year, they should still be able to use the same data they are right now.

To create and access a database, you use a database management system (DBMS), which is a piece of software that follows its own particular conventions to systematically store the data and relationships that make up a database.  There are many database management systems available, but one of the most common is MySQL, which is used not only by large sites such as Zappos.com and Facebook.com, but also by small websites. To introduce you to databases in general while exploring the MySQL database management system in particular, we are going import a database from a non-MySQL database management system into MySQL.

The database we will use concerns the personal finances of United States legislators. The database was compiled by the Center for Responsive Politics at OpenSecrets.org from data published by the US Government. OpenSecrets used the Microsoft SQL Server DBMS to produce the database, and will require just a few transformations to get it to work in MySQL. First, we will review the information available on OpenSecrets’ website; second, we will download the data from OpenSecrets and transform it into a new MySQL database; third, we will use MySQL to explore the data; fourth, we will generate the same tables as those displayed on OpenSecrets’ website.

This chapter assumes you already have a local server such as XAMPP with PHPMyAdmin already correctly configured.

The Goal of this Exercise

To review the type of information we will be dealing with, head over to http://www.opensecrets.org/pfds/index.php and select a politician from the “Richest Members of Congress” sidebar.

Click on “Transactions” to see the transactions for that politician in the given year:

The OpenSecrets website will pull the data relevant to the person you selected from the database and display a table that looks like the following:

Transactions

Organization

Action

Date

Transaction Notes

Value

3M Co

Purchased

May 1 2009

 

$1,001 to $15,000

3M Co

Sold

Jun 1 2009

 

$1,001 to $15,000

ABB Ltd

Sold

 

 

$1,001 to $15,000

ABB Ltd

Purchased

Jan 27 2009

 

$15,001 to $50,000

ABB Ltd

Sold

Jan 27 2009

 

$1,001 to $15,000

ABB Ltd

Purchased

Mar 3 2009

 

$1,001 to $15,000

ABB Ltd

Purchased

Mar 3 2009

 

$1,001 to $15,000

ABB Ltd

Sold

Oct 19 2009

 

$15,001 to $50,000

ABB Ltd

Sold

Oct 19 2009

 

$50,001 to $100,000

ABB Ltd

Sold

Oct 19 2009

 

$1,001 to $15,000

A note on this particular data: as we will soon see the database does not actually look like the table shown here. This table is a simplified, sorted view of a small selection of the database. A big part of what a DBMS does is provide a way for data in the database to be simplified, selected, and sorted so only the most relevant parts are displayed. By the end of this chapter, you will see how to use MySQL to format the database we download from OpenSecrets to present a table that looks like this one.