Comments on idea.

[nickk]

Hello,

I have been writing PHP scripts for quite some time now, but nothing big, just basic site navigation stuff, guestbooks, etc. I have a problem with making anything "bigger" which requires different parts to work together well.

I was recently asked to code the "brain" behind my school's yearbook website. I have been making a plan of how the whole application is going to work, and I wanted to post my ideas so maybe some more experiences PHP users can give me some advice.

Now the idea behind the website is that between 250 and 350 students are able to upload a piece of text (no bigger than maybe 2 or 3 medium sized paragraphs), and a photo. The text/photos should be displayed neatly online, and should be made available in .txt format (for the text pieces) for the publisher to use for the physical yearbook. There should also be a section where parents, etc. can upload a small message which will go into the yearbook.

My plan is the following: have 5 tables =>
[ul][li]1 table for each class (there are 4)[/li]
[li]1 table for the parent's comments section[/li][/ul]

Each "class" table will have the following:
[ul][li]ID[/li]
[li]Name[/li]
[li]Text[/li]
[li]Photo[/li][/ul]

When a student submits their text + photo, the script gives them the ID of the last student + 1, saves their text in the MySQL table, and in a .txt file with the name of their ID. Same thing happens with the photo. it is saved under ID.jpg (where ID is the student's ID), and in the photo field in the table, it simply saves the location of the photo.

With the tables + seperate files it should be easy to display the information for each student comfortably, and for the publisher to extract the information for the physical book.

Any idea/comments/suggestions welcome, and I would be very greatful for any help I get.

Thanks, Nick.

[nemonoman]

It's a good basic design.

You don't need a separate table for each class and parents.
Rather, just add a 'class' column to your table. 'Freshman', 'Sophmore', etc. (e.g., 'Parents'). This would be an inherently smarter design.

Populate this field with a pulldown menu on your entry page.

You might save the text in the database rather than as a text file. Not much overhead, and text would be searchable, in case anyone cared. Also it would allow you control text length.

Good to keep the photos as separate files with links, however.

Also, probably you want LastName, FirstName columns.

Using these ideas, you could produce some helpful reports:

Alpha student list
SELECT LastName, FirstName, Class From ClassTable
order by LastName, FirstName, Class

Alpha class list
SELECT LastName, FirstName, Class From ClassTable
order by Class, LastName, FirstName

Freshmen needing photo upload:
SELECT LastName, FirstName From ClassTable
WHERE Photo=''
AND Class='Freshman'

etc.

Let me commend your thoughtfulness in how you set things up, and applaud you for posting for discussion.

[nickk]

Thank you nemonoman for the reply. You have a good point there, I think I will add a "class", "first name", and "last name" field. This saves alot of space, 1 table instead of 5. About the text files, the problem is that the publisher has minimal computer knowledge, they definately don't know how to look through MySQL databases. Allthough now that I think of it, its actually quite easy to make a script that takes each piece of text from the table and saves it in txt format.
Thanks again, nemonoman you were very helpfull 🙂 .

EDIT: just noticed your edit. Wow, I see how useful it is if I have everything contained within 1 table, it makes it very easy to administrate the whole application this way.

I just remembered another question I had, namely, what kind of authentication should I use for this app? There are quite alot of students so it wont be very effective to physically give each one a password they can use. I was thinking of having no authentication at all, then if someone does post a fake text, etc. for someone else, the admin (me) can allways edit it.

[sidney]

if your school has a system of emails for each student using there emails to validate a real posting would be possible

once they posted there info and photo they could be sent a email
with an activation code and a validating link the info put in db and a field called validated set to false

then once the student uses the link the field code be set to true