Page 1 of 1

PHP and MySQL INSERT and UPDATE

Posted: Fri Dec 17, 2004 12:05 pm
by jibip
Hello,

I will preface this as everyone else does. I am a relative newbie to PHP and MySQL. I am building a website for my church's bible quiz team. I want to use a database to keep track of the results. I have 2 tables "Quizzers" and "Quizzes".

Quizzers
ID
Name
TotalPoints
TotalQuizzes
AvgPoints (=TotalPoints/TotalQuizzes)

Quizzes (this is somewhat abbreviated)
ID
Quizzer1ID
QN1-Question1 (points for a correct answer)
QN1-Question2
QN1-Question3
QN1-Total
Quizzer2ID
QN2-Question1
QN2-Question2
QN2-Question3
QN2-Total

Assume that I have a form that will take in values. I need to insert them into the quizzes table. I also need to update the quizzer table with for the totalpoints, increment totalquizzes and recalculate avgpoints.

I'm really going to need some help here, so this is what I need. I need php code to do this, any suggestions for the MySQL datatypes for the various fields.

Also, there is actually 8 quizzers per quiz and 20 questions. Is there a good way, possibly utilizing arrays, that I can insert values into mysql without basically having 160 variables?

If you need any other info from me, just ask. Thanks.

Posted: Fri Dec 17, 2004 2:15 pm
by timvw
do we look like the homework club? :P


anyway, to me it seems that:

each quizzer can participate multiple quizzes
a quizz can be played by multiple quizzers

this relation is called a n - m relation.

but the relation data model doesn't have a direct representation for this relation... therefore you have to normalize and bring in a quiz-quizzer entitie.

this way you end up with 1 - n relationships. and this can be represented in a relational dbms...