QUuestion on PHP Login form handling

Databases
1

My site has a login form which looks like this:
Email:__________
Password:________
Login

The website system has three types of users: users, employers and administrators, and they are stored in three tables:


CREATE TABLE users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  email VARCHAR(80) NOT NULL,
  pass CHAR(60) NOT NULL,
  user_phone VARCHAR(11) NOT NULL,
  user_address VARCHAR(250) NOT NULL,
  active CHAR(32) NULL,
  last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
  last_login_ip VARCHAR(15) NOT NULL,
  registration_time DATETIME NOT NULL,
  registration_ip VARCHAR(15) NOT NULL,
  PRIMARY KEY (user_id),
  UNIQUE KEY (email),
  INDEX login (email, pass)
) ENGINE = INNODB;

CREATE TABLE employers (
  employer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(20) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  company_name VARCHAR(80) NOT NULL,
  email VARCHAR(80) NOT NULL,
  pass CHAR(40) NOT NULL,
  employer_phone VARCHAR(11) NOT NULL,
  employer_mobile VARCHAR(11),
  employer_address VARCHAR(250) NOT NULL,
  active CHAR(32) NULL,
  last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
  last_login_ip VARCHAR(15) NOT NULL,
  registration_time DATETIME NOT NULL,
  registration_ip VARCHAR(15) NOT NULL,
  PRIMARY KEY (employer_id),
  UNIQUE KEY (email),
  INDEX login (email, pass)
) ENGINE = INNODB;



CREATE TABLE administrators (
   admin_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   first_name VARCHAR(20) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   email VARCHAR(80) NOT NULL,
   pass CHAR(40) NOT NULL,
   last_login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                     ON UPDATE CURRENT_TIMESTAMP,
   last_login_ip VARCHAR(15) NOT NULL,
   created_time DATETIME NOT NULL,
   PRIMARY KEY (admin_id)
) ENGINE = INNODB;

After a user logs in, the login.php script will handle it. How does login.php tell this person is a user, employer or administrator? Login.php needs to validate users by query relative tables. Do I need to create a new table indicating the relationship between users/employers/administrators? If so, how will the new table look like?

Also, where can I find some PHP website source code that contains examples like this?

2

The most straight forward approach would be to just query each table until you got a match. Not very elegant but it would work.

A more traditional approach would be to have only one users table with a role column set to either User,Employee or Admin. Easier to manage one table instead of three.

3

A more traditional approach would be to have only one users table with a role column set to either User,Employee or Admin. Easier to manage one table instead of three.

Thanks, if I just use one table, then there will be a ‘user_group’ column indicating User, Employee or Admin, like user_group = 0(User), user_group = 1(Employee), user_group = 2(Admin), right? I thought of just using one table, but the problems is there would be many redundant columns because User, Employee or Admin have their many different columns.

Could I just add a new table showing the relationship between User, Employee or Admin?

Does Wordpress source code contain such similar code that I can refer to?

4

Is the end app a job search website?

You should have a roles table and a lookup table (user_id ad role_id), some users might be administrators as well, the company might advertise a job on it’s own site.

Will all administrators have equal rights and permissions?

5

SpacePhoenix,

It’s a job seek website, jobseekers can search a job and apply for it; employers can post their jobs on the site.

How should the roles table and loopup talbe look like? Is there any sample systems?

“some users might be administrators as well” – This is possible, but in my website it is not that complicated, just normal users and administrators and no intersection between them, since the scale of the site is relatively small.

"Will all administrators have equal rights and permissions? " – Yes, they have equal rights and permissions(maybe just 1 or 2 administrators that can view all the users, active an user account, edit or delete a user, etc.).

Where can I find some good sample code for web apps like this?

6

The new table approach would be to have something like:

accounts - id,email,password,role (employee,user,admin)

users - id,account_id,user specific columns
employees - id,account_id,employee specific columns
admins - id,account_id,admin specific columns

So your login script uses accounts to log someone in. Once it knows the role it can pull in additional information from the other tables using account.id.

The Doctrine 2 ORM documentation has a chapter on this.
http://docs.doctrine-project.org/en/latest/reference/inheritance-mapping.html
I know you might not be ready to use an ORM but reading through the chapter might give you some ideas.

7

ahundiak,

Thanks for the advice. You mentioned earlier:“The most straight forward approach would be to just query each table until you got a match. Not very elegant but it would work.” — I tried this approach, my login.php script will first query Users table using email and pass, if not found, then query Employers table; if not found, then query Admins table.

This approach works most of the time, but what if a User and a Employer happen to have the same email address and password? The email address could be the same, the password is using SHA() to encript, so two SHA(‘pass’) would never be the same?

Also thanks for the Doctrine 2 ORM documentation link, I am reading it now.

8

The duplicate email/password is one of the flaws of your individual table scheme. That is why almost every system just uses one table for the actual sign in process. And why you probably should as well.