7.1 Organizing Data in a Database

Information in your database is organized in tables, which have rows and columns. (Columns are also sometimes referred to as fields.) Each column in a table is a category of information, and each row is a set of values for each column. For example, a table holding information about dishes on a menu would have columns for each dish's ID, name, price, and spiciness. Each row in the table is the group of values for on particular dish—for example, "1," "Fried Bean Curd," "5.50," and "0" (meaning not spicy).

You can think of a table organized like a simple spreadsheet, with column names across the top, as shown in Figure 7-1.

Figure 7-1. Data organized in a grid

One important difference between a spreadsheet and a database table, however, is that the rows in a database table have no inherent order. When you want to retrieve data from a table with the rows arranged in a particular way (e.g., in alphabetic order by student name), you need to explicitly specify that order when you ask the database for the data. The SQL Lesson: ORDER BY and LIMIT sidebar in this chapter describes how to do this.

SQL (Structured Query Language) is a language to ask questions of and give instructions to the database program. Your PHP program sends SQL queries to a database program. If the query retrieves data in the database (for example, "Find me all spicy dishes"), then the database program responds with the set of rows that match the query. If the query changes data in the database (for example, "Add this new dish" or "Double the prices of all nonspicy dishes"), then the database program replies with whether or not the operation succeeded.

SQL is a mixed bag when it comes to case-sensitivity. SQL keywords are not case-sensitive, but in this book they are always written as uppercase to distinguish them from the other parts of the queries. Names of tables and columns in your queries generally are case-sensitive. All of the SQL examples in this book use lowercase column and table names to help you distinguish them from the SQL keywords. Any literal values that you put in queries are case-sensitive. Telling the database program that the name of a new dish is fried bean curd is different than telling it that the new dish is called FRIED Bean Curd.

Almost all of the SQL queries that you write to use in your PHP programs rely on one of four SQL commands: INSERT, UPDATE, DELETE, or SELECT. Each of these commands is described in this chapter. Section 7.3 describes the CREATE TABLE command, which you use to make new tables in your database.

To learn more about SQL, read SQL in a Nutshell, by Kevin E. Kline (O'Reilly). It provides an overview of standard SQL as well as the SQL extensions in MySQL, Oracle, PostgreSQL, and Microsoft SQL Server. For more in-depth information about working with PHP and MySQL, read Web Database Applications with PHP & MySQL, by Hugh E. Williams and David Lane (O'Reilly). MySQL Cookbook, by Paul DuBois (O'Reilly) is also an excellent source for answers to lots of SQL and MySQL questions.