0

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?

user3205002
  • 25
  • 1
  • 5
  • possible duplicate of [Using sessions & session variables in a PHP Login Script](http://stackoverflow.com/questions/10097887/using-sessions-session-variables-in-a-php-login-script) also take a look at [this](https://stackoverflow.com/questions/8485280/php-login-script), and this [critique](https://stackoverflow.com/questions/8485280/php-login-script) – Huey Jan 28 '14 at 10:44
  • NO, this is a totally different question! – user3205002 Jan 28 '14 at 10:49
  • 1
    I'm sorry to say that, Mr user3205002, but the tables you show in your post are excatly what you must not do in a relational database. They are full of duplicates, and if you start using them you will soon be riddled with inconsistencies. I advise you to read some basic tutorials on how to design a database. Some textbooks might help too. – kuroi neko Jan 28 '14 at 10:53
  • I'm sorry, I misread your question. How does your PHP script work? Posting it, or at least the relevant part here will help determine the best way to determine the rank of the person. – Huey Jan 28 '14 at 10:56
  • I agree @kuroineko, you should definitely look into database normalisation. In relation to your questions though, you might have a single table for this and add a 'type' column that specifies if the entity is a user, employer or admin. – adamS Jan 28 '14 at 10:56
  • Thanks, any good database design tutorials or textbooks? About my tables 'full of duplicates', I divided users by groups. When I did this, I referred to some source code online to get this idea. Seems many PHP source code are not that good designed? – user3205002 Jan 28 '14 at 12:03

0 Answers0