developing an online scavenger hunt

I hope this is an appropriate topic for this forum. It concerns a project I would like to develop for my class.

I want to create an online scavenger hunt around my class site. The students will be competing to see who can finish first. They will have to find 5 items. When they find the item, I want them to fill out a short form that will input into a mysql database the item number and a timestamp.

Each student will be submitting 5 forms (one for each item). The variable for their name would be the ID field in the database. That is, each time they submitted the form, it would enter the information in the row for that student. I think the database fields would look something like this:


I can code forms, and I am learning PHP. What I would like is for someone to take me under their wings and guide me in setting up the database correctly as well as setting up the php coding in the forms to accomplish this task. I can create the output page with no problem. I learn very well from example, so if there is something similar already created that I can look at, I can probably figure it out from there.

Many thanks!

Debby

My wings are kind of full, but I can give you an idea on how to set up the database and forms.

I would have 3 tables: Users, Items, and Found.

Users:
ID - int not null auto_increment primary key
Name - varchar(20)
Password - varchar(32)

Items:
Item_ID - int not null auto_increment primary key
Item_Name - varchar(50)
Description - varchar(255)

Found
User_ID - int not null multiple key (foreign key to Users.ID)
Item_ID - int not null multiple key (foreign key to Items.Item_ID)
Time - timestamp

Add in whatever other fields you need, this is the basics.

The Found table relates each user to an item that they have found. There would be a multiple key or unique for each of them, that way the user could only have one entry in the table for each item.

In your form, you would of course ask for the username and password and probably have a drop down box for the items. You can populate the drop down box with the data in the Items table.

This is dependent on whether you want to user to see all of the items in the drop down box. Maybe they have to go in order, item1, item2, etc. In that case, you can do a COUNT(User_ID) WHERE User_ID = 'XX' from the Found table and see what item the user should be on. Then maybe ask them a question about the item to make sure they actually have it (i'm sure you would check later in person to make sure no one was cheating...)

I would record the times that the user found the item, too. That might come in handy later on for figuring the winner...

Hope that helps you. Hopefully someone else can actually help you with the code if you need it. If you come across a specific MySQL or PHP related problem, post the question in the proper forum and someone will help you.

---John Holmes...

Thanks for the great response. Only one question (so far!).

I am using phpMyAdmin to set up the tables. The only problem I have is setting up the User_ID and Item_ID fields. Where do I insert the "multiple key" attribute?

The items on the row are FIELD, TYPE, LENGTH/SET, ATTRIBUTES, NULL, DEFAULT, EXTRA and then checkboxes for PRIMARY, INDEX, and UNIQUE. The only fields I can type in are the LENGTH/SET and DEFAULT. I don't see "multiple key" or "foreign key" in any of the drop down menus.