Storing User Data

Before users can register and log in to your site, you must create a login table to store user information. A login table contains fields for the user name and password, and can also include any other user information you choose to store, such as names, addresses, and phone numbers.

Before you create the login table, however, you need to decide what type of controls you want. Do you want to restrict access to the entire site to registered users only, or do you want to use access levels to restrict access to different sections of the site? If you use different access levelsfor example, guest status or member statusyou need to include a field in the login table to store access-privilege information.

In the following exercise, you'll use MySQL to create a login database that includes a users table for storing user information. You'll use this table for the remaining exercises in this chapter.

To create a MySQL database table for storing user information:

1.	Do one of the following: Start the MySQL Monitor in Windows. Start MySQL in a Terminal window in Mac OS X. For more information on using the MySQL Monitor in Windows, see the task "To create a database using MySQL in Windows," in Chapter 5. For more details on using MySQL in a Terminal window in Mac OS X, see "To create a database using MySQL on a Mac," in Chapter 5.
2.	At the mysql prompt, type CREATE DATABASE Login; and press Enter (Windows) or Return (Macintosh) (Figure 13.1). Figure 13.1. Create a new database named Login with MySQL. You've created a new MySQL database named Login.
3.	At the next mysql prompt, type USE Login; and press Enter (Windows) or Return (Mac). You've just made the Login database the active database. In the next step, you'll enter code to create a table named users in the Login database (Script 13.1). The users table includes fields for the name, user name, password, and access level for each registered user. You'll insert four records in this table. Script 13.1. MySQL code to create and insert records in the users table. Type each line at the mysql prompt. Press Enter (Windows) or Return (Mac) after each semicolon, and then type the next line once you see the mysql prompt. 1 CREATE TABLE users (Name VARCHAR (30), userName VARCHAR (50), Password CHAR (8), Access VARCHAR (13), PRIMARY KEY (userName)); 2 INSERT INTO users VALUES ("Joe Rigby", "[e-mail], "jr16733e", "guest); 3 INSERT INTO users VALUES ("June Fields", "[e-mail], "june2005, "member"); 4 INSERT INTO users VALUES ("Ron Summers", "[e-mail], "rsX36576", "member); 5 INSERT INTO users VALUES ("Jim Knowles", "[e-mail], "JK2005XK", "administrator);
4.	At the prompt, type the following lines of code. After each semicolon in the code, press Enter (Windows) or Return (Mac), and then type the next line once you see the mysql prompt: CREATE TABLE users (Name VARCHAR (30), userName VARCHAR (50), Password CHAR (8), Access VARCHAR (13), PRIMARY KEY (userName) ); INSERT INTO users VALUES ("Joe Rigby, "[e-mail]", "jr16733e", "guest); INSERT INTO users VALUES ("June Fields, "[e-mail]", "june2005", "member); INSERT INTO users VALUES ("Ron Summers, "[e-mail]", "rsX36576", "member); INSERT INTO users VALUES ("Jim Knowles, "[e-mail]", "JK2005XK", "administrator); Alternatively, you can download the code file from this book's Web site at www.peachpit.com/dw.vqp. Open the file named Script13.1.txt and copy and paste the code in the MySQL Monitor (Windows) or Terminal window (Mac).
5.	At the prompt, type SELECT * FROM users; and press Enter (Windows) or Return (Mac). Using SELECT and a wildcard operator (*) selects all the fields in the table. The contents of the users table appear (Figure 13.2). Figure 13.2. You can view the contents of the users table by using SELECT and a wildcard operator (*), which selects all the fields in a table.
6.	At the prompt, do one of the following If you're running Windows, type exit and press Enter to close the MySQL Monitor. If you're on a Mac, type exit and press Return to stop MySQL, and then type exit and press Return to log out. Then quit Terminal and close the Terminal window. You've created a MySQL database named Login and added a table named users to this database. The users table contains four records that include name, user name, password, and access-level fields. The primary key is the user name. You'll use this table for the remaining exercises in this chapter.