User Personalization with PHP: Beginning the Application

[next]

In this tutorial we will build an online bookmark system. This will be used to create a database for storing all our URL's and their descriptions. There are many commercial and non-commercial applications that offer almost the exact functionality that we will create in our application; the difference is that we have full control over our application, allowing us to tweak it to suit our needs.

Functional Requirements

So what do we want this application to do?

It must enable users to login and out of the system. This is very important if you are using this system online or on a shared computer.
Users must be able to manage their own bookmarks. This includes storage, changes, retrieval and deletions.
The system should offer user recommendations of other sites.
It must display news headlines.
Users must be able to personalize their corner of the site.

When we translate these requirements programmatically, we have the following:

The program will authenticate every user. The method of authentication will include a username and password, which will be checked against the stored passwords. The passwords will be encrypted when stored for extra security.
A verification code line has been included on the login form for those who want to use the application online. This will prevent automatic login by a robot. We will also take a detailed look at different ways in which this method can be implemented.
To recommend other sites for users to visit, we will use the bookmarks that are stored in the database by other users. If, for example, more than one user stores the same bookmark, then we will use it as a recommendation for another user.
As far as personalizing the application is concerned, the user will be able to upload an image, which will be displayed when they login. In addition, they will also be able to implement their own color scheme.

The Database

All the information about the user and their bookmarks will be stored in two tables; one will be called 'users' and the other 'bmarks'. Because one user can have many bookmarks, we need to find a way to link information stored in the bookmarks table to a user in the users table. The tables will be created out as follows:

Bookmarks table

Field	Description
bid	Primary key assigns unique number for every bookmark
uid	Links the bookmark to a user in the users table
URL	The URL
desc	Description of the URL

Users table

Field	Description
uid	Assigns unique number to user
uname	Stores the user name
upass	Stores the password
email	Stores the email address of the user
level	Saves the access level of the user
bgc	Stores the color scheme of the user
img	Stores the image of the user

We will store the user id as a foreign key to represent the user in the bookmark table. Then, each time a particular user stores a bookmark, their userID is going to be stored in the bookmarks table as well. This will make it easy to retrieve their bookmarks when we run queries. I've also included some sample data which we will be using throughout this series. To set up the main database, you will need to create a database called 'personalization' and then run the SQL code below. Simply copy and paste it into your MySQL client:

# Table structure  for table 'bmarks'
#
CREATE TABLE  'bmarks' (
  'bid' tinyint(4) NOT NULL auto_increment,
  'uid' tinyint(4) NOT NULL,
  'url' varchar(50) NOT NULL,
  'desc' varchar(50) NOT NULL,
  PRIMARY KEY  ('bid')
) ENGINE=InnoDB  AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
#
# Dumping data for  table 'bmarks'
#
INSERT INTO 'bmarks'  VALUES (1, 1, 'https://www.common.com/phpworks', 'common php work');
INSERT INTO 'bmarks'  VALUES (2, 1, 'https://www.common.com/phpfunctions', 'about php');
INSERT INTO 'bmarks'  VALUES (3, 2, 'https://www.personal.com/home', 'my personal page');
INSERT INTO 'bmarks'  VALUES (4, 2, 'https://www.common.com/phpworks', 'php works');
INSERT INTO 'bmarks'  VALUES (5, 2, 'https://www.common.com/phpfunctions', 'about php functions');
INSERT INTO 'bmarks'  VALUES (6, 4, 'https://www.common.com/phpworks', 'about php works');
INSERT INTO 'bmarks'  VALUES (7, 4, 'https://www.common.com/phpfunctions', 'about php functions');
INSERT INTO 'bmarks'  VALUES (8, 4, 'https://www.personal.com/activities', 'my personal site');
# --------------------------------------------------------
#
# Table structure  for table 'users'
#
CREATE TABLE 'users'  (
  'uid' tinyint(4) NOT NULL auto_increment,
  'uname' varchar(20) NOT NULL,
  'upass' varchar(32) default NULL,
  'email' varchar(30) NOT NULL,
  'level' varchar(6) NOT NULL,
  'bgc' varchar(15) NOT NULL,
  'img' char(50) NOT NULL,
  PRIMARY KEY  ('uid'),
  KEY 'log'  ('uid','uname','upass','email','level')
) ENGINE=InnoDB  AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
#
# Dumping data for  table 'users'
#
INSERT INTO 'users'  VALUES (1, 'lnam', '1a1dc91c907325c69271ddf0c944bc72', 'lnam@lnam.com', 'admin',  'red', 'user.gif');
INSERT INTO 'users'  VALUES (2, 'jane', '5844a15e76563fedd11840fd6f40ea7b', 'jane@web.com',  'normal', 'green', 'user.gif');
INSERT INTO 'users'  VALUES (4, '#goms', ' 9b8d9480d8c452f5287bcba7101ae92', '#goms@web.com',  'admin', 'blue', 'user.gif');

At a later stage we will need to make some changes to the current tables, but for now the database is complete.