The Database Tables - PHP

The database table that is used to track user log in and log out is very simple. Below is the SQL to create a table for it:

# Host: localhost
# Database: intranet
# Table: 'logtbl'
#

CREATE TABLE `logtbl` (
  `logid` int(4) NOT NULL auto_increment,
  `u_id` int(4) NOT NULL default '0',
  `start_sess` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_sess` datetime NOT NULL default '0000-00-00 00:00:00',
  `duration` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`logid`)
) TYPE=MyISAM;

It has the following fields:

logid          - Stores a unique number for each new record
u_id           - Stores the user id of the currently logged in user
start_sess - time and date of the user log in
end_sess   - time and date of user log out
duration     - duration in hours, minutes and seconds between start_sess and end_sess date time values

The all important users table contains the usernames and passwords of all the intranet users.  Below is the SQL that will create this table. I've also included sample data for the table. Notice the format in which the email addresses are written. The checkemail() function in the validate class that we discussed in earlier articles checks for this format to verify the validity of a email address:

# Host: localhost
# Database: intranet
# Table: 'users'
#

CREATE TABLE `users` (
  `uid` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL default '',
  `sname` varchar(20) NOT NULL default '',
  `upass` varchar(8) NOT NULL default '',
  `access_level` enum('admin','regular') NOT NULL default
'regular',
  `email` varchar(100) NOT NULL default '',
  `depid` int(2) NOT NULL default '0',
  `isActive` int(2) NOT NULL default '0',
  PRIMARY KEY  (`uid`)
) TYPE=MyISAM

The table has the following fields:

uid          - creates a unique id for every user
name      - stores the name of the user
sname    - stores the surname of the user
upass     - stores the user password
access   - stores the access level either admin or normal
email     - stores the email address of the user
depid     - stores the department id of the user
isactive - sets the user account to either  1 for active or 0 for  inactive

And here's the sample data for the table:

INSERT INTO `users` VALUES (1, 'mubasen', 'gaseb', 'pass',
'admin', 'mubasen.gaseb@damaranet.com', 3, 1);
INSERT INTO `users` VALUES (2, 'dantago', 'nanub', 'sunday',
'regular', 'dantago.nanub@damaranet.com', 1, 1);
INSERT INTO `users` VALUES (3, 'axaro', 'garoeb', 'monday',
'regular', 'axaro.garoeb@damaranet.com', 2, 1);
INSERT INTO `users` VALUES (4, 'koro', 'garises', 'teusday',
'admin', 'koro.garises@damaranet.com', 1, 1);
INSERT INTO `users` VALUES (5, 'saridao', 'taurob', 'thursday',
'admin', 'saridao.taurob@damaranet.com', 2, 1);