Tables: Containers for your data

In MySQL databases, you store your information in an object called a table. Tables are essentially a grid, where each vertical segment represents a column (a specific category of information) and each horizontal segment represents a row (a single record in the table).

Figure 2-1 shows a table of customer data. Notice how the table includes separate columns for each logical grouping of the data (company name, contact name, and so on).

In web development, you use MySQL tables to store the data that will appear in your pages. To get that data from the server to the web page requires five steps:

1. On the web page, some JavaScript code launches a PHP script on the server.
2. That PHP script asks a MySQL database for the data required by the web page.
3. The PHP script configures the data into a format that JavaScript can understand.
4. PHP sends the data back to the web page.
5. The JavaScript code accepts the data and displays it on the page.

I go through these steps in glorious detail in Book 5, Chapter 3 and in Book 6, Chapter 1.

Queries: Asking questions of your data

By far the most common concern expressed by new database users (and many old-timers, as well) is how to extract the information they need from all that data. What if, for example, you have a database of accounts receivable invoices and your boss wants a web page that tells her how many invoices are more than 150 days past due? You can’t hand-code such a page because, for a large database, your page would be out of date before you were done. The better way would be to ask MySQL to do the work for you by creating another type of database object: a query. Queries are, literally, questions you ask of your data. In this case, you could ask MySQL to display a list of all invoices more than 150 days past due.

Queries let you extract from one or more tables a subset of the data. For example, in a table of customer names and addresses, what if I wanted to see a list of firms that are located in France? No problem. I’d just set up a query that asks, in effect, “Which rows have 'France' in the country column?” The answer to this question is shown in Figure 2-2.

The actual querying process is performed using a technology called Structured Query Language (or SQL, pronounced “ess-kew-ell”). In the five-step procedure I mention in the previous section, the SQL portion takes place in Step 2.

Importing data into MySQL

Before I talk about building a database from scratch, let me first go through the procedure for getting some existing data into MySQL. phpMyAdmin supports several import formats, but you’ll most likely want to use a comma-separated values (.csv) file, where the column data in each row is separated by commas. Another possibility is a SQL (.sql) file, which is a backup file for a MySQL database.

1. In phpMyAdmin, click the Import tab.

   If you don't see the Import tab, click More, then click Import.

2. In the File to Import section, click Browse (Windows) or Choose File (Mac).

   Your operating system’s file chooser dialog appears.

3. Click the file that contains the data you want to import and then click Open (Windows) or Choose (Mac).

4. In the Format section, make sure the list shows the correct format for the file you chose.

   If you’re importing a CSV file, the list should have CSV selected; if you’re importing a SQL backup file, the list should have SQL selected.

5. If you’re importing a CSV file, use the Format-Specific Options section to tell phpMyAdmin the structure of the file.

   In particular, if the first line of your CSV file contains the column names of your data, then you need to select the check box labeled The First Line of the File Contains the Table Column Names.

6. Click Go.

   phpMyAdmin imports the data.

If you imported a CSV file, you should see the message Import has been successfully finished and in the navigation pane you should see a new database named CSV_DB, as shown in Figure 2-4.

Here are the steps to follow to rename the database and the table that contains the imported data:

1. In the navigation pane, click CSV_DB.

   phpMyAdmin opens the database. Notice that you now see a table named TBL_NAME. That’s the table that contains the imported CSV data. I show you how to rename it beginning with Step 6.

2. Click the Operations tab.

   If you don’t see the Operations tab, click More, then click Operations.

3. In the Rename Database To section, type the new database name in the text box provided.

4. Click Go.

   phpMyAdmin asks you to confirm.

5. Click OK.

   phpMyAdmin changes the database name.

6. In the navigation pane, click TBL_NAME.

7. Click the Operations tab.

   If you don’t see the Operations tab, click More, then click Operations.

8. In the Table Options section, use the Rename Table To text box to type the new table.

9. Click Go.

   phpMyAdmin changes the table name.

Backing up MySQL data

As you work with phpMyAdmin, you should run periodic backups to make sure your data is safe. Here are the steps to follow:

1. In phpMyAdmin, click the Export tab.

   If you don’t see the Export tab, click More, then click Export.

2. In the Format section, use the list to select SQL (although this is the default format).

3. Click Go.

   phpMyAdmin exports the data, which your web browser then downloads to your computer.

Creating a MySQL database

The first question you need to ask yourself is: Do I need just a single database or do I need multiple databases? As a web developer, you’ll almost always need multiple databases. Here’s why:

• You need a separate database for each website you build.
• You need a separate database for each web app you build.
• You need a separate database for each client you have.

If you’re just building a single website or app, and you have no clients, then one database is fine, but know that MySQL is ready and willing to accommodate almost any number of databases you care to throw at it.

Here are the steps to follow to create a database using phpMyAdmin:

1. In the navigation pane, click New that appears at the top of the navigation tree.
2. In the Create Database section, use the Database Name text box to type the name you want to use.

3. In the Collation list, select utf8_general_ci.

   Collation refers to how MySQL compares characters (for example, when sorting data). In this case, you’re telling MySQL to use a standard, case-insensitive (for example, a equals A) collation on the UTF-8 character set.

4. Click Create.

   phpMyAdmin creates the database for you.

Designing your table

You need to plan your table design before you create it. By asking yourself a few questions in advance, you can save yourself the trouble of redesigning your table later. For simple tables, you need to ask yourself three basic questions:

• Does the table belong in the current database?
• What type of data should I store in each table?
• What columns should I use to store the data?

The next few sections examine these questions in more detail.

Relating tables

MySQL is a relational database system, which means that you can establish relationships between multiple tables. As an example, suppose you have a database that contains (at least) two tables:

• orders: This table holds data on orders placed by your customers, including the customer name, the date of the order, and so on. It also includes an order_id column as the primary key, as shown in Figure 2-5.
• order_details: This table holds data on the specific products that comprise each order: the product name, the unit price, the quantity ordered. It also includes an order_id field, as shown in Figure 2-6.

Why not lump both tables into a single table? Well, that would mean that, for each product ordered, you'd have to include the name of the customer, the order date, and so on. If the customer purchased ten different products, this information would be repeated ten times. To avoid such waste, the data is kept in separate tables, and the two tables are related on the common column called order_id.

For example, notice in Figure 2-5 that the first row in the orders table has an order_id value of 10248. Now check out Figure 2-6, where you see that the first three rows of the order_details table also have an order_id value of 10248. This means that when you join these tables on the related order_id field, MySQL combines the data, as shown in Figure 2-7. For example, notice that the first three rows still have an order_id value of 10248, but they now also include the customer_id column from the orders table.

Creating a MySQL table

Here are the steps to follow to create a table in a MySQL database:

1. In the navigation pane, click the database in which you want to add the table.

2. In the Structure tab, use the Create Table section to type a name for the table, select the number of columns you want, and then click Go.

   If you're not sure how many columns you need, just make your best guess for now. You can always add more later on.

3. Type a name for the column.

4. In the Type list, select the data type you want to use for the data.

   There’s a very long list of data types to wade through, but only a few make sense in most web projects:

   • INT: Stores an integer value between -2,147,483,648 and 2,147,483,648. For really small integer values, consider using either TINYINT (-128 to 127 or 0 to 255) or SMALLINT (-32,768 to 32,767 or 0 to 65,535).
   • VARCHAR: Stores a variable-length string between 0 and 65,535 characters long. If you need to store super-long chunks of text, consider MEDIUMTEXT (up to 16,777,215 characters) instead.
   • DATE: Stores a date and time value.
5. If you selected VARCHAR in Step 4, you can use the Length/Values field to enter a maximum size for the column.

6. Use the Default list to specify a default value that MySQL will enter automatically into the column when you create a new row.

   If you want the current date and time in a DATE column, select CURRENT_TIMESTAMP. Otherwise, select As Defined, then enter a value in the text box that appears.

7. In the Collation list, select utf8_general_ci.

8. To allow MySQL to enter no value into the column, select the Null check box.

   If you leave Null deselected, then be sure you always specify a value for the column.

9. If you want MySQL to index the column, use the Index list to select the type of index you want.

   In most cases you should choose the all-purpose INDEX type; if the column values are all different, select the UNIQUE type; for a text-heavy field, select the FULLTEXT type.

   Don't index every column. Instead, you only need to index those columns that you’ll be using for sorting and querying.

10. Repeat Steps 3 through 9 until you’ve defined all your columns.
11. Click Save.

Adding data to a table

Ideally, most of your table data will get inserted automatically, either by importing data or by having your page users fill in an HTML form (see Book 6, Chapters 2 and 3). If you do need to enter table data by hand, here’s how it’s done:

1. In the navigation pane, click the table in which you want the data added.

2. Click the Insert tab.

   phpMyAdmin displays empty text boxes for each column in the table. If you see two sets of text boxes, scroll down to the bottom of the Insert tab and change Continue Insertion with 2 Rows to Continue Insertion with 1 Row.

3. Use the Value fields to add a value for each column.

   If a column accepts null values (that is, if the column’s Null check box is selected), then it’s okay to leave that column’s Value field blank.

4. If you want to add multiple rows, use the two lists near the bottom of the page to select Insert as New Row and then Insert Another New Row.
5. Click Go to insert the data.

Creating a primary key

When you import a table, MySQL doesn’t automatically create a primary key, so you need to follow these steps to create the primary key yourself:

1. In the navigation pane, click the table you want to work with.
2. Click the Structure tab.

3. Click the check box that appears to the left of the column you want to use as the primary key.

   Make sure you select a column that contains only unique values.

4. Click Primary.

   MySQL configures the column as the table’s primary key.

What happens if none of your table’s fields contain unique items? In that case, you need to create a column to use as the primary key. Here’s how:

1. In the navigation pane, click the table you want to work with.
2. Click the Structure tab.
3. Leave the Add 1 Columns as is, but select At Beginning of Table in the list, then click Go.

4. Type a name for the primary key field.

   If you’re not sure what name to use, something like table_id would work, where table is the name of the table.

5. Select the A_I (AUTO_INCREMENT) check box.

   MySQL displays the Add Index dialog.

6. Leave the default settings as they are, and then click Go.

7. Click Save.

   MySQL adds the field and automatically populates it with unique integer values.

What is SQL?

SQL is a collection of commands that interrogate or modify — query, in the SQL vernacular — MySQL data in some way. SQL is huge, but as a web developer you really only need to know about four query types:

• SELECT: Returns a subset of a table’s data
• INSERT: Adds a new row to a table
• UPDATE: Modifies a table’s existing data
• DELETE: Removes one or more rows from a table

In the case of the SELECT, UPDATE, and DELETE query types, you target the specific rows you want to work with by specifying criteria, which are extra parameters that define one or more conditions the rows must meet. For example, you might want to run a SELECT query that returns only those customers where the country column is equal to France. Similarly, you might want to run a DELETE query only on those items in the products table where the discontinued column has the value TRUE.

Creating a SELECT query

The most common type of query is the SELECT query that returns rows from one or more tables based on the columns you choose and the criteria you apply to those columns. It's called a SELECT query not only because you use it to select certain rows, but also because it's based on the SQL language's SELECT statement. SELECT is the SQL “verb” that you'll see and work with most often, and it’s used to create a subset based on the table, columns, criteria, and other clauses specified in the statement. Here’s a simplified syntax for the SELECT verb:

SELECT select_columns

FROM table_name

WHERE criteria

ORDER BY sort_columns [DESC]

• SELECT select_columns: Specifies the names of the columns you want in your subset. If you want all the columns, use * instead.
• FROM table_name: The name of table that contains the data.
• WHERE criteria: Filters the data to give you only those rows that match the specified criteria.
• ORDER BY sort_columns: Sorts the results in ascending order based on the data in the columns specified by sort_columns (separated by commas, if you have more than one). Use the optional DESC keyword to sort the rows in descending order.

The most basic SELECT query is one that returns all the rows from a table. For example, the following SELECT statement returns all the rows from the customers table:

SELECT *

FROM customers

In the following example, only the company_name, city, and country columns are returned in the results:

SELECT company_name, city, country

FROM customers

Here's another example that sorts the rows based on the values in the company_name column:

SELECT *

FROM customers

ORDER BY company_name

Understanding query criteria

The heart of any query is its criteria. They are a set of expressions that determine the rows that are included in the query results. All query expressions have the same general structure. They contain one or more operands — which can be literal values (such as 123 or "USA" or 2018-08-23), identifiers (names of MySQL objects, such as tables), or functions — separated by one or more operators — the symbols that combine the operands in some way, such as the plus sign (+) and the greater than sign (>).

Most criteria expressions are logical formulas that, when applied to each row in the table, return TRUE or FALSE. The subset contains only those rows for which the expression returns TRUE.

Querying multiple tables

Although most of your MySQL queries will use just a single table, some of the most useful and powerful queries involve two (or more) tables. The type of multiple-table query you’ll see and use most often is called an inner join because it joins two tables based on a common column.

To create an inner join on two tables, use the following version of the FROM clause:

FROM table1

INNER JOIN table2

ON table1.column = table2.column

Here, table1 and table2 are the names of the two tables you want to join, and table1.column and table2.column are the common columns in each table. Note that the column names don't have to be the same.

For example, suppose you have two tables: orders and order_details, and they each have a column named order_id that stores a value that is unique for each order. The following SELECT statement sets up an inner join on these tables:

SELECT *

FROM orders

INNER JOIN order_details

ON orders.order_id = order_details.order_id

If you only want certain columns from both tables in the results, specify the column names after the SELECT command using the table.column syntax, as in this example:

SELECT orders.order_id, orders.customer_id, order_details.quantity

FROM orders

INNER JOIN order_details

ON orders.order_id = order_details.order_id

Adding table data with an INSERT query

An INSERT query adds a new row to an existing table. In MySQL, you build an INSERT query using the INSERT verb:

INSERT

INTO table (columns)

VALUES (values)

• table: The name of the table into which you want the row appended.
• columns: A comma-separated list of column names from table. The values you specify will be added to these columns.
• values: A comma-separated list of values that you want to add. The order of these values must correspond with the order of the column names in the columns parameter.

For example, suppose we have a table named categories that includes three fields: category_id, category_name, and description. First, assume that category_id is the table's primary key and its value is generated automatically by an AUTO_INCREMENT function, which means you can ignore it when building your INSERT query. Therefore, you can use the following SQL statement to add a new row:

INSERT

INTO categories (category_name, description)

VALUES ('Breads', 'Multi-grain, rye, and other deliciousness')

Modifying table data with an UPDATE query

An UPDATE query modifies the values in one or more columns and optionally restricts the scope of the updating to those rows that satisfy some criteria. In MySQL, you build an UPDATE query by using the UPDATE verb to construct a statement with the following syntax:

UPDATE table

SET column1=value1,column2=value2,…

WHERE criteria

• table: The table that contains the data you want to update
• column1=value1,column2=value2, etc.: The new values you want to assign to the specified columns
• criteria: The criteria that define which rows will be updated

For example, suppose you have products table and want to increase the values in the unit_price column by 5 percent for the Beverages category (category_id = 1). This is the same as multiplying the current unit_price values by 1.05, so the UPDATE statement looks like this:

UPDATE products

SET unit_price = unit_price*1.05

WHERE CategoryID = 1

Removing table data with a DELETE query

A DELETE query removes rows from a table and optionally restricts the scope of the deletion to those rows that satisfy some criteria. If you don't include criteria, MySQL deletes every row in the specified table.

In MySQL, you build a delete query by using the DELETE verb to construct a statement with the following syntax:

DELETE

FROM table

WHERE criteria

• table: The table that contains the rows you want to delete
• criteria: The criteria that defines which rows will be deleted

For example, if you want to delete those rows in the products table where the supplier_id value is 1, you use the following SQL statement:

DELETE

FROM products

WHERE supplier_id = 1