Technology. Education. Art (TEA) TEAlearn.org

Home > Courses > Web development using PHP and MySQL > Authentication: Create Database

Authentication: Create Database

Subject: Web development using PHP and MySQL

We will be creating the database tables for our authentication program in this lesson

For example we have a MySQL database named "bgdb" so we will be creating two tables:
1. user: this table will be used to store the user registration record.
2. dairy: this dairy table will be use by this simple dairy program we will be developing; to help users to keep their personal information (just like the paper dairy).

The essence of this example is to demonstrate how to use authentication and session to keep track of individual information; so that when they login they will see only their dairy or information.

Here is the SQL CREATE queries to create the two tables:

User (table)


CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(200) NOT NULL,
  `password` varchar(200) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

dairy (table)


CREATE TABLE IF NOT EXISTS `dairy` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `content` text,
  `date_submit` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Now that we are done with the tables or data layer, let us create the following PHP files in preparation of our authentication web application:

auth_change_pw.php
auth_login.php
auth_register.php
auth_register_confirm.php
auth_test.php
auth_welcome.php
conn.php

I purposely use the abbreviation (auth_) to name all the files use for the authentication.

user table