Newbie ? about updating many-to-one tables

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Right to the point: Am I taking the wrong approach in the manner I'm
trying to update a many-to-one relationships and/or have I implemented
the design of my database poorly?

To practice w/ PHP & MySQL, I'm trying to design a database with three
tables for a quiz game: Questions (one-to-many relationship with)
Questions_Choices (many-to-one relationship with) Choices. My thinking
is that different multiple-choice questions might have a common

I'm trying to write a form where someone can update a question and its
choices. Due to my lack of script writing experience, the pseudo-code
I've come up with seems pretty convoluted. I refer to foreign keys as
"_links" here. Here's the part that focuses on comparing the old batch
of choices to the new batch:

public function update_choices($new_choices, $old_choices) {
    // compare the new choices to the old choices, insert the difference
    foreach ($old_choices as $choice) {
        if (!in_array($choice, $new_choices) {
            // see if any other record in the Question_Choices table uses this
            $qc_id = ...;
            // if no other Question_Choice record uses this choice_link...
            if ($qc_id == 0) {
                // delete the choice record from the Choices table
            // delete this record from the Questions_Choices table

    // compare the old choices to the new choices, remove the difference
    foreach ($new_choices as $choice) {
        if (!in_array($choice, $old_choices) {
            // see if a choice already exists Choices table
            $choice_id = ...;
            // if the new choice doesn't match an existing record in Choices
            if ($choice_id == 0) {
                // insert a new record into the Choices table
            // insert a reference to this choice record into the
Questions_Choices table

I tried to do this with UPDATE queries, but it proved even more
difficult. And this is just with a simple database. :(

Doesn't the database become exponentially more complex the more many-
to-many tables you have or is my approach making the task of updating
harder than it has to be?

Re: Newbie ? about updating many-to-one tables


Quoted text here. Click to load it

The design is OK, just the script is bad.

Quoted text here. Click to load it

OK, nothing wrong with that.

Quoted text here. Click to load it

The reason why it's convoluted is that it does more than it should. You
should write separate methods for

* adding/removing/updating questions
* adding/removing/updating choices
* adding/removing/updating a question's choices

Three completely different tasks.

Quoted text here. Click to load it

To update the choices for a particular question, you just have to DELETE
all old references and INSERT the new ones. There's no need for complex
tests like above here.

If possible you should use a transactional database like MySQL/InnoDB,
which gives you much more security and features. For example if you
completely remove a question, the DB can automatically remove all
references to its choices from the m:n table. And on the other hand the
DB can prevent the deletion of a choice if it's still in use in some

Quoted text here. Click to load it

IMHO the latter.


Re: Newbie ? about updating many-to-one tables

Quoted text here. Click to load it

If you are going to be working on large complex db's and just need practise
in normalization and using key tables, this is certainly fine.

For the particular project you mention, it might not be worth the effort to
build a separate table for answers.  Having non-unique columns is not at all
uncommon; I mean, look at "enum".

Most likely, if the answer to two questions is the same word, it is probably
not important.

Quoted text here. Click to load it

Way harder.  My first thought would be to make a text-input form where the
question and answers load into the form as defaults, then a query to  update
the row with the form contents.

Re: Newbie ? about updating many-to-one tables

clumsy_ninja wrote:
Quoted text here. Click to load it

I highly recommend you ask these questions in comp.database.mysql.  This
has nothing to do with PHP, and the real MySQL experts hang out there.
You'll find a few knowledgeable people here, but not the experts you
find there.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: Newbie ? about updating many-to-one tables


Quoted text here. Click to load it

I just thought I'd add a bit to my first response.

There is no reason not to UPDATE the entire row.  If you query the entire
file and load the current fields in the form, nothing will happen on submit
unless something is changed.  But my point is, it's silly to go through all
the trouble of finding out which fields have changed.  Just load the old
fields and resubmit the entire form as an UPDATE.

See my point?

1. "SELECT * FROM db WHERE id='$id'"
2. Convert all fields to variables; I'll make $question=$row['question']; ie
the value of the question in your db.
3. <form ...><fieldset><input name="question" type="text" ... value="<?php
echo $question;?>" />

and so on for all the answers and other editable information.

I use this technique a lot.  It's very easy to use and makes a nice pattern.

Once you have it working, you'll want to make the fields sticky for
POST/GET and/or SESSION, or whatever method you use to hold data between
screens.  That way you can give the editor a preview screen and either
repeat the form, filled in correctly, at the bottom of the preview page, or
even simpler just let him backspace and reload the form where he left it.

 <input type="text" name="question" size="30" maxlength="30" value="<?php
 if (isset($_POST['question'])) {
  echo $_POST['question'];
 } else {
  echo $question;
 ?>" />

Site Timeline