MYSQL / PDO PHP系统---寻找全程输入

So, I have a bit of beginner programming knowledge (not much web-based) but I've managed to figure out how to set up a MYSQL Database & how to make a PHP file that will pull the rows from the test table I have made in the database.

I am looking for some input or pseudocode or general guidance on what steps I would need to take to achieve what I have in mind.

I know I will likely need to research the necessary relational calls to the few data items I will collect.

I am looking to make my volunteer job easier. I volunteer at a place that coordinates others volunteers. My job is to keep track of their certifications (in a physical filing box)---I am using someone else's old excel sheets.

I want to present to the director a more efficient way for us to gather key data and then easily share it with the volunteers.

There is not an issue with privacy (none of the data I would gather has names or personal information, just whether or not this particular volunteer has a certain certification---I would associate it with their volunteer number and then just cross-reference it to my physical files)

so, what I want to set up is a PHP/MYSQL system to achieve this:

Have a user log in (with Facebook or Gmail--everyone must have one or the other to use it)**this point is least important at this stage, as I just want to evidence a system with working functionality

Have a user be issued by a php page a checklist of certifications. This is basically all they will ever see when they log in.

Have that user check which ones they have, leaving the others empty.

Have the values save and be persistent. (either with a save button or just automatically)

Have the checklist be shareable (by the user) with a web-link; the checklist being populated by values that they update as needed.

ASSUMPTION I assume I would need two tables?

A Table for maintaining the values of the checklist and the associated logos: TABLE 1

I ASSUME my second table would be for tracking USERS

TABLE 2

I assume there would be relational calls between the tables?

So, if this is too broad a question, I understand. I am looking just for an approach to tackling it, not necessarily any too detailed of a response. Just a framework for me to move forward.

You need 3 tables here:

1. List of certifications.
   • You designed it correctly, except for minor note: I would've changed item# to certification_id to stress that it's a primary-key.
   • Storing a logo in the database while possible is not a recommended practice. Usually in the db you store only a URL of the image or maybe a string like '/www/uploads/logo_1.jpg' that you can use to find an image on the disk.
2. The second table users will be the list of your users. It should have user_id (int) and volunteer_number. If you don't need any other info, it will be enough.
3. Now the third table user_certs will connect these two and contain information about which volunteer has which certification. The fields will be:
   • user_id (id from the second table)
   • certification_id (id from the first table)

It is actually already enough to store the information. You can tell if the user has specific certification by looking into the user_certs table. If there is a record, (s)he has the cerification.

Now about the sharing your list. You simply can give each user a link like this: example.org/certificates.php?user_id=12345. In PHP when you render this page you will have this data:

• user_id received from $_GET
• list of all possible certifications from table 1
• list of certifications passed by user_id=12345 received from table 3.

This is enough to build that sharable list. If you later need some more data (like the date of the certification, name of the testcenter etc) you can extend the user_certs table with new fields.

Your DB design should meet two criteria:

1. No info is unnecessarily repeated anywhere
2. If a user or certification is deleted, all other records containing that user or certification should also be

I would have 3 InnoDB tables

users
    id //perhaps volunteer id if they're unique. Primary key
    login //email used to login
    api_service //not sure about this; gmail or facebook perhaps?
    password //IF you choose to implement login yourself, remove api_service
    ... //anything else describing the user (not certifications)
    created //date account was added

certifications
    id //unique identifier. Primary key
    name //what is it called
    ... //anything else describing an individual certification

user_certs
    id //unique identifier. Primary key
    user //Foreign key to this table. Primary key from users table
    cert //Foreign key to this table. Primary key from certifications table
    issued_on // date the user received the certification
    expires // date the cert will expire
    ...//anything else describing the binding such as who granted cert or 
       //what is cert ID number

The whole login mechanism can't really be worked out in a paragraph. I'm not familiar with Facebook or Google login APIs so I couldn't help you there anyway. Sorry.

Some example queries:

To show all certifications by one user (given his volunteer ID)

SELECT certifications.name, certifications.id 
FROM users
LEFT JOIN user_certs ON user_certs.user = user.id
LEFT JOIN certifications ON certifications.id = user_certs.cert
WHERE users.id = $volunteerID

To see all users with a given certification name

SELECT users.id
FROM users
LEFT JOIN user_certs ON user_certs.user = user.id
LEFT JOIN certifications ON certifications.id = user_certs.cert
WHERE certifications.name = $certName