im working on a site for a school, it must be able to show the parents what their childrens points, merits, detentions ect are. I dont have alot of experience working with databases so ill take it one question at a time!
im using mysql databasis with phpmyadmin, my first question is this,i did the login page for the parents where username is their email addres and a pass they chose, the 1st thing that must display is a list of their children in the school, from there they must click on the child to take them to the points, merits and all the rest,, setting up that recordset for the rest is easy but how do i make a value called up by the first recordset(the children they have) to be a link calling up the next recordset with all the other info,, and concerning the database,, can all this info be in one table on 'n database or is it even recommended to have more than one database? the fisrt recordset , does it need to be calling the data from a diffrent database than the second or just from a diffrent table,, or can it just be another coloumb in the same table!
Do you have any idea how over your head you are?
I'm going to move this thread to the Applications Development forum which is more suited for this kind of question. Meanwhile, you may find useful information here -
1. You use only one database
2. Use auto increment field to create a primary key in the students table and an auto increment primary key in the merits table. they will be called student_id and merit_id respectively
3. The merits table will contain a field called student_id, which is how you will relate students to the particular merit, demerit, etc.
If you put a form on the web, you must include security measures. Note that I did not say, "it's a good idea to include security measures." It's a must.
All variables used in queries to the database or being posted to the database must be validated and sanitized.The login credentials for the database should be kept in a file in a folder that is at the same level as your website root, so if the root is public_html, the folder should be at the same level.
As Murry noted, you are in over your head. It seems too early for you to think in terms of an actual project. You should work through a few tutorials before you dive into this.
Ditto what others have already said. There are big privacy concerns here and if you are not experienced in database design, PHP and web security, you should probably pass on this. There are hosted portal systems like http://www.aeries.com/aeries_overview.asp which might be a good route.
Having said that, if you do persue this, start by getting the database design right - it will drive the entire project. I can't provide any real advice without knowing much more about the requirements. But you should not need more than one database. You will just have multiple tables in the same database.
>the 1st thing that must display is a list of their children in the school,
>from there they must click on the child to take them to the points, merits and all the rest,,
Here's a potential security gotcha. Links pass data to other pages via querystring. It would be a simple matter for an authenticated parent to change a value in the querystring and look at data for any other student. So you must take measures to prevent this using session variables in your SQL queries. Good luck.
Hi guys! thanks so much for your help!
i know this is 'n really big challenge for me but i think ill be able to pull this off(with a bit of help from u guys! )
The concern about the security i understand and i will use session variables in my sql queries, is there maybe a good blog or resource you can refer me to for some other security musts?
Now i know i might be asking a stupid question but could you walk me through what u meant with
"3. The merits table will contain a field called student_id, which is how you will relate students to the particular merit, demerit, etc."
The 2 tables and the auto increment primary keys that makes sense to me i get the consept im just a little fuzzy on the excecution of it,,
tell me if im way off but im thinking a recordset calling up the diffrent student_id's should be in a form somehow submitting that id to recall the info of that student,, im just not sure how?
guys, thanks really much i appreciate ur help!
and if i knew that the school had the money to pay anyone else to do this i would have passed this on to them but i wanted the challenge and volunteerd to do it for them!
Here's the problem - you are so deficient in experience and vocabulary that not only will you not understand our replies, but also we will have to cover SO MUCH introductory material that you will quickly exhaust your welcome. And I'm pretty sure your school has given you a relatively short delivery time for this.
Your best bet is to try to get some up front experience by going through the tutorials on building a dynamic page/site on the Adobe Dev Center pages, get some of David Powers' books about DW and PHP and go through them from cover to cover. Then you'll be ready to come back here and ask questions and get understandable answers. Until then, I think you are on your own.
I think most of us at some time or another have taken a job at least a little beyond our skills. If this is something you really want to learn how to do, you may also want to consider hiring a third party and working with them. But as often is the case, if you got the job by under bidding, that may be hard to do.
I second, or third, the concerns about privacy and security here. Don't know if you're in the US or not, but there are regulations that I'm sure apply to even private schools. The security required is probably closer to that of a banking site than that of something that stores or passes data with no personal or financial information. One major flaw that I noticed in your initial description is the list of students. You would want the parent's accounts automatically linked to those of their children.
i might have given you the impession that im a complete idiot, which compare to you guys i am, but for the most part i do understand what u are talking about. Ive been working on dreamweaver for almost 3 years now, i have studied the essential guide to dreamweaver by david Powers from front to back, just,, try me please!
my database has 3 tables, Parent students and merits,
(Just to clarify, this site is aimed at the parents, for them to see their childrens results ect. they will be the only ones with login details for now)
Parent_id Name(name of one parent only) Surname Email
Student_id Name Surname Grade Class Parent_id
Merits_id Student_id and About 50 columns on all subjects, sport activities ect.
does this answer your question Bregent?
And about the security, im in south Africa, we're just getting started with the online community,, legal requirement arnt that high but im not trying to belittle the importance thereof,, i will definately look into that before handing over the site,
oh and i forgot to add that the only data that will be entered into the database will be done by the administator through imports,, parents or students will not be able to Register themselves,, everyone will be registered by the administrator and updated as required, login details will be given to them,, that makes it a bit easier i think
No, nobody thinks you're an idiot. The actual functionality and data handling are fairly straightforward, you seem to be on the right track with that. It's basically master-details page sets. I think the cause for concern is the security issue. Aside from protecting against actual breeches, adding layers of security provide a level of comfort to the end user that the information is private and accurate. A few suggestions, apologies if you've already considered them - basically stuff we've all encountered before n some form:
1. For database security and developmemt I defer to bregent and Murray, but the first thing is to use case sensitive alphanumeric strings for the DB name, UID and password.
2. Use an SSL site certificate
3. Encrypt the Logon ID/password when saved to the database. Webassist has an extension that makes this easy, I'm sure there are other solutions as well.
4. Detect when users log in from a different computer and have secret questions
>i might have given you the impession that im a complete idiot,
No, we just have no way of knowing what you know - hence the warnings.
>my database has 3 tables, Parent students and merits,
That's fine to start with. No doubt you will probably be able to optimize this, but this should work for now.
Back to your original question. The link from the student list will pass the Student_id to the student detail table. The SQL for the detail recordset would be something like:
SELECT * from Parents, Students, Merits
Parents.Parent_id = Students.Parent_id
Merits.Student_id = Students.Student_id
Students.Student_id = %S
Parents.Parent_id = %S
Note that you probably don't want to select all columns from all tables (SELECT *) - so just list the ones you need.
You would use sprintf to replace the placeholder with real values. The value for Student_id would be from the querystring and the value for Parent_id would be from a session variable.
Hi Guys Thanks so much for all your help! I've been working on the site; luckily I have until February,
My brain is shutting down and im stuck, ill take it from the start, once a parent logs in,, his/her email address (which I use as their usernames) is stored in the MM_Username session right?
So from there on they are directed to the page where a list of their children is displayed, from there on I’ve figured it out its just how to make the recordset display a list of only their children that gets me.
My database tables are like this.
(parent surname, parent firstname, email, Parent_id,)
(Student_id, Parent_id and then about 25 other merit columns)
I use the Parent_id to relate the two tables but now tell me if I'm going about this all wrong,
in the recordset for the page that should display the list of their children I want to use the session variable MM_Username(parentemail) to validate the user, which i can do successfully if I’m only using one table in the database(Parents) with Parent.email as colname, but how do i get the info from the second table, i tried this:
SELECT Parents.email, `table 2`.`Class`, `table 2`.Grade, `table 2`.Surname, `table 2`.`First name`, `table 2`.Parent_id, Parents.Parent_id
FROM Parents, `table 2`
WHERE Parents.email = colname AND Parents.Parent_id = `table 2`.Parent_id
Obviously it didnt work,, what am i doing wrong>?
To return only the student that are linked to the parent, you have to filter the student able using the parent ID. The Logon ID, or email, is stored in the Session variable as you mentioned. So what you need to do is retrieve the parent ID and then use that value to filter the student table.
The best method is to use a nested query, or nested Select statement. This is a recordset within a recordset. The first Select queries the student table and in the Where clause, another Select statement is inserted to query the parent's table to get the ID value. If you search on those terms you'll find a lot of examples.
A workaround is to just create two recordsets. The first one retrieves the parent ID value using the MM_Username session as the filter. The second recordset queries the students table on the parentID column, using a variable representing the value obtained from the first recordset.
Both of those do the same thing, but the first is more efficient. The key is that you first need to have the parent ID available before you can use it. Another way to do that, even less desirable though, is to modify the log in code and store that number in a session in addition to the email. And you could always just use the email address as the parentID in the student table. But again, that's not best practice as it means that if a parent needed to change his or her email, then it would have to be pdated in more than one place.