Is there a convenient way to put a table within a table or something like that?

MySQL database
#1

I have some little classroom surveys to get the students up and talking. The students should walk around the classroom, ask everyone the question and send the answers from their phone.

So far I only have polls with 1 question, like: What is your biggest fear about marriage? There are 13 choices.

The topic of Unit 2 is “Cloning”.

My MySQL table: poll_cloningQ1 has the columns: id, gender, yes, no, not_sure, totals

id is just in case an auto-increment primary key may be useful.
gender is: male, female, other (so this table only has 3 rows)
The columns: yes, no, not sure are incremented according to gender and answer.
totals is just the sum of answers in each row, for calculating %ages.

Later on display the results and % results according to gender in output.php and ask students to comment on the results.

Trouble is for topic “cloning”, I have 13 questions. Do I really need 13 tables?

Is there a convenient way to somehow put a table within a table?

I know I can just copy the data structure of table: poll_cloningQ1 and rename it poll_cloningQ2 and so on, but I am hoping I can put all the data in 1 table, somehow.

Actually, the only problem I see is that the SELECT query for the output.php will have to query 13 tables. Since their structure is identical, I suppose that is not too difficult.

#2

Relational databases do not do multidimensional data; everything is a row. You can still do what you want, but you need to reconsider your approach.

If everything has to be a row you need to think of each entity in your survey as a row, and then think about how you link between them.

Each of your surveys is a row - it will have a survey name and that’s all you need. So you’ll have a survey table.

Each of your questions is a row - it will have a question, a reference to the survey it belongs to, and an ordering column. This is a “many to one” link - many questions belong to one survey. So you’ll have a question table.

Each of your answer options is a row - it will have an answer option, a reference to the question it belongs to, and an ordering column. This is another “many to one” link. So you’ll have an answer option table.

Finally you’ll need to record the answers given. The simplest structure for this would be a table which has a reference to an answer option and a count.

As you can see, there are quite a few moving parts here.

1 Like
#3

Thanks, I am still a bit puzzled, but I have heard of this “one to many” and “many to one”. I will read about it.

My simplest surveys have 1 question, 13 possible answers, so basically 3 rows: gender, 13 columns as possible answers and the 14th column Totals. All entries are simple integers, initialized with zero. The PHP just increments according to gender and answer.

3 rows X 14 columns (ignoring id)

Now I have 13 questions, each question has 3 rows: gender X 3 columns choices + 1 column Totals.

I will try adding a column Questionnr. Maybe I can enter the data and fish the data out then fairly easy for display.

$column = $q2;
$mycount1 = 'UPDATE poll_cloning SET ' . $column . ' = ' . $column . ' + 1, Totals = Totals + 1 WHERE Gender = \'' . $q1 . '\''  AND Questionnr = $qnr;

Just not sure how to pass the question number to PHP right now!

Thanks for the advice!

#4

This is how I would structure the database table. You know that you have a question, so that is one column. You have 13 answers/columns and that should be it for the database table. If you need a table for totals, name, gender or what have that would be a different database table.

If you use Ajax and JavaScript on the database table you can even do neater things with the the questionnaire, such as have one question only have five possible answers. Almost the same type of logic is involved with an online trivia game. Here’s my query string as an example from my trivia game -

$sql = "SELECT id, user_id, hidden, question, answer1, answer2, answer3, answer4, category FROM " . static::$table . " WHERE category=:category";

Notice the user_id column as that is a way of tying in another database table.

I only have four columns for answers but that doesn’t mean I have to use all the columns. Another neat feature if I need to add another column I could do it with PHP code.

I hopes that help a little.

If you use an array that is setup where the key matches the columns then updating the table becomes really easy to do:

        /* Initialize an array */
        $attribute_pairs = [];

        /* Create the prepared statement string */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = "{$key}=:{$key}"; // Assign it to an array:
        }

        /*
         * The query/sql implodes the prepared statement array in the proper format
         * and I also hard code the date_updated column as I practically use that for
         * all my database table. Though I think you could override that in the child
         * class if you needed too.
         */
        $sql  = 'UPDATE ' . $table . ' SET ';
        $sql .= implode(", ", $attribute_pairs) . ' WHERE id =:id';

        /* Normally in two lines, but you can daisy chain pdo method calls */
        $pdo->prepare($sql)->execute($data);
1 Like
#5

Thanks! That helps a lot.

I will try to implement that. Since I don’t know much about this, I will need to try it out. But I like the idea of using $attribute_pairs = [];, that will make my php files a lot shorter I believe!

Basically, for the analysis of the saved data, I don’t need id, I just put id in case I may need it sometime.

For this saved data, the gender is important, so I think I need:

' WHERE gender =:gender';‘

Thanks for the tips!

#6

@Strider64 not sure if this is quite what you meant, but I did it like this and it works:

One table for each question would give me a lot of very small tables, so I would rather keep it all together. I used a hidden input to send the Question Number to PHP.

Now I can increment each field according to Qnr and gender and keep 1 table instead of 13.

I can use a blanko text file: blanko_surveys.php and replace certain strings using Python to make the PHP file for the form. Also, I can generate PHP variables automatically with a little bit of Python.

So I make a string of variables like this using Python and put them in the blanko_surveys.php:

$q1 = $_POST['G1'];
$Qnr1 = $_POST['hidden1'];
$q2 = $_POST['G2'];
$Qnr2 = $_POST['hidden2'];
$q3 = $_POST['G3'];
$Qnr3 = $_POST['hidden3'];
$q4 = $_POST['G4'];
$Qnr4 = $_POST['hidden4'];
$q5 = $_POST['G5'];
$Qnr5 = $_POST['hidden5'];
$q6 = $_POST['G6'];
$Qnr6 = $_POST['hidden6'];
$q7 = $_POST['G7'];
$Qnr7 = $_POST['hidden7'];
$q8 = $_POST['G8'];
$Qnr8 = $_POST['hidden8'];
$q9 = $_POST['G9'];
$Qnr9 = $_POST['hidden9'];
$q10 = $_POST['G10'];
$Qnr10 = $_POST['hidden10'];
$q11 = $_POST['G11'];
$Qnr11 = $_POST['hidden11'];
$q12 = $_POST['G12'];
$Qnr12 = $_POST['hidden12'];
$q13 = $_POST['G13'];
$Qnr13 = $_POST['hidden13'];

# loop through the variables and make an array of UPDATE commands.
for ($i=1; $i <= 13; $i++) {
    if (${"q$i"} == '') ${"q$i"} = 'no_answer';
        $choice = ${"q$i"}; // yes, no or not_sure
       $question = ${"Qnr$i"}; // the question number
       $updates[] = 'UPDATE ' . $table . ' SET ' . $choice . ' = ' . $choice . ' + 1, Totals = Totals + 1 WHERE Qnr = \'' . $question  . '\' AND gender = \'' . $gender . '\'';
		    	//echo ${"q$i"} . '<br>';
		    	//echo ${"Qnr$i"} . '<br>';

Then I just use a foreach loop to run the UPDATE commands. Works!

But if anyone has an improvement, I’d be glad to hear it!

#7

Many to many table structures or serializing the data is a better design. The other option is to use a NoSQL system.

Where stryders design lacks, is expandability in a clean way.

Table_Survey
Id
Date
DisplayName

Table_Questions
Id
Question
Survey_Id
(You can also dynamically create what type of question here; as in drop down/ radio button/ ect)

Student_Survey_Response
Id
Table_Id
Question_Id
Response

Something along these lines

#8

Thanks for the tips!

I am trying to understand them!

Basically, in this instance, I have 13 questions on a static webpage. Each question has the choices:

yes, no, not sure

These are radio buttons, so only 1 choice allowed. Then there is a hidden input which tells PHP which question is being answered. This was the best way I could think of to get all the results in 1 table.

Just for separation, I split the answers according to the gender of the respondent. Given some people don’t want to be classified as male or female, I have 3 genders, so my table: poll_cloning has 3 * 13 = 39 rows. 3 rows represent 1 question’s results according to gender.

Later on, I display the results per question in a table in output.html and ask the students to comment on the results. Just to get them talking!

Still haven’t mastered creating functions in PHP!

Sponsor our Newsletter | Privacy Policy | Terms of Service