How to Create a User Database for a Members-Only Website
If an application design calls for a database that stores user information, the database is the core of this application. The database is needed to store the usernames and passwords of all users allowed to access the website. Often, the database is used to store much more information about the customer. This information can be used for marketing purposes.
The login application in this example is most appropriate for sites that sell products to customers. The user database is named Customer.
Design the customer database
Your first design task is to select the information you want to store in the Customer database. At the very least, you need to store a username and a password that the user can use to log in. It's also useful to know when the user account was created.
In deciding which information to collect during the user registration, you need to balance your urge to collect all the potentially useful information that you can think of against your users' urges to avoid forms that look too time-consuming and their reluctance to give out personal information. One compromise is to ask for some optional information. Users who don't mind will enter it, and those who object can just leave it blank.
Some information is required for your website to perform its function. For instance, users can readily see that a site that’s going to send them something needs to collect a name and address. However, they might not see why you need a phone number. Even if you require it, users sometimes enter fake phone numbers.
So, unless you have a captive audience, such as your employees, who must give you everything you ask for, think carefully about what information to collect. It's easy for users to leave your website when irritated. It's not like they drove miles to your store and looked for a parking space for hours. They can leave with just a click.
For the sample application, assume the website is an online store that sells products. Thus, you need to collect the customer's contact information. you believe you need her phone number in case you need to contact her about her order. Most customers are willing to provide phone numbers to reputable online retailers, recognizing that orders can have problems that need to be discussed.
The database contains only one table. The customer information is stored in the table, one record (row) for each customer.
|id||INT||Auto-incrementing primary key|
|VARCHAR(255)||E-mail address for the account. This will also be used as the username for login of the user account.|
|create_date||DATE||Date when account was added to table|
|password||VARCHAR(255)||Password for the account|
|last_name||VARCHAR(255)||Customer's last name|
|first_name||VARCHAR(255)||Customer's first name|
|street||VARCHAR(255)||Customer’s street address|
|city||VARCHAR(255)||City where customer lives|
|state||CHAR(2)||Two-letter state code|
|zip||CHAR(10)||ZIP code; 5 numbers or ZIP + 4|
|phone||VARCHAR(25)||Phone number where customer can be reached|
|phone_type||VARCHAR(255)||Phone type (work or home)|
The table has 12 fields. The first four fields, id, email, password, and create_date, are required and cannot be blank. The remaining fields contain information like the customer's name, address, and phone, which are allowed to be blank. The first field, id, is the primary key.
Build the customer database
You can create the MySQL database using any of several methods. The following SQL statement creates this database:
CREATE DATABASE CustomerDirectory;
The following SQL statement creates the table:
CREATE TABLE Customer ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL, create_date DATETIME NOT NULL, password VARCHAR(255) NOT NULL, last_name VARCHAR(255), first_name VARCHAR(255), street VARCHAR(255), city VARCHAR(255), state CHAR(2), zip CHAR(10), phone VARCHAR(25), phone_type VARCHAR(255) );
Access the customer database
PHP provides MySQL functions for accessing your database from your PHP script. The MySQL functions are passed the information needed to access the database, such as a MySQL account name and password. The MySQL account name and password are not related to any other account name or password that you have, such as a password to log in to the system.
In this application, the information needed by the PHP mysqli functions is stored in a separate file called dbstuff.inc. This file is stored in a directory outside the web space, for security reasons. The file contains information similar to the following:
<?php define("DBHOST", "YOURHOST"); define("DBUSER", "YOURUSER"); define("DBPASS", "YOURPASSWORD"); define("DB","CustomerDirectory"); ?>
Notice the PHP tags at the beginning and the end of the file. If these tags are not included, the information might display on the web page for the whole world to see. Not what you want at all.
For security reasons, this file is stored in a directory outside the web space. You can set the include directory in your php.ini file.
This database is intended to hold data entered by customers — not by you. It will be empty when the application is first made available to customers until customers add data.
When you test your application scripts, the scripts will add a row to the database. You need to add a row with a username and password for your own use when testing the scripts.