How to Create a User Database for a Members-Only Website - dummies

How to Create a User Database for a Members-Only Website

By Steve Suehring, Janet Valade

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.

Database Table: Customer
Variable Name Type Description
id INT Auto-incrementing primary key
email 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:

  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 This file is stored in a directory outside the web space, for security reasons. The file contains information similar to the following:

define("DBHOST", "YOURHOST");
define("DBUSER", "YOURUSER");

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.