Navigation
Tutorial Categories
 

Advanced User System (timezones, usergroups, online now, avatars) (Tutorials » PHP)

Here is my tutorial on how to make an advanced user system. This advanced user system will be alot like PHPwned's user system with usergroups, email confirmation, and "online now".

First we need to create a database to use for our user system. You could also an existing one instead. Then select to use that database.
SQL Code:
CREATE DATABASE usersys;
USE usersys;

First, we need a datastore table. Datastore (in this case) is storing an array of data in a single row for quick access instead of selecting all rows from a table. It especially is good for performance if you select more than one datastore value from the table in a single query (using mysql IN()).
Here is the SQL for our datastore table. Explanation below.
SQL Code:
CREATE TABLE datastore (
	name varchar(15) not null primary key,
	value longtext not null
) ENGINE=MyISAM;

Explanation

  • name - The unique datastore name.
  • value - The datastore value.


We also need a table for our usergroups. Explanation below.
SQL Code:
CREATE TABLE usergroup (
	id int(2) unsigned not null primary key auto_increment,
	name varchar(25) not null,
	usertitle varchar(25) not null,
	html_pre varchar(50) not null,
	html_post varchar(50) not null,
	perms varchar(255) not null
) ENGINE=MyISAM;

Explanation

  • id - The unique usergroup id. This is what a users usergroupid will be.
  • name - The name of the usergroup. eg. "Registered Users".
  • usertitle - The usertitle for the usergroup. Will show under a user's username in postbit. eg. "Registered User".
  • html_pre - The html to prefix a user's username with when outputted. eg. <b>
  • html_post - The html to suffix a user's username with when outputted. eg. </b>
  • perms - The usergroups permissions. A serialized array of permissions.


Now we need a table for the users. Explanation below.
SQL Code:
CREATE TABLE user (
	userid int(8) unsigned not null primary key auto_increment,
	username varchar(25) not null,
	usergroupid int(2) unsigned not null,
	password varchar(32) not null,
	salt varchar(3) not null,
	regtime int(10) unsigned not null,
	lastactivity int(10) unsigned not null default 0,
	lastvisit int(10) unsigned not null default 0,
	email varchar(75) not null,
	dob int(8) unsigned not null default 0,
	timezone float(4) not null default 0,
	dst tinyint(1) unsigned not null default 0,
	av varchar(3) not null,
	aim varchar(16) not null,
	msn varchar(75) not null,
	yim varchar(32) not null,
	bio varchar(255) not null,
	loc varchar(50) not null,
	occ varchar(50) not null,
	homepage varchar(50) not null
) ENGINE=MyISAM;

Explanation

  • userid - The unique id of the user.
  • username - The user's username.
  • usergroupid - The id of the usergroup in which the user belongs.
  • password - The md5 hash of the user's password.
  • salt - The characters which to md5 with a users password to create their password hash.
  • regtime - The unix timestamp of when the user registered.
  • lastactivity - The unix timestamp of when the user last loaded a page.
  • lastvisit - The unix timestamp of when a user logged in last.
  • email - The user's email address.
  • dob - The user's date of birth in the format of YYYYMMDD.
  • timezone - The user's timezone. GMT.
  • dst - Whether or not to show daylight savings time corrections 0=never, 1=always, 2=auto.
  • av - The file extension of a user's avatar if any.
  • aim - The user's AOL Instant Messenger scree nname.
  • msn - The user's MSN Messenger email.
  • yim - The user's Yahoo Messenger screen name.
  • bio - The user's biography.
  • loc - The user's location.
  • occ - The user's occupation.
  • homepage - The user's homepage.


Now we need a table for email confirmations. Explanation below.
SQL Code:
CREATE TABLE confirm (
	id int(10) unsigned not null primary key auto_increment,
	userid int(8) unsigned not null,
	email varchar(75) not null,
	register tinyint(1) unsigned not null,
	confirm varchar(32) not null,
	dateline int(10) unsigned not null
) ENGINE=MyISAM;

Explanation

  • id - The unique confirm id
  • userid - The user id of the user that created this confirmation
  • email - The email address for this confirmation. (will update a user's email address with this if not for registration)
  • register - Boolean whether or not this is to confirm registration
  • confirm - The confirm string. The important part.
  • dateline - The unix timestamp of when this confirmation was created


We need a table for timezones. Explaination below.
SQL Code:
CREATE TABLE timezone (
	timezone float(4) not null primary key,
	label varchar(50) not null
) ENGINE=MyISAM;

Explanation

  • timezone - The timezone GMT would be 0, GMT-6 would be -6
  • label - The label for the timezone (shown in registration, and usercp)


We need to fill our timezone table with all the timezones.
SQL Code:
INSERT INTO timezone (timezone, label) VALUES
(-12, 'Eniwetok, Kwajalein'),
(-11, 'Midway Island, Samoa'),
(-10, 'Hawaii'),
(-9, 'Alaska'),
(-8, 'Pacific Time (US & Canada)'),
(-7, 'Mountain Time (US & Canada)'),
(-6, 'Central Time (US & Canada), Mexico City'),
(-5, 'Eastern Time (US & Canada), Bogota, Lima'),
(-4.5, 'Caracas'),
(-4, 'Atlantic Time (Canada), La Paz, Santiago'),
(-3.5, 'Newfoundland'),
(-3, 'Brazil, Buenos Aires, Georgetown'),
(-2, 'Mid-Atlantic'),
(-1, 'Azores, Cape Verde Islands'),
(0, 'Western Europe Time, London, Lisbon, Casablanca'),
(1, 'Brussels, Copenhagen, Madrid, Paris'),
(2, 'Kaliningrad, South Africa'),
(3, 'Baghdad, Riyadh, Moscow, St. Petersburg'),
(3.5, 'Tehran'),
(4, 'Abu Dhabi, Muscat, Baku, Tbilisi'),
(4.5, 'Kabul'),
(5, 'Ekaterinburg, Islamabad, Karachi, Tashkent'),
(5.5, 'Bombay, Calcutta, Madras, New Delhi'),
(5.75, 'Kathmandu'),
(6, 'Almaty, Dhaka, Colombo'),
(6.5, 'Yangon, Cocos Islands'),
(7, 'Bangkok, Hanoi, Jakarta'),
(8, 'Beijing, Perth, Singapore, Hong Kong'),
(9, 'Tokyo, Seoul, Osaka, Sapporo, Yakutsk'),
(9.5, 'Adelaide, Darwin'),
(10, 'Eastern Australia, Guam, Vladivostok'),
(11, 'Magadan, Solomon Islands, New Caledonia'),
(12, 'Auckland, Wellington, Fiji, Kamchatka');


Last but not least, we need a table for "online now". Explanation below.
SQL Code:
CREATE TABLE online (
	sid varchar(32) not null primary key,
	userid int(8) unsigned not null,
	username varchar(25) not null,
	usergroupid int(2) unsigned not null default 1,
	ip varchar(15) not null,
	lastactivity int(10) unsigned not null,
	lastpage varchar(100) not null
) ENGINE=MyISAM;

Explanation

  • sid - The user's session id.
  • userid - The user's userid (if user is not a guest).
  • username - The user's username (if not a guest).
  • usergroupid - The user's usergroupid (default 1 because 1 is the "guests" usergroup).
  • ip - The user's IP address.
  • lastactivity - The unix timestamp of the user's last page view.
  • lastpage - The REQUEST_URI of the user's last page view.


Lets add some indexes to make queries faster when using ORDER BY or WHERE. I'm not going to explain why to use indexes, if you need to know more about indexes, google is your friend.
SQL Code:
ALTER TABLE user ADD INDEX username (username);
ALTER TABLE online ADD INDEX lastactivity (lastactivity);


All SQL Combined


SQL Code:
CREATE DATABASE usersys;
USE usersys;
CREATE TABLE datastore (
	name varchar(15) not null primary key,
	value longtext not null
) ENGINE=MyISAM;
CREATE TABLE usergroup (
	id int(2) unsigned not null primary key auto_increment,
	name varchar(25) not null,
	usertitle varchar(25) not null,
	html_pre varchar(50) not null,
	html_post varchar(50) not null,
	perms varchar(255) not null
) ENGINE=MyISAM;
CREATE TABLE user (
	userid int(8) unsigned not null primary key auto_increment,
	username varchar(25) not null,
	usergroupid int(2) unsigned not null,
	password varchar(32) not null,
	salt varchar(3) not null,
	regtime int(10) unsigned not null,
	lastactivity int(10) unsigned not null default 0,
	lastvisit int(10) unsigned not null default 0,
	email varchar(75) not null,
	dob int(8) unsigned not null default 0,
	timezone float(4) not null default 0,
	dst tinyint(1) unsigned not null default 0,
	av varchar(3) not null,
	aim varchar(16) not null,
	msn varchar(75) not null,
	yim varchar(32) not null,
	bio varchar(255) not null,
	loc varchar(50) not null,
	occ varchar(50) not null,
	homepage varchar(50) not null
) ENGINE=MyISAM;
CREATE TABLE confirm (
	id int(10) unsigned not null primary key auto_increment,
	userid int(8) unsigned not null,
	email varchar(75) not null,
	register tinyint(1) unsigned not null,
	confirm varchar(32) not null,
	dateline int(10) unsigned not null
) ENGINE=MyISAM;
CREATE TABLE timezone (
	timezone float(4) not null primary key,
	label varchar(50) not null
) ENGINE=MyISAM;
INSERT INTO timezone (timezone, label) VALUES
(-12, 'Eniwetok, Kwajalein'),
(-11, 'Midway Island, Samoa'),
(-10, 'Hawaii'),
(-9, 'Alaska'),
(-8, 'Pacific Time (US & Canada)'),
(-7, 'Mountain Time (US & Canada)'),
(-6, 'Central Time (US & Canada), Mexico City'),
(-5, 'Eastern Time (US & Canada), Bogota, Lima'),
(-4.5, 'Caracas'),
(-4, 'Atlantic Time (Canada), La Paz, Santiago'),
(-3.5, 'Newfoundland'),
(-3, 'Brazil, Buenos Aires, Georgetown'),
(-2, 'Mid-Atlantic'),
(-1, 'Azores, Cape Verde Islands'),
(0, 'Western Europe Time, London, Lisbon, Casablanca'),
(1, 'Brussels, Copenhagen, Madrid, Paris'),
(2, 'Kaliningrad, South Africa'),
(3, 'Baghdad, Riyadh, Moscow, St. Petersburg'),
(3.5, 'Tehran'),
(4, 'Abu Dhabi, Muscat, Baku, Tbilisi'),
(4.5, 'Kabul'),
(5, 'Ekaterinburg, Islamabad, Karachi, Tashkent'),
(5.5, 'Bombay, Calcutta, Madras, New Delhi'),
(5.75, 'Kathmandu'),
(6, 'Almaty, Dhaka, Colombo'),
(6.5, 'Yangon, Cocos Islands'),
(7, 'Bangkok, Hanoi, Jakarta'),
(8, 'Beijing, Perth, Singapore, Hong Kong'),
(9, 'Tokyo, Seoul, Osaka, Sapporo, Yakutsk'),
(9.5, 'Adelaide, Darwin'),
(10, 'Eastern Australia, Guam, Vladivostok'),
(11, 'Magadan, Solomon Islands, New Caledonia'),
(12, 'Auckland, Wellington, Fiji, Kamchatka');
CREATE TABLE online (
	sid varchar(32) not null primary key,
	userid int(8) unsigned not null,
	username varchar(25) not null,
	usergroupid int(2) unsigned not null default 1,
	ip varchar(15) not null,
	lastactivity int(10) unsigned not null,
	lastpage varchar(100) not null
) ENGINE=MyISAM;
ALTER TABLE user ADD INDEX username (username);
ALTER TABLE online ADD INDEX lastactivity (lastactivity);

Save this as usersys.sql and import it into mysql console (or phpmyadmin).
Replace root with your username, and if you have a password add -p so it prompts for your password.
If you are connecting to a remote MySQL server and the host allows this, add -h yoursite.com
Bash Code:
mysql -u root

Execute all queries in usersys.sql
SQL Code:
\. usersys.sql

Now the fun part: coding. But before we get too far into PHP coding, we need to create a config file with MySQL settings as well as permission bitfields.
config.php
PHP Code:
<?php
// mysql configuration
$dbconf = array(
	'host' => 'localhost',	// the mysql host, usually localhost
	'user' => 'root',	// the mysql username
	'pass' => '',		// the mysql password
	'persist' => true,	// whether or not to use a persistant connection
	'dbname' => 'usersys'	// the mysql database to select
);

/*
 * our permission bitfields
 * each permission group has a list of bitfields starting at 1 and doubling for each option in that group.
 * you can add many groups but keep in mind that the "perms" field in "usergroup" table is 255 chars long.
 * the "perms" field is a serialized array of permissions with the keys being the name of the permission group
 * and the value being the added value of all the selected permissions for that permission group
 * so try to keep to less than 10 permission groups, otherwise use text instead of varchar for the field type
 */
$perms = array(
	'home' => array(
		1 => 'canview'
	)
	/* heres is a further example since we dont really have anything to allow / disallow right now.
	 * this is just an exmaple and is just for you to get an idea of what you can do with it.
	 * anytime you change these groups, go and edit each usergroup in admincp and update their permissions
	 * in a real enviroment, you probably would remove the above group "home" and add other groups
	'forums' => array(
		1 => 'canlist',		// whether or not a user can list forums / threads
		2 => 'canview',		// whether or not a user can view threads
		4 => 'cancreate',	// whether or not a user can create threads
		8 => 'canreply',	// whether or not a user can reply to threads
		16 => 'canrate',	// whether or not a user can rate threads
		32 => 'canmod',		// whether or not a user can moderate
		64 => 'canadmin'	// whether or not a user can administer
	)
	*/
);
?>

Here is how this system will work. All requests need to go through index.php. index.php will include the backend as well as the required file(s) for the requested page. You can change this to use mod_rewrite and instead of getting values from $_GET, get values from $_SERVER['REQUEST_URI'] (edit the links in other files to link to the new URLs as well). For this tutorial I will use $_GET because mod_rewrite is not always an option for everyone.

First, we need a basic layout. I made this very simple layout for the sake of this tutorial. Save this as layout.php
PHP Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>PHPwned.org user system<?php if( $title ){ echo ' -//- '. $title; }?></title>
	<link rel="stylesheet" type="text/css" href="global.css" />
	<?php
	// in case any css or javascript is needed in the head (<style> and <link> is ONLY valid in <head>)
	echo $header;
	?>
</head>
<body>
	<div id="container">
		<h1 id="logo"><a href="http://<?php echo $_SERVER['HTTP_HOST'];?>/">PHPwned.org user system tutorial</a></h1>
		<div id="userinfo">
			<?php echo $userinfo;?>
		</div>
		<div id="content">
			<?php echo $content;?>
		</div>
	</div>
	<div style="text-align: center;">
		<?php echo $timeinfo;?>
	</div>
</body>
</html>

And the CSS file to go with the layout: Save this as global.css
CSS Code:
/* Styles for the PHPWned.org user system tutorial. This is not meant to be pretty, just to provide some basic layout */
* {
	margin: 0;
	padding: 0;
}

/* global font, and no margin/padding on body */
body {
	padding-bottom: 14px;
	font-family: sans, sans-serif;
	font-size: 12px;
	background-color: #222222;
	color: white;
}


/* global link styles */
a {
	color: black;
	text-decoration: none;
}

a:hover {
	text-decoration: underline;
}


/* no border on images. border is not an attribute in xhtml 1.0 strict you can only set it with css */
img {
	border: 0;
}


/* the container */
#container {
	margin: 35px;
	padding: 25px;
	border: 5px dashed gray;
	background-color: white;
	color: black;
}


/* .clear : to clear our floats with either a br or a div */
.clear {
	clear: both;
	line-height: 0;
}


/* Form elements */
label {
	display: block;
	float: left;
	width: 130px;
	white-space: nowrap;
	font-weight: bold;
	text-align: right;
	margin: 4px 3px 1px 0;
}

input, select, textarea {
	float: left;
	margin: 2px 0 2px 1px;
	border: 1px solid #aaaaaa;
}


/* heading styles */
h1, h2, h3, h4, h5, h6 {
	border-bottom: 1px solid #dadada;
	margin: 1px;
	padding: 1px;
}

h1 {
	font-size: 18px;
	color: #222222;
}

h2 {
	font-size: 16px;
	color: #444444;
}

h3 {
	font-size: 14px;
	color: #666666;
}


/* paragraph style */
p {
	margin: 2px 1px 14px 1px;
	text-indent: 15px;
}


/* list style */
ul {
	margin: 2px 2px 2px 25px;
}


/* logo / site title */
h1#logo {
	border: 0;
}

h1#logo a {
	/* if you have a logo image uncomment this stuff and change logo path and width/height
	background-image: url('images/logo.png');
	background-repeat: no-repeat;
	background-position: 50% 50%;
	width: 100px;
	height: 60px;
	text-indent: -9999px;*/
	display: block;
	margin: 1px 0 5px 0;
	padding: 2px;
}

/* #userinfo : the login form elements */
#userinfo {
	border: 1px solid #dadada;
	background-color: #fafafa;
	padding: 10px;
	margin-bottom: 10px;
}

#userinfo label {
	display: inline;
	float: none;
}

#userinfo input {
	float: none;
}

#userinfo form div div {
	float: left;
	padding: 2px;
}

#userinfo #rightlinks {
	float: right;
	margin-top: 10px;
}

#userinfo #rightlinks a {
	font-size: 14px;
	font-weight: bold;
	display: block;
	float: left;
	padding: 3px 10px;
	border: 1px dashed #cccccc;
}

#userinfo #rightlinks a:hover {
	background-color: white;
	border: 1px solid #cccccc;
}


/* the date of birth form */
.dob div {
	float: left;
	margin: 2px;
}

.dob select, .dob label {
	display: inline;
	float: none;
}


/* online now members style */
#whosonline {
	border: 1px solid #aaaaaa;
	border-left: 15px solid #aaaaaa;
	padding: 3px;
}


/* user profile styles */
.profile_right {
	float: right;
	width: 155px;
	padding: 5px;
	text-align: center;
	background-color: #fafafa;
	border: 1px solid #cccccc;
}

.profile_right img {
	display: block;
	margin: 2px auto;
}

.profile_info {
	width: 75%;
	margin-left: 30px;
	background-color: #fafafa;
	border: 1px dashed #cccccc;
}


Next, we need our backend functions. This will be everything from MySQL, datastore, and session handling.
Save this as functions.php
PHP Code:
<?php
/**
 * Our initialization function. Connects to MySQL and selects a db.
 * @param array $dbconf - The array containing mysql config
 * @param array $dskeys - An array of datastore names to load
 * @param bool $session - Whether or not to initialize a session. Set to false if this is a cron job
 * @param bool $login - Whether or not this is the login page
 * @param bool $logout - Whether or not this is the logout page
 * @return bool - true if connecting to mysql went okay, false if error.
 */
function Main($dbconf, $dskeys=false, $session=true, $login=false, $logout=false)
{
	// if $dbconf['persist'] use a persistant mysql connection, otherwise a non-persisting one.
	$GLOBALS['dbc'] = $dbconf['persist']? mysql_pconnect($dbconf['host'], $dbconf['user'], $dbconf['pass']) : mysql_connect($dbconf['host'], $dbconf['user'], $dbconf['pass']);
	
	// if we couldnt connect to mysql
	if( !($GLOBALS['dbc']) )
	{
		// print an error
		print_errors("Could not connect to MySQL.");
		
		// return false so our script knows not to continue
		return false;
	}
	
	// init $qtime and $qcount (query time and query counter)
	$GLOBALS['qtime'] = $GLOBALS['qcount'] = 0;
	
	// select database for this mysql connection.
	$GLOBALS['dbid'] = mysql_select_db($dbconf['dbname'], $GLOBALS['dbc']);
	
	// if any datastore names were set to be loaded
	if( $dskeys )
	{
		// load them
		datastore($dskeys);
	}
	
	// unset $dbconf and $dskeys globally, we wont need them anymore
	unset($GLOBALS['dbconf'], $GLOBALS['dskeys']);
	
	// if we using sessions
	if( $session )
	{
		// if this is the login page and user is not already logged in
		if( $login && !$_SESSION['userid'] )
		{
			// try to login
			login();
		}
		// otherwise if its the logout page and the user is logged in
		else if( $logout && $_SESSION['userid'] )
		{
			// log out. userid and password cookies are unset they dont log right back in below
			logout();
		}
		
		// if user is not logged in and cookies are set, log them in
		if( !$_SESSION['userid'] && $_COOKIE['userid'] && $_COOKIE['password'] )
		{
			// if login is unsuccessful
			if( !login() )
			{
				// then delete the cookies so we dont keep tryin each time
				delete_cookie('userid');
				delete_cookie('password');
			}
		}
		
		// do session stuff
		session();
	}
	
	// return true so our script knows everything went okay
	return true;
}

/**
 * the "online now" garbage collector. deletes old sessions from the online table
 * @param bool $force - Whether or not to force a datastore rebuild (eg. the user just logged in or just started their session)
 * @return void
 */
function collect_garbage($force=false)
{
	// delete old sessions
	query("DELETE FROM online WHERE lastactivity<". (TIMENOW-$GLOBALS['session_timeout']));