Creating logins using a WP Data Access table

  • Resolved Mushfiqur

    (@aa11plus)


    5 years, 1 month ago

    Hi Pete (and all),

    I’m currently building simple tables to store all our school’s data (students, teacher, admin, rooms, subjects, classes, register, results, etc). My project plan is to first get all my school’s data into separate, logical and rational tables. Then, go on to create lookups and form relationships between the tables.

    My question is this:

    If I create 3 tables (students, teachers and admin) can I add a few extra columns to each (username, password and group) and use those tables to create WP user logins? How best could I do that, please? If not, is there a work-round to be able to link the student to a login so that when a student (or staff or admin) logs in, they only see the data in all tables assigned to them (e.g. what class they are in, their attendance, their own results, the subjects and classes they are assigned to, their own timetable).

Viewing 6 replies - 1 through 6 (of 6 total)
  • Thread Starter Mushfiqur

    (@aa11plus)

    I’ve just raed the ‘id of the active user in WHERE Clause’ and the ‘User/student record for print?’ threads and watched a few tutorials about the $$USER$$ (video: Managing roles and user access). What I’m not understanding is how do I link the WP User to a row in my students table (or tutor or admin tables), please? So, if I have a WP user login called joe.bloggs and Joe Bloggs is in my student table, how do I link the two together so that all entries pertaining to Joe Bloggs is accessed when joe.bloggs logs in?

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi aa11plus,

    The easiest way to link a wp user login to the student table is to add a column to your student table that contains the login id. Please follow these steps:
    A Add a column to the student table (in this example: wp_user_id)
    B Create a table option set for the student table that hides column wp_user_id
    (make sure all users, except the app administrator, use this option set)
    C Create another table option set for the student table that shows column wp_user_id
    (this option set is for your app administrator to allow to add the user id to
    a student)
    D Add a lookup to the table option set create under C
    (lookup table is wp_users = WordPress table containing user logins)
    E Make sure student records have the right wp user id
    F Edit the default WHERE clause of your Data Projects page:
    wp_user_id = $$USERID$$

    Let me know if this works…

    Best regards,
    Peter

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Not sure if this helps, but here is the part of the data model that shows the relationship between the student table and the wp_users table:
    https://wpdataaccess.com/wp-content/uploads/2020/01/student_wp_user_id.png

    Best regards,
    Peter

    Thread Starter Mushfiqur

    (@aa11plus)

    Thanks for the added help; all visuals and step-by-step instructions really do help as I’m not an SQL programmer in any way, shape or form. Love your app and it’s easy to get started. It’s easy to get the school data into the app. It’s just getting use to all the logic and terminology side of things.

    I’ll try and understand what you’ve sent and see if I can get it working. Then will return and feedback.

    Thanks ever so much!

    Thread Starter Mushfiqur

    (@aa11plus)

    Peter, it worked!!!

    Many thanks for the advice. As I said, I’m in no way, shape or form an SQL programmer or dB designer and your instructions were enough to get me through.

    Many, many thanks…

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Well done! ??

    Best regards,
    Peter

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Creating logins using a WP Data Access table’ is closed to new replies.