SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    QUuestion on PHP Login form handling

    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:

    Code:
    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. #2
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,027
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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/...e-mapping.html
    I know you might not be ready to use an ORM but reading through the chapter might give you some ideas.

  7. #7
    SitePoint Member
    Join Date
    Jan 2014
    Location
    LA, USA
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •