Need help implementing an idea

[jay93]

Edit:

I've moved this post into its own thread, as it really is a separate topic.

This was split from http://www.sitepoint.com/forums/show...PASSWORD-RESET! (so reading that thread may help)

--cpradio

i was working on this website, where teachers can publish students' marks...my plan is to let the teacher open the mysql table and update the column "marks" for each "username" of id "id"... and anytime the marks are updated, each student gets an email... is my logic right? and is there any other way i can go about this??

2. regarding the emailing part, i have just conjured the idea, but i dont have much idea on how to implement it... could you please give me some hints or tell me of some code?

i know i've disturbed you for almost a week now, but this is kind of the last issues i am on, and then i ll be taking a very long break... my sincerest gratitude to you...

[cpradio]

Do you have any table schemas (structures) you can share?
This may be terminology issue, but do you really plan on letting the teachers open/update the MySQL table directly or do you plan to put a form in front of it?

As for the e-mailing part, that should be simply enough, just use mail()

[jay93]

[Inline] [ Edit ] [ Create PHP Code ]

# Name Type Collation Attributes Null Default Extra Action
1 id int(10) UNSIGNED No None AUTO_INCREMENT Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
2 username varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
3 email varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
4 security varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
5 marks int(255) No None Change Drop Browse distinct values Primary Unique Index Spatial


this is my table in phpmyadmin, and actually the ideal thing would have been to present teachers with a form to insert marks, but i m not sure on how to do that, so the raw plan i had was to make the teachers edit the mysql table...sounds pretty crude and primitive though...


2.and regarding the mail(), could you please explain more on how to use that function. for instance like
send mail""" if ''' table is updated"''' to all members in the table....

[cpradio]

[Inline] [ Edit ] [ Create PHP Code ]

# Name Type Collation Attributes Null Default Extra Action
1 id int(10) UNSIGNED No None AUTO_INCREMENT Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
2 username varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
3 email varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
4 security varchar(255) latin1_swedish_ci No None Change Drop Browse distinct values Primary Unique Index Spatial Fulltext More
5 marks int(255) No None Change Drop Browse distinct values Primary Unique Index Spatial


this is my table in phpmyadmin, and actually the ideal thing would have been to present teachers with a form to insert marks, but i m not sure on how to do that, so the raw plan i had was to make the teachers edit the mysql table...sounds pretty crude and primitive though...

Agreed, a form would be simple enough (if that interests you). You will simply query the table for all records, loop through them generating your form entries, one thing you need to be sure you do is treat them as an array of form items with names like "marks[<?php echo $row['id']; ?>]" so you know which id the marks applies to.

2.and regarding the mail(), could you please explain more on how to use that function. for instance like
send mail""" if ''' table is updated"''' to all members in the table....

You can;t easily send an email from MySQL, there are options, but using a form for the updates would make it more seamless (in my opinion).

If you decide to go with a form for updating marks, I can help you through the entire process. If you want to the trigger approach as outlined in the link above, then you will need to develop a script that runs on a cronjob to send emails accordingly (I can help with that too).

Let me know which way you want to take this project.

[jay93]

thank you very much... i really needed someone to guide me through this...regarding the form, the issue i had in mind was if we made such a form, will it be accessible to students too?? because if that happens then students will be able to change their marks, right?

and about the email thingy, actually i was planning to do it through php, same way as my previous website (remember the portfolio and the password reset stuff)...so basically,
i ll have a php controller, and a template.... much like reset.php (controller), and reset_form( template) from above... in fact i'm building this new website same way as the old, so i still have my old files config.php, login.php. logout.php, etc...just like the previous one... and in this new website, i m using the same reset method as the prev one from reset.php (remember??) to enable students to reset their passwords...

[jay93]

.............................. and right now i have three folders .... html (which contains 3 folders called
css,
img
and js respectively, and 6 files namely
index.php,
login.php,
logout.php,
pass.php (just like above)
register.php and
reset.php

2nd folder is "includes" which contains constants.php, functions.php and config.php


3rd folder called templates (contains
header.php,
footer.php
login_form.php,
pass_form.php,
portfolio.php ((I AM GOING TO CHANGE THIS TO STH LIKE A PAGE WHERE STUDENTS WILL SEE THEIR MARKS AND OTHER STUDENT RESOURCES LIKE NOTICES, ANNOUNCEMENTS, ETC),
register_form.php aand
reset_form.php

[cpradio]

thank you very much... i really needed someone to guide me through this...regarding the form, the issue i had in mind was if we made such a form, will it be accessible to students too?? because if that happens then students will be able to change their marks, right?

Do you have a way to tell if the logged in user is a student or a teacher? If you do, you just simply have to validate they are a teacher, then show the page, otherwise, show an Access Denied message/page.

and about the email thingy, actually i was planning to do it through php, same way as my previous website (remember the portfolio and the password reset stuff)...so basically,
i ll have a php controller, and a template.... much like reset.php (controller), and reset_form( template) from above... in fact i'm building this new website same way as the old, so i still have my old files config.php, login.php. logout.php, etc...just like the previous one... and in this new website, i m using the same reset method as the prev one from reset.php (remember??) to enable students to reset their passwords...

That would work, so long as your initial view is showing a form for the teacher to fill out, and after it processes the entry, it kicks off an email to the student. Thus making it so the teacher can update the marks within the page.

[jay93]

actually i have no idea on how to distinguish between students and teachers???could you please help me with that/?

[2ndmouse]

Presumably, your db will differentiate between students and teachers. If so, you could add a login page which checks the db on submit. If the login is for a teacher, display the form, if not display a rejection.

[jay93]

one sec, so if this is my code for login.php how can i modify it to cater for teachers?? and the new database i create for teachers, called "teachers", do i keep the same columns as those for students, i mean identical to them?? like "id", username, security, email, hash??

<?php

    // configuration
    require("../includes/config.php"); 

    // if form was submitted
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {
        // validate submission
        if (empty($_POST["username"]))
        {
            apologize("You must provide your username.");
        }
        else if (empty($_POST["password"]))
        {
            apologize("You must provide your password.");
        }

        // query database for user
        $rows = query("SELECT * FROM users WHERE username = ?", $_POST["username"]);

        // if we found user, check password
        if (count($rows) == 1)
        {
            // first (and only) row
            $row = $rows[0];

            // compare hash of user's input against hash that's in database
            if (crypt($_POST["password"], $row["hash"]) == $row["hash"])
            {
                // remember that user's now logged in by storing user's ID in session
                $_SESSION["id"] = $row["id"];

                // redirect to portfolio
                redirect("/");
            }
        }

        // else apologize
        apologize("Invalid username and/or password.");
    }
    else
    {
        // else render form
        render("login_form.php", ["title" => "Log In"]);
    }

[cpradio]

one sec, so if this is my code for login.php how can i modify it to cater for teachers?? and the new database i create for teachers, called "teachers", do i keep the same columns as those for students, i mean identical to them?? like "id", username, security, email, hash??

<?php

    // configuration
    require("../includes/config.php"); 

    // if form was submitted
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    {
        // validate submission
        if (empty($_POST["username"]))
        {
            apologize("You must provide your username.");
        }
        else if (empty($_POST["password"]))
        {
            apologize("You must provide your password.");
        }

        // query database for user
        $rows = query("SELECT * FROM users WHERE username = ?", $_POST["username"]);

        // if we found user, check password
        if (count($rows) == 1)
        {
            // first (and only) row
            $row = $rows[0];

            // compare hash of user's input against hash that's in database
            if (crypt($_POST["password"], $row["hash"]) == $row["hash"])
            {
                // remember that user's now logged in by storing user's ID in session
                $_SESSION["id"] = $row["id"];

                // redirect to portfolio
                redirect("/");
            }
        }

        // else apologize
        apologize("Invalid username and/or password.");
    }
    else
    {
        // else render form
        render("login_form.php", ["title" => "Log In"]);
    }

Before we get into that, can you provide the schema/structure for your users table?

[jay93]

CAP.JPG

sure, it's as above...

[cpradio]

Okay, my recommendation would be to add a new column to that table that designates if the user is a student or a teacher.

[jay93]

okay, so what should the type of this new column be?? should it be varchar, or a boolean??

[cpradio]

Okay, first thing to do when defining a new column is to consider what types of values you want it to hold. So right now you definitely know you need to know the difference between a Student username and a Teacher username, what about an Administrator or Principal? Would those designations be helpful later on?

If so, I'd make the column varchar(20) and store one of the following per record, 'Student', 'Teacher', 'Principal', 'Administrator'

[jay93]

for now, only teachers and students will suffice...

so is this fine, for the table users (for students) ?? and what about teachers' table?

CAP.JPG

[cpradio]

You can use the same table for both. So I'd rename the student column to be "user_type".

Then when you insert a student, you will populate user_type with Student, and when you input a teacher, you will populate it with Teacher.

[jay93]

sounds good, so now there is an issue with registartion too i guess? because the user will have to specify whether he is a teacher or a student, right?

wouldnt it be better if we like just added some specific teachers to the table, so that only they can see the form when they register, instead of making it open for teachers to register? what do you suggest?

[cpradio]

That is a valid point. So if you take that approach you will want to create a Teachers table, with a single column (until you want to store more data)

userid int not null

Do not make it auto-increment, it is going to store the id from the user table, so it knows which user to identify as a teacher.

[jay93]

do you mean a table with a single column like the below?
CAP.JPG

[cpradio]

Yes, however, I would define the column name as userid instead of id so it is obvious as to what its' intentions are.

[jay93]

well i used id in the "users" table, and thought it should be consistent... does it matter if i make ths one different??

and now that we've got this, what next, i m quite lost at the moment...? how do we change the login.php and registration.php files?

[cpradio]

No, it doesn't make a difference, it really just makes it obvious to its intentions.

No, you don't need to alter the login or the registration files.

So now that you have that table, go ahead and add a user to it (one that you know is a teacher account) by placing the id value from the users table into the id column of the teacher table.

Next go ahead and start outlining your new page for updating marks, creating the physical file, the template, etc. like you have for the other pages. The first thing you will want to do is add a check that queries the teacher table based on the $_SESSION['id'] value to see if there is a record in that table for that id, if there is, render the template for updating marks, otherwise, render an access denied template.

[jay93]

okay, well i had another question, this form is going to be a little different, one that kind of self-updates anytime a new student registers...in other words, ideally, is there a way the form will automatically display all the names of students who have currently registered?? with a text field adjacent to the m where the teacher will enter their marks??

2. And i m not sure whether this will be possible, but another thing i would have very much loved to be in the form is, just above the form there is a field where the teacher enters the name of a subject, (and this will automatically be inserted into the "users" table as a new column) so that anytime the students log in , they get to see all their subjects and their scores in each????

i hope i m not expecting a hell lot...

[jay93]

"2. And i m not sure whether this will be possible, but another thing i would have very much loved to be in the form is, just above the form there is a field where the teacher enters the name of a subject, (and this will automatically be inserted into the "users" table as a new column) so that anytime the students log in , they get to see all their subjects and their scores in each???? "


well, another idea occured to me, instead of inserting the subject scores into the users table, we can create a separate table for them, a column for each subject...
but the catch is, could the teacher just type any subject name atop his form, and enter the subjects' marks for all students(automatically displayed on the teachers' form) and each subject the teacher enters automatically becomes a new column in the new table we want to create??