Storing user predictions

[Jedimasta]

Here's what I got. I'm relatively new to the php scene, but I'd like to think that I'm a quick learner.

I can't go much into detail, but to give you an idea, my company is running an event with local celebs. During the course of the event, there are going to be 15 different competitions that the celebs and company execs will be participating in. Not everyone will be participating in all of the events, though.

I'm creating a form using php and mysql to to allow onlookers to predict which of the contestants will win each competition. At the end of the event, winners will be chosen and given prizes for the most number of correct answers. Creating the forms will be simple, dynamically generated, but my question lies with the best way to store the guesses of each audience member who votes.

My dB is structured like so:

Competitions
|- comp_id (unique id for the competition)
|- comp_name (competition name, i.e. pie eating)
|- comp_desc (a short description of the competition)
|-winner_id (id of the contestant who wins, will be filled in after event)

Contestants
|- cont_id (unique id for contestant)
|- cont_name (contestant's name)
|- comp_id (id for competition that contestant is participating in.)
|-cont_bio (short contestant bio/description)

Entries
|- f_name (entrant's first name)
|- l_name (entrant's last name)
|- email (entrant's email address)
|- ?????

Now, what I want to know is, how do I go about storing the 15 guesses for each entry, so that I can go back to do comparisons and give scores for entrants on a tally page. Will I need to create 15 additional fields in the entries table to store the guess for each competition, or is there a better way to do this? Keep in mind that they need to be stored until winner results are comiled AFTER the event is over. Any suggestions are GREATLY appreciated.

[tdomega]

Entries
|- f_name (entrant's first name)
|- l_name (entrant's last name)
|- email (entrant's email address)
|- comp_id <-- here store for which contest the guess is
|- contestant_id <-- the actual guess
|- time <-- to ensure noone guesses after the competition 😉

just ensure that everyone can only guess once (e.g. by email)

for winning entries.comp_id and entries.contestant_id ahve to be the same as competitions.comp_id and competitions.winner_id

btw a unique id field for every table is a good idea if you want to change/delete data 😉

[Jedimasta]

Sorry, I guess I didn't clarify, the user will be guessing for all 15 competitions, not just a single one.

I should have included that, because it makes a big difference in what gets stored. I thought about a csv string, but I'm thinking it would be rather difficult to parse through that later to determine the winners.

[tdomega]

hmm i dont think that makes a difference, you will just have 15 rows for each user
should also make the win-queries much easier...

[Jedimasta]

let's say i take the answers provided and make a text string, seperated by commas and slip that into the db. is there a php tag that looks for a specific position in that list to get it's output?

For example, I also program in Cold Fusion. with it I can have a variable that contains a list: myList = 45,76,34,90
Then I can use a tag to pull position 3 from that list and output it or do whatever i need to: ListGetAt(myList, 3) which outputs 34.

Is there a similar function in php?

[tdomega]

ok, when you want to do it that way the function you need is explode ($seperator,$list), it creates an array and fills it with the elements in the string.

eg $arr=explode(',',$myList);

[Jedimasta]

Aaaaah! Very good, thank you. I'll know where to come if I need more help. That should work, but I'm not on that project quite yet. Thanks for the heads up though!